xlvba string array code example

Example: 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.
'
'
'