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.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!