How do I declare an array variable in VBA?

calendar_today Asked Apr 17, 2011
thumb_up 25 upvotes
history Updated April 16, 2026

Question posted 2011 · +17 upvotes

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

Accepted answer +25 upvotes

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

2 code variants in this answer

  • Variant 1 — 1 lines, starts with Dim test(1) As String 'declares an array with 2 elements …
  • Variant 2 — 8 lines, starts with Dim test() As String

Top vba Q&A (6)

+25 upvotes ranks this answer #15 out of 81 vba solutions on this site — top 19%.
vba