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.