VBA Declare & Initilize String Array

This tutorial will teach you how to declare and initialize a string array in VBA.

Declaring a String variable

When you declare a string variable in VBA, you populate it by adding a single string to the variable which you can then use in your VBA code.

Dim strName as String
StrName = "Bob Smith"

Declaring a Static String Array

If you want to populate an array with a string of values, you can create a STATIC string array to do so.

Dim StrName(2) as String 
StrName(0) = "Bob Smith"
StrName(1) = "Tom Jones"
StrName(2) = "Mel Jenkins"

Remember that the Index of an Array begins at zero – so we declare the Array size to be 2 – which then enables the Array to hold 3 values.

Instead, you can explicitly define the start and end positions of an array:

Dim StrName(1 to 3) as String 
StrName(1) = "Bob Smith"
StrName(2) = "Tom Jones"
StrName(3) = "Mel Jenkins"

Declaring a Variant Array using the Array function

If you want to populate an array with a string of values without implicitly stating the size of the Array, you can create a variant array and populate it using the Array function.

Dim strName as Variant
strName = Array("Bob Smith", "Tom Jones", "Mel Jenkins")

Declaring a String Array using the Split Function

If you want to keep the variable as a string but do not want to implicitly state the size of the Array, you would need to use the Split function to populate the array.

Dim strName() as String 
strNames = Split("Bob Smith, Tom Jones, Mel Jenkins")

The Split function allows you to keep the data type (eg String) while splitting the data into the individual values.