Excel-VBA Solutions: 2015

Split a Long Row of Data Into Multiple Rows

We can use Excel Macros to organize our data. Below is one such example. Here what we going to do is split long row of data into multiple rows. Here the rule is we need 5 cells per row, then to start the next row. So data in F1 will move to A2, G1 to B2 and so on. Same on third row until data runs out.

Here is how our data currently in the sheet.

Below is the end result we need.

So here is the sample code to do that. You can customize the code to suit to your situation. New sheet will be added and result will be created in that sheet. At last sheet will renamed as "Result sheet" and activated. And you will get a confirmation message at end.

Dim WS As Worksheet
Dim WS_Result As Worksheet

Set WS = Worksheets("Input file")
Set WS_Result = Worksheets.Add

'find last column
Lastcol = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

Counter = 1
ResultRowCount = 1
For i = 1 To Lastcol
If Counter = 6 Then
Counter = 1
ResultRowCount = ResultRowCount + 1
Else
WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i)
End If

WS_Result.Cells(ResultRowCount, Counter).Value = WS.Cells(1, i)

Counter = Counter + 1
Next i

WS_Result.Name = "Result sheet"
WS_Result.Activate
MsgBox "Completed!", vbInformation, ""

Detect Operating System

Sometimes we need to develop VBA programs to compatible with both Windows and Mac operating systems. If you develop a VBA code for windows, sometimes it will work for Mac as well. For an example if your program only manipulate data within a excel sheet it will work for both operating systems. But you will find that some VBA programs not compatible with Mac. For an example if you are trying to access a directory using a VBA code this will not work in both OS's. Because file path of Windows contains backslash ( \ ). But Mac use colon (:) to separate the folder names. This is an only one example. If you develop VBA programs you will find that there are various instances not supported by both OS's

So if your code is not compatible with both OS's, you will need to develop two VBA codes. But there are situations you don't know what OS your user will use. However you can make your code compatible with both OS by combining both codes. What you can do is first check the OS, and if it detects Mac, you can ask the program to follow one path by using a if statement. Else you can ask it to follow the path written for Windows.

Here is a VBA code to do that.

'If the operating system is Mac
If Application.OperatingSystem Like "*Mac*" Then
'..........
'Here goes the code compatible with Mac
'..........

'If windows
Else
'..........
'Here goes the code compatible with Windows
'..........
End If

Use of On Error GoTo 0

In this post I will explain why we need to use On Error GoTo 0 in error handling. I will explain the use of On Error GoTo 0 using a simple example.

Assume we need to make changes in different workbook. So first we need to declare the variable and set the workbook as follows.

Dim WB_Example As Workbook

Set WB_Example = Workbooks("Example File.xlsx")

So if we run above two lines variable WB_Example will be declared and workbook "Example File.xlsx" will be assigned to that variable. But this will be successful only if "Example File.xlsx" is in open state. Otherwise it will give below error.

If you click the debug button second line will be highlighted as follows

However if this workbook is not in open state, we can open it automatically and continue the rest of the code. For that we need to avoid that error message. Error message can be avoided and resume code using On Error Resume Next statement. We can do it as follows.

Dim WB_Example As Workbook

On Error Resume Next
Set WB_Example = Workbooks("Example File.xlsx")

If Err <> 0 Then
If Err.Number = 9 Then
Workbooks.Open ("D:\VBA Blog\On Error Goto Zero\Example File.xlsx")
Else
MsgBox "Unknown Error.", vbCritical, "warning!"
Exit Sub
End If
End If

So if there is an error, program will check whether error number is equal to 9. If it equals to 9, Then program will automatically opens the workbook. Because we know that this error occurs at this point due to workbook is not in open state. But if the error number is different to 9, error occurred due to some other reason. So program will notify it to the user and terminated at that point.

Think we run above code and didn't get any other Err.Number different to 9. So then our program will continue from above code. So this is where we need On Error GoTo 0. Consider the following code. If you look at the last line you will see that we are trying to put a string to a cell A1 of sheet2. But what will happen if we don't have worksheet with name "Sheet2". Progrma should give us an error.

But if you run the code, you will realized that it is not giving such error if there is no Sheet2 available. So this happen due to effect of On Error Resume Next statement. So this On Error Resume Next statement was helpful up to particular point of code. But after that we are getting unexpected problem due to it. So now we need to end effect of On Error Resume Next statement in a earliest possible point of our code. This can be done by using On Error GoTo 0 statement.

