vba dynamic array declaration 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
Example 3: excel vba create an array from a range
v = [a1:b20]
'If v is dimensioned as a variant then he above creates a 2d array,
'20 rows high by 2 columns wide and those 40 array elements contain
'the values from the specified range.
'The square brackets are shorthand notation. Another way to code the
'same thing is:
v = Range("a1:b20")
'In both of the above examples, the Range object is returning its
'default... the Range.Value property. But keep in mind that the
'Range.Value2 property is about 20% quicker. So it could be more
'performant to code these two examples like so:
v = [a1:b20].Value2
v = Range("a1:b20").Value2