Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

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")

Contact Form

Name

Email *

Message *

Popular Posts