So now if we run the macro again and if there is no Sheet2 in our workbook, then we will get this error message.

So if you click the Debug button, last line will highlighted as follows.

So you can see that effect of On Error Resume Next statement is avoided by On Error GoTo 0.

Transposing an Array in VBA

In this post I will explain you a quick way to transpose an array. Sometimes we need to transpose our arrays before put it to worksheet. You can easily use below method for those situations.

So let's consider the below sample data for this example.

I have this data in Sheet10 of my Excel workbook.
So let's declare our variables and set the worksheet first.

Dim WS As Worksheet

Set WS = Worksheets("Sheet10")

Dim All_Data() As Variant

Now we can put above data to declared array.

All_Data = WS.Range("A1", "J4")

So we have put all the data to All_Data array.
Next we need to add new worksheet to put our transposed array.

Worksheets.Add

Then you can transpose the array and put it to new worksheet as follows.

ActiveSheet.Range("A1", Range("A1").Offset(UBound(All_Data, 2) - 1, 3)).Value = Application.Transpose(All_Data)

So below is the full code to transpose an array.

Dim WS As Worksheet

Set WS = Worksheets("Sheet10")

Dim All_Data() As Variant

All_Data = WS.Range("A1", "J4")

Worksheets.Add

ActiveSheet.Range("A1", Range("A1").Offset(UBound(All_Data, 2) - 1, 3)).Value = Application.Transpose(All_Data)

And you will get below result after running the macro.

Re-size Dynamic Arrays

Sometimes when we create dynamic arrays, we know how many elements our array will have. If we know that we can specify the upper bounds of the arrays in the Redim statement. But there are times we can't tell exactly how many elements our array will have. Because we might need to add new elements to our array when go through the subroutine. I will explain this using below example.

Consider this sample data.

Think we need to add records of each person to an array if that person get more than $7000 of monthly income. So we don't know how many people will be added to our array when we defining it.
Because items will be added to the array when go through the subroutine. So inside a for each loop, it will check the monthly income of each person and if it is more than $7000, records of that person will be added to the array. So below is the full code to do that.

Dim WS As Worksheet
Dim Rng As Range
Dim PeopleWithHigherIncome() As Variant
Dim HigherIncomeCounter As Long
Dim LoopCounter As Integer

Set WS = Worksheets("Sheet1")

'find last row
Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

HigherIncomeCounter = 0

For Each Rng In WS.Range("A2", "A" & Lastrow)

If Rng.Offset(0, 3).Value > 7000 Then

HigherIncomeCounter = HigherIncomeCounter + 1

ReDim Preserve PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)

For LoopCounter = 1 To 4
PeopleWithHigherIncome(LoopCounter, HigherIncomeCounter) = Rng.Offset(0, LoopCounter - 1).Value
Next LoopCounter

End If

Next Rng

'Put PeopleWithHigherIncome array to new worksheet
Worksheets.Add

ActiveSheet.Range("A1", Range("A1").Offset(3, UBound(PeopleWithHigherIncome, 2) - 1)).Value = PeopleWithHigherIncome

Last two lines of above code will add new sheet and display the result array in that new worksheet.

Here is the final result you will get.

As you can see the values are transposed from our original data. In a next post I will explain how to transpose an array. So we can transpose this result array before put it to a worksheet.

Also there are few things you need to remember.

You can't use below line instead of ReDim Preserve PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)

ReDim PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)

Because if you use only ReDim keyword, it will delete earlier added items to the array. But ReDim Preserve keyword will add new items to the array without deleting existing items.

Also you can't write

ReDim Preserve PeopleWithHigherIncome(1 To HigherIncomeCounter, 1 To 4)

Instead of

ReDim Preserve PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)

If you add Preserve keyword, it can only Re-dimension the last dimension of the array.

Calculate With Arrays

Sometimes we need to do calculations based on the data in our excel sheets and put the results back to the sheets. We can do these calculations for each cell one by one using a for next loop. But it can be inefficient in the running stage of the program. It won't be an issue if we only deal with sheets only have data in few rows. But in real world, we have to deal with sheets which have thousands of rows of data.

So in this post I will explain you how to calculate efficiently using arrays.

Here is the example we have.

