Array variables

Static array variables

Insted 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. When you use a ReDim-statement to change the array variable size, the variable contents is also erased. To avoid deleting the variable 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

Document last updated 2006-07-31 20:58:52 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-11-14 18:09:13 CET):

Re: I found it (sort of)
Almost there, the array size (count of items in the array) is actually:
lngArraySize = UBound(myArray) - LBound(myArray) + 1

You can loop through all array items like this:
For i = LBound(myArray) To UBound(myArray)
' do something
Next

Ryan from Chicago wrote (2006-11-14 16:24:30 CET):

I found it (sort of)
Well... I hacked my way through it with

msgbox( UBound(myArray) - LBound(myArray) )

irritating but good enough.

Ryan from Chicago wrote (2006-11-14 16:12:39 CET):

How to access members of array object
Hi Ole,

It seems like it should be simple-- I want to know the size of an array with a statement like:

dim myArray(10)
msgbox (myArray.Size)
(or .length, or .count, or whatever)

but I keep getting "Invalid Qualifier"

This is such a basic concept... am I brain dead and missing obvious syntax errors or is this another one of the gastly oversights in VBA?

Nicolas B. from Frankfurt/Main, Germany wrote (2006-08-01 13:18:55 CET):

Re: Array as argument
Hi Ole,

Thanks for this example: exactly what I needed (and even more!).

Ole P. from Norway wrote (2006-07-31 21:10:18 CET):

Re: Array as argument
See the updated example above :-)

Nicolas B. from Frankfurt/Main, Germany wrote (2006-07-31 18:46:48 CET):

Array as argument
Hi Ole,

Is it possible to give an array as argument a customized function/procedure?
I try:

Sub test(books() as string)
'to declare a procedure requiring a array of strings named "books",

but without success.
Any idea?

Ole P. from Norway wrote (2005-11-26 11:04:08 CET):

Re: Class Module
You can do this from the Workbook_Open event macro.
More details can be found here.

ahmad from Tehran/Iran wrote (2005-11-26 09:11:36 CET):

Class Module
Can I have a Class Module to be initialized just as the excel file containing the Module opens. I need to insert an array into that class module to be loaded by items listed in a worksheet in that file.Then, In my macros I don't want to refer to the cells of that worksheet but preferably to it's equivalent items in the array.