Excel VBA String Array

When we have multiple variables to be declared in a VBA Code, we can declare the exact number of a variable with data type we want. But this process is quite lengthy when the variable count goes above 5. Why declare variables multiple times when we can frame that into String Array. A VBA String Array is used when we need to hold more than one string value with a string variable. This looks tricky but in reality, it is very easy to implement. We don’t have to declare one type of variable multiple times if each variable store’s different values. This slash in a huge VBA Code is done using Excel VBA String Array.

How to Use VBA String Array?

To use VBA String Array in any code, it is very simple. For this, we just need to define how many variables we will require. This will be first done using DIM. Suppose, if we want 10 variables of any data type so that could be done as shown below.

Code:

Sub VBA_StringArray()

Dim NameOfVariable(1 To 10) As DataType

End Sub

We can choose any name in place Name Of Variable and any data type in Data Type box as highlighted above.

Examples of String Array in Excel VBA

Below are the examples of an excel VBA string array.

You can download this VBA String Array Excel Template here – VBA String Array Excel Template

Example #1

In this example, we will see how to use String Array in VBA without any limit in variables. Here, we will not define the length of variables, instead, we will directly create string array and use the number of variables as we our need. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Step 2: Define the subprocedure preferably in the name of VBA String Array or we can choose any name as per our choice.

Code:

Sub VBA_StringArray1()

End Sub

Step 3: Now we will be using Employee names for creating the array. For this declare a variable in a similar name and put brackets “( )” after that. And choose any data type. We can choose String or Integer or Variant. But as the data may vary so using Variant will be good.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant

End Sub

Step 4: Now use the same variable which we declared above and use Array function.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array(

End Sub

As we can see, as per the syntax of Array, it only allows Variant data type and Argument List (). The reason for seeing the Variant data type is because we can store any type of value in it.

Step 5: Now consider the names of employees which we will be using here. We have Anand, Shraddha, Aniket, Ashwani, and Deepinder as Employee names. And it should be in the way as we do concatenation.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array("Anand", "Shraddha", "Aniket", "Ashwani", "Deepinder")

End Sub

Step 6: And to print the values stored in the Employee Data array we will use MsgBox. And array will be in the sequence of numbers at which we have defined.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array("Anand", "Shraddha", "Aniket", "Ashwani", "Deepinder")
MsgBox EmployeeData(0) & ", " & EmployeeData(1) & ", " & EmployeeData(3) & ", " & EmployeeData(4)

End Sub

Step 7: Run this code by hitting the F5 or Run button which is placed on the topmost ribbon of VBE.

Step 8: We will get the message box with all the employee names in the sequence we put it.

Step 9: Let’s try to change the sequence of Employee Data array. Here we have exchanged 0 and 4 with each other.

Code:

Sub VBA_StringArray1()

Dim EmployeeData() As Variant
EmployeeData = Array("Anand", "Shraddha", "Aniket", "Ashwani", "Deepinder")
MsgBox EmployeeData(4) & ", " & EmployeeData(1) & ", " & EmployeeData(3) & ", " & EmployeeData(0)

End Sub

Step 10: Let’s run this code again. We will notice, Employee Data name Deepinder is now moved to first place and Anand is at 4^th place.

Example #2

In this example, we will set the position of cells in the array and get the combination of output by that. For this, we will use the same employee names which we have seen in. For this, follow the below steps:

Step 1: Write the subprocedure.

Code:

Sub VBA_StringEmployeDataay2()

End Sub

Step 2: Define a variable as Variant with cell positioning as (1, 3) Which 1 shows the 2^nd position.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant

End Sub

Step 3: Now we will assign each employee name to different co-ordinates. Such as, at 1^st row, 2^nd column we have set employee Anand.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"

End Sub

Step 4: Similarly we will choose different co-ordinates from (1, 3) position and give each employee name in a different position.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"

End Sub

Now to get the output from the defined array, we will use the message box.

Step 5: We have used the position of co-ordinates. Such as for (0, 1).

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"
MsgBox ("EmployeData In Index 0,1 : " & EmployeData(0, 1))

End Sub

Step 6: Similarly, another message box to see other values stored in different co-ordinates.

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"
MsgBox ("EmployeData In Index 0,1 : " & EmployeData(0, 1))
MsgBox ("EmployeData In Index 1,2 : " & EmployeData(1, 2))

End Sub

Step 7: Once done, compile the code by hitting the F8 or Run button. We will see, the values stored in the array (0, 1) is Anand.

Step 8: And the second array (1, 2) stores the value as Aniket.

This is how co-ordinating in String array works.

Step 9: What if we change the array co-ordinates for the second message box from (1, 2) to (2, 2).

Code:

Sub VBA_StringEmployeDataay2()

Dim EmployeData(1, 3) As Variant
EmployeData(0, 1) = "Anand"
EmployeData(0, 2) = "Shraddha"
EmployeData(1, 2) = "Aniket"
EmployeData(1, 3) = "Ashwani"
EmployeData(0, 0) = "Deepinder"
MsgBox ("EmployeData In Index 0,1 : " & EmployeData(0, 1))
MsgBox ("EmployeData In Index 1,2 : " & EmployeData(2, 2))

End Sub

Step 10: We will see, once the first array message box shows the value, the second message box will give the error, as Subscript Out Of range. Which means, we have selected the range which either incorrect or not exists.

Pros of VBA String Array:

VBA String Array can hold any type of value.
There is no limit to storage capacity in String Array.

Things to Remember

We can create 2D and 3D String array both.
Called value of array should be in the range of defined values.
It is not advised to fix the length of the array. Because if the value of an array is out of range, then we will end up getting the error.
Also, save the file in macro enable excel format to preserve the written VBA Code.
We can use different functions such as LBOUND, UBOUND, Split, Join, Filter, etc. in String Arrays using Loops.