Static array variables

Instead of using several unique variables to store information, you can use an array variable. When you know how many elements you need to store in the array, you can use a static array variable like this:

Sub TestStaticArray()
' store 5 worksheet names in the array variable MyNames()
Dim MyNames(1 to 5) As String ' declares a static array variable
Dim iCount As Integer
    For iCount = 1 To 5
        MyNames(iCount) = ThisWorkbook.Worksheets(iCount).Name
    Next iCount
    For iCount = 1 To 5
        MsgBox "Content of MyNames(" & iCount & ") = " & MyNames(iCount)
    Next iCount
    Erase MyNames() ' deletes the varible contents, free some memory
End Sub

Dynamic array variables

Dynamic array variables are useful when you in advance don't know how many elements that you need to store information about. You declare dynamic array variables just like a static array variable, except that you don't give any information about the array size:

Sub TestDynamicArray()
' stores all worksheet names in the array variable MyNames()
Dim MyNames() As String ' declares a dynamic array variable
Dim iCount As Integer
Dim Max As Integer
    Max = ThisWorkbook.Worksheets.Count ' finds the maximum array size
    ReDim MyNames(1 to Max) ' declares the array variable with the necessary size
    For iCount = 1 To Max
        MyNames(iCount) = ThisWorkbook.Worksheets(iCount).Name
    Next iCount
    ' the next line is useful if you don't know 
    ' the upper or lower limit of an array variable
    For iCount = LBound(MyNames) To UBound(MyNames)
        MsgBox "Content of MyNames(" & iCount & ") = " & MyNames(iCount)
    Next iCount
    Erase MyNames() ' deletes the varible contents, free some memory
End Sub

If you know that you will need an array variable with 1000 items, use a static variable. The downside is that you will use memory for a 1000 items every time, also in the cases that you only store information about 10 items. If you use a dynamic array variable you will use the memory more efficient.

Sometimes it's not possible to calculate how large the array variable will need to be. In these cases the size of the array variable need to be increased as necessary by using a ReDim-statement. Try to avoid using the ReDim-statement many times, e.g. inside a loop. Using the ReDim-statement many times may cause your procedure to run very slowly. It might actually be faster to loop through the unknown sized data to count it first and then ReDim the array variable once before looping through the data one more time to populate the array with some content. When you use a ReDim-statement to change the array variable size, the array variable content is also erased. To avoid deleting the contents when you ReDim the array variable you can use the ReDim Preserve-statement:

Sub GetFileNameList()
' stores all the filenames in the current folder
Dim FolderFiles() As String ' declares a dynamic array variable
Dim tmp As String, fCount As Long
    fCount = 0
    tmp = Dir("*.*")
    Do While tmp <> Empty
        fCount = fCount + 1
        ReDim Preserve FolderFiles(1 to fCount) 
        ' declares the array variable again (size+1)
        FolderFiles(fCount) = tmp
        tmp = Dir
    Loop
    For fCount = LBound(FolderFiles) To UBound(FolderFiles)
        Debug.Print "File #" & fCount, FolderFiles(fCount)
    Next fCount
    MsgBox fCount & " filenames are found in the folder " & CurDir
    Erase FolderFiles ' deletes the varible contents, free some memory
End Sub

Passing array variables to procedures and functions

Below you will find some examples showing how you can do this:

Sub PassingArraysToFunctionsAndProcedures()
Dim arrVariantArray As Variant ' array can store numbers and text
Dim arrNumericArray(0 To 9) As Long ' array can store numbers only
Dim arrDynamicArray() As Long ' array can store numbers only
Dim i As Long
    ' populate a variant array
    arrVariantArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, "one", "two", "three")
    i = AddVariantArrayItems(arrVariantArray)
    MsgBox "Variant Array Items Sum: " & i, vbInformation, "Before Update"
    
    ' pass an array to a procedure without updating the variable content
    NonValueChangingProcedure arrVariantArray
    i = AddVariantArrayItems(arrVariantArray)
    MsgBox "Variant Array Items Sum: " & i, vbInformation, "After (no change)"
    
    ' populate a numeric array
    For i = LBound(arrNumericArray) To UBound(arrNumericArray)
        arrNumericArray(i) = i * 10
    Next i
    i = AddVariantArrayItems(arrNumericArray)
    MsgBox "Numeric Array Items Sum: " & i, vbInformation
    
    ' populate a dynamic numeric array
    ReDim arrDynamicArray(0 To 100)
    For i = LBound(arrDynamicArray) To UBound(arrDynamicArray)
        arrDynamicArray(i) = i * 10
    Next i
    i = AddVariantArrayItems(arrDynamicArray)
    MsgBox "Dynamic Numeric Array Items Sum: " & i, vbInformation, "Before Update"
    
    ValueChangingProcedure arrDynamicArray
    i = AddVariantArrayItems(arrDynamicArray)
    MsgBox "Dynamic Numeric Array Items Sum: " & i, vbInformation, "After (new sum)"
    
End Sub

Function AddVariantArrayItems(arrItems As Variant) As Long
Dim i As Long, lngSum As Long
    If IsArray(arrItems) Then
        For i = LBound(arrItems) To UBound(arrItems)
            On Error Resume Next ' ignore non-numeric errors
            lngSum = lngSum + arrItems(i)
            On Error GoTo 0
        Next i
    End If
    AddVariantArrayItems = lngSum
End Function

Function AddNumericArrayItems(arrInput() As Long) As Long
Dim i As Long, lngSum As Long
    For i = LBound(arrInput) To UBound(arrInput)
        lngSum = lngSum + arrInput(i)
    Next i
    AddNumericArrayItems = lngSum
End Function

Sub ValueChangingProcedure(arrInput() As Long) 
' arrays must be passed ByRef (default in VBA)
Dim i As Long
    For i = LBound(arrInput) To UBound(arrInput)
        arrInput(i) = arrInput(i) + 1
    Next i
End Sub

Sub NonValueChangingProcedure(ByVal arrInput As Variant) 
' variant arrays can be passed ByVal or ByRef
Dim i As Long
    If IsArray(arrInput) Then
        For i = LBound(arrInput) To UBound(arrInput)
            On Error Resume Next
            arrInput(i) = arrInput(i) + 1
            On Error GoTo 0
        Next i
    End If
End Sub

Like this post