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.

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.

Contact Form

Name

Email *

Message *

Popular Posts