How do I declare an array variable in VBA?

Question

I need to add the var in array

Public Sub Testprog()  Dim test As Variant Dim iCounter As Integer  If test = Empty Then     iCounter = 0     test(iCounter) = "test" Else     iCounter = UBound(test) End If End Sub 

Getting error at test(iCounter) = "test"

Please suggest some solution

Answer 1

Generally, you should declare variables of a specific type, rather than Variant. In this example, the test variable should be of type String.

And, because it's an array, you need to indicate that specifically when you declare the variable. There are two ways of declaring array variables:

1. If you know the size of the array (the number of elements that it should contain) when you write the program, you can specify that number in parentheses in the declaration:

   Dim test(1) As String   'declares an array with 2 elements that holds strings 

   This type of array is referred to as a static array, as its size is fixed, or static.

2. If you do not know the size of the array when you write the application, you can use a dynamic array. A dynamic array is one whose size is not specified in the declaration (Dim statement), but rather is determined later during the execution of the program using the ReDim statement. For example:

   Dim test() As String Dim arraySize As Integer  ' Code to do other things, like calculate the size required for the array ' ... arraySize = 5  ReDim test(arraySize)  'size the array to the value of the arraySize variable 

Answer 2

Further to Cody Gray's answer, there's a third way (everything there applies her as well):

You can also use a dynamic array that's resized on the fly:

Dim test() as String Dim arraySize as Integer  Do While someCondition     '...whatever     arraySize = arraySize + 1     ReDim Preserve test(arraySize)     test(arraySize) = newStringValue Loop 

Note the Preserve keyword. Without it, redimensioning an array also initializes all the elements.