It is a sample data set of employees of a company. Column D has their monthly income and column E has the tax rate. According to the above data, tax rate equals to 10%. Normally tax rate increases with the salary level. But to make this example simple, I have set it to 10% for any salary level. So now what we need to do is calculate Annual income, Tax and Remaining.

So let's start the coding.

First we need to declare variables.

Dim WS As Worksheet

Dim MonthlyIncome() As Variant
Dim Answers() As Variant

Dim D1 As Long
Dim i As Long

We are going use two variant type arrays for this example. Next we need to set the worksheet to declared variable.

Set WS = Worksheets("Sheet1")

Below line will add existing data to MonthlyIncome array.

MonthlyIncome = WS.Range("D2:D19")

We are going to store calculated results in a different array call Answers. For that, we need to determine it's dimensions. Following line will assign it's first dimension to the variable D1.

D1 = UBound(MonthlyIncome, 1)

As we need to store three different result for each employee, second dimension should equal to 3. So we can Redim the Answers array as follows.

ReDim Answers(1 To D1, 1 To 3)

Then below, for next loop will calculate the results for each employee and store the results in Answers array.

For i = 1 To D1
Answers(i, 1) = MonthlyIncome(i, 1) * 12
Answers(i, 2) = (MonthlyIncome(i, 1) * 12 * 10) / 100
Answers(i, 3) = Answers(i, 1) - Answers(i, 2)
Next i

Then we can write Answers array to worksheet.

WS.Range(Range("F2"), Range("F2").Offset(D1 - 1, 2)).Value = Answers

Finally it is good practice to erase the arrays

Erase MonthlyIncome
Erase Answers

So that is the step by step explanation about how to use arrays for calculations. Below is the full code for above example.

Dim WS As Worksheet

Dim MonthlyIncome() As Variant
Dim Answers() As Variant

Dim D1 As Long
Dim i As Long

Set WS = Worksheets("Sheet1")

MonthlyIncome = WS.Range("D2:D19")

D1 = UBound(MonthlyIncome, 1)

ReDim Answers(1 To D1, 1 To 3)

For i = 1 To D1
Answers(i, 1) = MonthlyIncome(i, 1) * 12
Answers(i, 2) = (MonthlyIncome(i, 1) * 12 * 10) / 100
Answers(i, 3) = Answers(i, 1) - Answers(i, 2)
Next i

WS.Range(Range("F2"), Range("F2").Offset(D1 - 1, 2)).Value = Answers

Erase MonthlyIncome
Erase Answers

Following is the result you will get after running the code.

Quickly Write Multidimensional Array to Excel Range

I explained how to write multidimensional array to an Excel range using for next loop from earlier post. But there is much quicker way to do that without for loop.

So lets put below data to a multidimensional array call "PopulationDensity" and then write it back to a new sheet using this quick method.

So here is the code to do that.

Dim WS As Worksheet

Set WS = Worksheets("Sheet4")

Dim PopulationDensity() As Variant

PopulationDensity = WS.Range("A2:D16")

Dim WS_New As Worksheet

Set WS_New = Worksheets.Add

WS_New.Range(Range("A1"), Range("A1").Offset(UBound(PopulationDensity, 1) - 1, _
UBound(PopulationDensity, 2) - 1)).Value = PopulationDensity

Erase PopulationDensity

And this is the result you will get.

Index of this array starts from 1. If index of your array start from 0 then you need to change below line

WS_New.Range(Range("A1"), Range("A1").Offset(UBound(PopulationDensity, 1) - 1, _
UBound(PopulationDensity, 2) - 1)).Value = PopulationDensity

as follows

WS_New.Range(Range("A1"), Range("A1").Offset(UBound(PopulationDensity, 1) , _
UBound(PopulationDensity, 2) )).Value = PopulationDensity

Quick Multidimensional Array from Excel Range

There is a quicker way to create multidimensional array from an Excel range.

Assume we need to store below data in an array.

Then here is the quickest way to do that

Sub Quick_MultidimensionalArrays()

Dim WS As Worksheet

Set WS = Worksheets("Sheet1")

Dim All_Data() As Variant

All_Data = WS.Range("A2", "E19")

Erase All_Data

End Sub

However there is a very important thing you need to remember if you create array in this way. It is that if you create an array in this way, index of array will start from 1 and not from 0.

Also you can replace

All_Data = WS.Range("A2", "E19")

with below

All_Data = WS.Range("A2:E19")

