How do I declare an array variable in VBA?

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) = "te开发者_如何学编程st"
Else
    iCounter = UBound(test)
End If
End Sub

Getting error at test(iCounter) = "test"

Please suggest some solution

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
   

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.

Further to RolandTumble's answer to Cody Gray's answer, both fine answers, here is another very simple and flexible way, when you know all of the array contents at coding time - e.g. you just want to build an array that contains 1, 10, 20 and 50. This also uses variant declaration, but doesn't use ReDim. Like in Roland's answer, the enumerated count of the number of array elements need not be specifically known, but is obtainable by using uBound.

sub Demo_array()
    Dim MyArray as Variant, MyArray2 as Variant, i as Long

    MyArray = Array(1, 10, 20, 50)  'The key - the powerful Array() statement
    MyArray2 = Array("Apple", "Pear", "Orange") 'strings work too

    For i = 0 to UBound(MyArray)
        Debug.Print i, MyArray(i)
    Next i
    For i = 0 to UBound(MyArray2)
        Debug.Print i, MyArray2(i)
    Next i
End Sub

I love this more than any of the other ways to create arrays. What's great is that you can add or subtract members of the array right there in the Array statement, and nothing else need be done to code. To add Egg to your 3 element food array, you just type

, "Egg"

in the appropriate place, and you're done. Your food array now has the 4 elements, and nothing had to be modified in the Dim, and ReDim is omitted entirely.

If a 0-based array is not desired - i.e., using MyArray(0) - one solution is just to jam a 0 or "" for that first element.

Note, this might be regarded badly by some coding purists; one fair objection would be that "hard data" should be in Const statements, not code statements in routines. Another beef might be that, if you stick 36 elements into an array, you should set a const to 36, rather than code in ignorance of that. The latter objection is debatable, because it imposes a requirement to maintain the Const with 36 rather than relying on uBound. If you add a 37th element but leave the Const at 36, trouble is possible.

As pointed out by others, your problem is that you have not declared an array

Below I've tried to recreate your program so that it works as you intended. I tried to leave as much as possible as it was (such as leaving your array as a variant)

Public Sub Testprog()
    '"test()" is an array, "test" is not
    Dim test() As Variant
    'I am assuming that iCounter is the array size
    Dim iCounter As Integer

    '"On Error Resume Next" just makes us skip over a section that throws the error
    On Error Resume Next

    'if test() has not been assigned a UBound or LBound yet, calling either will throw an error
    '   without an LBound and UBound an array won't hold anything (we will assign them later)

    'Array size can be determined by (UBound(test) - LBound(test)) + 1
    If (UBound(test) - LBound(test)) + 1 > 0 Then
        iCounter = (UBound(test) - LBound(test)) + 1

        'So that we don't run the code that deals with UBound(test) throwing an error
        Exit Sub
    End If

    'All the code below here will run if UBound(test)/LBound(test) threw an error
    iCounter = 0

    'This makes LBound(test) = 0
    '   and UBound(test) = iCounter where iCounter is 0
    '   Which gives us one element at test(0)
    ReDim Preserve test(0 To iCounter)

    test(iCounter) = "test"
End Sub

You have to declare the array variable as an array:

Dim test(10) As Variant

David, Error comes Microsoft Office Excel has stopped working. Two options check online for a solution and close the programme and other option Close the program I am sure error is in my array but I am reading everything and seem this is way to define arrays.

The Array index only accepts a long value.

You declared iCounter as an integer. You should declare it as a long.