Array variable basics
2006-07-31 Arrays 0 255
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 SubIf 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