Dynamic arrays in VBA

In an earlier post, I explained about fixed size arrays. We can use fixed size arrays when we know the exact amount of records our database has. But there are instances where we are not certain about the amount of data user will input. For an example think about a database of a company employees. New employees will be recruited and some employees will resigned or retired. So the number of records in this database is not a constant. So we need to use dynamic array to store these data. In this post I will explain how to use dynamic arrays in VBA.

Above is a sample database which contains some information of employees of a company. So we can't tell the exact number of records we will have in this database. Because new records will be added to database and some records will be removed. Also there is a possibility of adding new columns in future to hold some other information. So we need to use dynamic array if we need to store this data.
Below example shows how to store above data in dynamic array and then how to transfer whole data to another sheet.

Dim WS As Worksheet
Dim All_Data() As Variant
Dim i, j As Long

Set WS = Worksheets("Sheet1")

'Find upper bounds
Dimension1_Upperbound = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row - 2
Dimension2_Upperbound = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column - 1

ReDim All_Data(0 To Dimension1_Upperbound, 0 To Dimension2_Upperbound)

For i = LBound(All_Data, 1) To UBound(All_Data, 1)
For j = LBound(All_Data, 2) To UBound(All_Data, 2)
All_Data(i, j) = WS.Range("A2").Offset(i, j).Value
Next j
Next i

Dim WS_New As Worksheet
Set WS_New = Worksheets("Sheet3")

For i = LBound(All_Data, 1) To UBound(All_Data, 1)
For j = LBound(All_Data, 2) To UBound(All_Data, 2)
WS_New.Range("A1").Offset(i, j).Value = All_Data(i, j)
Next j
Next i

We declare fixed size arrays like

Dim All_Data(0 To 17, 0 To 2) As Variant

But we don't use constants when declaring Dynamic arrays. We just declare it as follows.

Dim All_Data() As Variant

After that we use ReDim statement to assign the upper bound and lower bound.

ReDim All_Data(0 To Dimension1_Upperbound, 0 To Dimension2_Upperbound)

But we should find Dimension1_Upperbound and Dimension2_Upperbound prior to ReDim statement as we did in above example. So this is how to use dynamic arrays in VBA.

Multidimensional Arrays in VBA

If an array has more than one dimension it is called multidimensional array. If you have a list of names which you want to put to an array, you can put them in an array which has single dimension. But think you need to store these names with address and age of each person separately, then the best way is to use an array with two dimensions. Two dimensional arrays are like tables, where rows of tables correspond to first dimension and columns corresponds to second dimension. But if an array has more than two dimensions, following is the best way to illustrate it's structure.

Consider this four dimensional array

Dim SampleArray(0 To 1, 0 To 3, 0 To 2, 0 To 5) As Variant

It's structure can be best explained as follows.

So now let's look at how we can declare and populate multidimensional array. I will use below sample data for the explanations.

So let's put all these data to an array called All_Data()

Dim WS As Worksheet
Dim All_Data(0 To 17, 0 To 4) As Variant
Dim i, j As Long

Set WS = Worksheets("Sheet1")

For i = 0 To 17
For j = 0 To 4
All_Data(i, j) = WS.Range("A2").Offset(i, j).Value
Next j
Next i

It is a best practice to use LBound and UBound instead of constants. Because then we don't need to change whole code if we need to change the size of our array. So here is the modification of above code where constants are replaced with LBound and UBound functions.

Dim WS As Worksheet
Dim All_Data(0 To 17, 0 To 4) As Variant
Dim i, j As Long

Set WS = Worksheets("Sheet1")

For i = LBound(All_Data, 1) To UBound(All_Data, 1)
For j = LBound(All_Data, 2) To UBound(All_Data, 2)
All_Data(i, j) = WS.Range("A2").Offset(i, j).Value
Next j
Next i

So now you know how to put values from worksheet to a multidimensional array. So what if you need to do the reversal. ie-What if you need to put values from an array to a worksheet range. Code is very much similar to the above example. You just need to do a change in one line. Following is an example. Here what we do is, first we get data from first 3 columns to an array. Then reads values from array and put them back to sheet 3.

Dim WS As Worksheet
Dim All_Data(0 To 17, 0 To 2) As Variant
Dim i, j As Long

Set WS = Worksheets("Sheet1")

