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

User · Answer

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    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  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

User · Answer

The Array index only accepts a long value   You declared iCounter as an integer   You should declare it as a long

User · Answer

You have to declare the array variable as an array   Dim test 10  As Variant

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

[arrays] How do I declare an array variable in VBA?

Examples related to arrays

Examples related to vba