excel vba dynamic array code example

Example 1: vba string array

'In VBA a dynamic string array is declared in the following ways:

Dim strArr1() As String
Dim strArr2$()

'Neither declaration style is prefered. They are exactly equivalent.

'Both ways above produce a dynamic array, which means that the exact size of
'the array (how many elements) is specified later in code:

Redim strArr1(1 to 10)

'To instead declare a static string array (an array with a fixed size), 
'specify the size within the Dim statement:

Dim strArr1(1 to 10) As String
Dim strArr2$(0 to 4)

'ReDim() cannot later be used on static arrays. Their size is fixed.

'Once a string array is declared, the array elements are assign one at a time:

strArray1(1) = "abc"

'However, the Split() function can be used to split a string and assign to and
'to also size a dynamic string array in one go:

Dim strArr3$()
strArray3 = Split("a,b,c", ",")

'strArray3 will now contain three elements, "a", "b", and "c".

'Note: String arrays can only conatin text values. When numbers are assigned
'      to string array elements, the number is first coerced to a string value.
'      Boolean and Date values are likewise coerced to text values.
'      Objects CANNOT be assinged to string array elements.
'
'      For the above reasons (and especially with Excel data) it may be better
'      to declare and use Variant arrays instead of String arrays. Variants
'      can be assigned any data type. The trade-off is that manipulating 
'      Variants is slightly slower than base data types.
'
'      Variants are the default data type in VBA so the following declares
'      an array of Variants:

Dim vArr1()

'      But the data type may also be specified explicitly:

Dim vArr1() As Variant

'      The Variant data type does not have a specific Type Declaration Character.
'      For example, the String data type has the '$' type declaration character
'      which was demonstrated at the top of this page. But since the Variant is
'      VBA's default, no type declaration character is needed. Not specifying
'      a type results in a variable's type being a Variant, by default.

'Note: it is important to realize that since a Variant can hold any other type
'      of data, it can also hold an array. It is EXTREMELY common in VBA to 
'      declare a single Variant variable that will later be assinged an array:

Dim v

'	   The above variable is a single Variant. It is not an array. Variants
'      can be assinged numbers, dates, Booleans, error values, objects, 
'      essentially anything. And in addition, variants can also
'      be assigned arrays of any type:

v = Array(1, 2, 3, "a", "b", "c", True, False, #1/1/2021#)

'      The above line creates an array of 9 Variant values and assigns the 
'      entire array to the Variant existing variable, 'v'.
'
'      Perhaps the most common way to assign an array of Variants to 
'      an existing Variant variable is to pull data from a worksheet range:

v = Sheet1.Range("A1:A10").Value

'      The above statement assigns a 2-dimension array to v, ten rows deep 
'      and one column wide. The values and data types of the 
'      ten elements will correspond to the values and types
'      in the Sheet1 range, A1:A10. It is important to realize that 
'      the VBA array created here is a copy of the data in the worksheet
'      range. Changing one does not affect the other. But it is possible
'      to write the VBA array back to Excel to the original range, or 
'      to any range in any worksheet in any open workbook.
'
'
'

Example 2: excel vba initialize entire array

'VBA function to create a 2D array with every element set 
'to the same initial value:

Function Init2D(rows, cols, Optional val)
    Dim i&, j&
    ReDim v(1 To rows, 1 To cols)
    If Not IsMissing(val) Then
        For i = 1 To rows
            For j = 1 To cols
                v(i, j) = val
            Next
        Next
    End If
    Init2D = v
End Function

'This version uses no loops:
Function Init2D(rows, cols, Optional val = "")
    Const NUMER = "index(offset(a1,,,ROWS,COLS)*0+VAL,,)"
    Const ALPHA = "index(rept("""",len(offset(a1,,,ROWS,COLS))<0)&""VAL"",,)"
    DoEvents
    Init2D = Evaluate(Replace(Replace(Replace(IIf(IsNumeric(val), NUMER, ALPHA), "VAL", val), "ROWS", rows), "COLS", cols))
End Function

'--------------------------------------------------------------------

'10 rows, 1 column, all elements = 0:
a = Init2D(10, 1, 0)

'20 rows, 5 columns, all elements = "abc":
a = Init2D(20, 5, "abc")

'--------------------------------------------------------------------

'1D array version:
Function Init1D(elems, Optional val, Optional base = 1)
    Dim i&, max&
    max = base + elems - 1 
    ReDim v(base To max)
    If Not IsMissing(val) Then
        For i = base To max
            v(i) = val
        Next
    End If
    Init1D = v
End Function