For i = LBound(All_Data, 1) To UBound(All_Data, 1)
For j = LBound(All_Data, 2) To UBound(All_Data, 2)
All_Data(i, j) = WS.Range("A2").Offset(i, j).Value
Next j
Next i

Dim WS_New As Worksheet
Set WS_New = Worksheets("Sheet3")

For i = LBound(All_Data, 1) To UBound(All_Data, 1)
For j = LBound(All_Data, 2) To UBound(All_Data, 2)
WS_New.Range("A1").Offset(i, j).Value = All_Data(i, j)
Next j
Next i

Here is the result you will get.

Fixed Size Arrays in VBA

We can describe an array as a data structure which contains numbered list of items. Important fact is that we can refer these items by same name. Due to this reason arrays play major role in any programming language. In VBA, items are normally numbered from zero. But if we need, we can change it to start from different number.

So this post will give you good understanding about fixed size arrays. I will use below sample data in the explanations.

In VBA we can declare fixed arrays in several ways. For an example let's create fixed array called FirstTenNames.

Dim FirstTenNames(9) As String

So this array can contain 10 items. Here index start from 0. However if you need to start index from 1, you can declare array as follows

Dim FirstTenNames(1 to 10) As String

So now let's populate this array with first 10 names in column A. You can do it as follows.

Dim FirstTenNames(9) As String

Dim WS As Worksheet
Set WS = Worksheets("sheet1")

FirstTenNames(0) = WS.Range("A2").Value
FirstTenNames(1) = WS.Range("A3").Value
FirstTenNames(2) = WS.Range("A4").Value
FirstTenNames(3) = WS.Range("A5").Value
FirstTenNames(4) = WS.Range("A6").Value
FirstTenNames(5) = WS.Range("A7").Value
FirstTenNames(6) = WS.Range("A8").Value
FirstTenNames(7) = WS.Range("A9").Value
FirstTenNames(8) = WS.Range("A10").Value
FirstTenNames(9) = WS.Range("A11").Value

Now we know how to assign values to an array. What if we need to get values back from an array.
For example think you need to put this 10 names to new sheet (Sheet2) of this workbook. Then you should add following part to end of above code.

Dim WS_New As Worksheet
Set WS_New = Worksheets("sheet2")

WS_New.Range("A2").Value = FirstTenNames(0)
WS_New.Range("A3").Value = FirstTenNames(1)
WS_New.Range("A4").Value = FirstTenNames(2)
WS_New.Range("A5").Value = FirstTenNames(3)
WS_New.Range("A6").Value = FirstTenNames(4)
WS_New.Range("A7").Value = FirstTenNames(5)
WS_New.Range("A8").Value = FirstTenNames(6)
WS_New.Range("A9").Value = FirstTenNames(7)
WS_New.Range("A10").Value = FirstTenNames(8)
WS_New.Range("A11").Value = FirstTenNames(9)

You will get below result after running the code.

In above example we wrote one line for each item in the array. However it is not practicable in real world as some arrays can contain millions of data. So there should be a better way to populate an array and get data back from it. One solution is to use for next loops. So let's use for next loop to populate FirstTenNames array.

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")

Dim FirstTenNames(1 To 10) As String

For i = 1 To 10
FirstTenNames(i) = WS.Range("A1").Offset(i, 0).Value
Next i

So you can see that we were able to shorten our code by great extent. Also we can use for next loop to get back the data from the array as well. Add following part to end of above code.

Dim WS_New As Worksheet
Set WS_New = Worksheets("Sheet2")

For i = 1 To 10
WS_New.Range("A1").Offset(i, 0).Value = FirstTenNames(i)
Next i

After running the code you will get first ten names in column A of sheet 2 of the same workbook.
Also it is a good practice to erase the array after using it. You can erase an array using "Erase" keyword. For an example you can erase above array as follows

Erase FirstTenNames

In our above example we used constant values in for next loops. So if we need to change the number of items in array we need to change it in every for next loop in the code. To avoid this we can use

For i = LBound(FirstTenNames) To UBound(FirstTenNames) instead of For i = 1 To 10

So we can modify our code as follows.

Dim WS As Worksheet
Set WS = Worksheets("Sheet1")

Dim FirstTenNames(1 To 10) As String

For i = LBound(FirstTenNames) To UBound(FirstTenNames)
FirstTenNames(i) = WS.Range("A1").Offset(i, 0).Value
Next i

Dim WS_New As Worksheet
Set WS_New = Worksheets("Sheet2")

For i = LBound(FirstTenNames) To UBound(FirstTenNames)
WS_New.Range("A1").Offset(i, 0).Value = FirstTenNames(i)
Next i

Erase FirstTenNames

Now we don't need to change the for next loop manually if the number of items in the array changed.

How to Use Scroll Bar in Userform

Some times we need to use very large userforms in our VBA projects. Size of the form depends on the number of fields and various objects we have in our form. And if we need to show them in one window, we need to create big userform to put them all. You can create a form of any size by giving appropriate values for Height and width in properties window. But if our userform is bigger than the screen we need to use scroll bars inside our form. It is easier to put a scroll bar to a userform. But I saw that many people have problems with setting scroll bars correctly. Lot of people say that their scroll bars not working at run time. So there are few important things you should know when using scroll bars.

I will explain these important facts using a simple example. I have created a large form which has height of 1200 and width of 420. And there are a lot of text fields inside my userform.

If you look at properties window, you will notice that ScrollBars value is set to 0-fmScrollBarsNone by default. So we need to change this property depending on our requirement. If you have a userform with larger width, then you need to set ScrollBars value to 1-fmScrollBarsHorizontal. If you have a userform which has higher height, then you need to set this value to 2-fmScrollBarsVertical. And if you need scroll bars in both directions you can set the value to 3-fmScrollBarsBoth. You need to set to this value only if both width and height of your form is bigger than the screen.

You should set ScrollTop value to the 0. So then it will always appear at top.

Finally you need to set the ScrollHeight. It is very important to set appropriate value to this. Othewise you may not able to scroll up to the bottom of the form. Also if you set higher value, then user will scroll after the end of the objects. So user will see like your form is extended more than needed. So you should set a optimum value for this property for correct functionality and nice appearance. For example if you have a form having a height of 1200, you should set ScrollHeight value around 1850.

Insert Text Box to a worksheet

Today I'm going to show you how to insert activeX controls text box to a worksheet. Text boxes are very useful controls which we can use in both VBA userforms and Excel worksheets. We can use them for varies things. Text boxes can use to get the input from users. They can be used to show the results of any calculation. Also this object can be linked to specific cell in a worksheet. These are few usages of text boxes.

So if you need to insert a text box to a worksheet, follow these simple steps.

First you need to go to the Developer tab. Developer tab is not displayed by default in Excel application. If you don't know how to show the developer tab, you can learn it from my earlier post

How to show the Developer tab in Excel

If you go to the Developer tab you will see a "Insert" button within the "Controls" group.

Click on that button. Then a drop down list will appear showing two types of controls. Form Controls and ActiveX cControls.

Click on the text box listed under the ActiveX Controls.

After you click on that text box, take your cursor to the worksheet. You will see that your cursor has changed to + mark. Then click on anywhere on the worksheet and create a text box with suitable height and width.

You can drag the text box to any place within the excel sheet. And if you need to change the properties of text box, then right-click on the text box and click on the Properties.

You will see long list of properties related to your newly created text box. You can change any property to suit to your requirements. Also you can follow naming conventions if you like when you give name to the text box. For an example if you use text box to get name of user, you can name the text box as txtName.

Add Data Analysis Command to Data tab in Excel 2013

Analysis tool pack is very usefull tool which you can use for complex Engineering and statistical analysis. This add-in is available after you install the Microsoft Excel. But you need to load it first to use in Excel application. In this post, I will explain how to load this usefull tool. Follow these simple steps.

First, click on the File tab.

Click on options.

You will get the "Excel Options" box. Select the Add-Ins.

Once you select the Add-Ins, You will see "Analysis ToolPak" under Inactive Application Add-ins. Select it and click on "Go" button below.

Once you click on the "Go" button you will get below box. Check "Analysis ToolPak" and click on "OK" button

Now you can find "Data Analysis" command under "Data" tab.

So now you can do data analysis like Regression, Fourier Analysis etc. using this Data Analysis command.

How to transpose Values in a column to a row using VBA

Today I'm going to create very simple macro to transpose values in a column to a Row. Assume we have names of the countries of the world in column A. So I have 257 country names in this column A. What I need to do is transpose these names to 1st row. Here is a image of my current worksheet.

So we are going to put all these country names to row 1 using a simple macro.

First we need to open the VBA editor. Let's use shortcut keys for that. Press Alt+F11

You will get a window similar to below.

Click on Insert == > Module

Then Copy paste below code to coding area.

Sub TransposeData()

Dim WS As Worksheet

Set WS = ActiveSheet

columnNumber = 1

For i = 1 To 257

Cells(1, columnNumber) = Cells(i, 1).Text

'Increment column number by 1
columnNumber = columnNumber + 1

Next i

MsgBox "Completed"

End Sub

Then click on any line between two lines Sub TransposeData( ) and End Sub to select the macro.

Then click on the run button.

You will get the following result.

Then you can delete the values in column A except cell A1.

So now let's consider about the code

Dim WS As Worksheet

This defines the worksheet variable

Set WS = ActiveSheet

So we have set active sheet to the variable WS

columnNumber = 1

This defines where we need to start entering the values. So here we order to insert from column 1 to column 257. We can change the number depending on where we need to enter our first value.

For i = 1 To 257

Next i

We used for next loop to go trough all the rows of column A

Cells(1, columnNumber) = Cells(i, 1).Text

This takes value from current cell of column A and assign it to relevant column of row 1.

columnNumber = columnNumber + 1

This line increment the columnNumber by 1. So next value of column A will be entered in next column of row 1.

Insert several images to a worksheet using VBA

In an earlier post I explained how to insert one image to an Excel worksheet. If you want to learn how to insert one image you can go to that post from below link.

Insert an Image to Excel Worksheet using VBA

Today's post is about how to modify that code to insert several images to a sheet. So I'm going to create very simple template to explain this. We will not have any text description or content in our sheet. This will only have images. But you can modify it to create your own template with lot of images, descriptions etc.

Assume below is the sheet where we need to insert our images.

And I have all images I want to insert in one folder. I have put them all to one folder so I can easily input file paths. But you don't need to put the all images to one folder.

So we have five images to insert. In our earlier post about inserting one image, we put the file path inside the code. Like that we can put paths of All the images in the code. But then it is not user friendly. Because our file may used by a person who doesn't know anything about VBA. So what we going to do is add another sheet call "File Paths" to this work book. And specify a column to put file paths of relevant images.

So now it is not a problem even our user doesn't know anything about VBA. Also we can put a button in this sheet to call our macro. So once the paths are entered, user can click that button. Then images will be inserted automatically to template sheet. And user will be directed to that sheet.

So now we need to develop a macro to do this. Actually this is much similar to code we used for insert one image. What we need to do is add a looping method to get paths of files one by one. So here is the full code.

Sub InsertSeveralImages()

Dim pic_Path As String 'File path of the picture
Dim cl As Range, Rng As Range
Dim WS_Templte As Worksheet

Set WS_Templte = Worksheets("Template")
Set Rng = Worksheets("File Paths").Range("C3:C7")

pastingRow = 2

For Each cl In Rng

pic_Path = cl.Value
Set InsertingPicture = WS_Templte.Pictures.Insert(pic_Path)

'Setting of the picture
With InsertingPicture
.ShapeRange.LockAspectRatio = msoTrue
.Height = 100
.Top = WS_Templte.Rows(pastingRow).Top
.Left = WS_Templte.Columns(3).Left
End With

pastingRow = pastingRow + 8

Next cl

Set myPicture = Nothing

WS_Templte.Activate

End Sub

Below is a brief explanation of the code.

First we need to define the variables

Dim pic_Path As String 'File path of the picture
Dim cl As Range, Rng As Range
Dim WS_Templte As Worksheet

We use "Template" sheet to insert the images.

Set WS_Templte = Worksheets("Template")

And Range("C3:C7") of "File Paths" sheet is the range where user put the file paths of the images.

Set Rng = Worksheets("File Paths").Range("C3:C7")

We use below line to define the row we going to insert our first image.

pastingRow = 2

pastingRow value should be incremented by suitable amount before go to next loop to give space to insert images. So we have put space of 8 rows in this example to insert one image. And you can notice that I have used below line before go to next loop.

pastingRow = pastingRow + 8

And we have used for each loop in this example to insert each and every image.

For Each cl In Rng

Next cl

And there is a coding part inside that For each loop. I have explained that part form my earlier post.

WS_Templte.Activate

And above line will direct the user to sheet where the images are inserted.

Excel-VBA Solutions

Pages