Dynamically Dimensioning A VBA Array

Question

Why am I unable to set the size of an array based on a variable   What s the best way around this   Dim NumberOfZombies as integer NumberOfZombies   20000 Dim Zombies NumberOfZombies  as New Zombie

User · Answer

You can also look into using the Collection Object. This usually works better than an array for custom objects, since it dynamically sizes and has methods for:

Add
Count
Remove
Item(index)

Plus its normally easier to loop through a collection too since you can use the for...each structure very easily with a collection.

User · Answer

You need to use a constant   CONST NumberOfZombies   20000 Dim Zombies NumberOfZombies  As Zombies   or if you want to use a variable you have to do it this way   Dim NumberOfZombies As Integer NumberOfZombies   20000  Dim Zombies   As Zombies  ReDim Zombies NumberOfZombies

User · Answer

You can use a dynamic array when you don t know the number of values it will contain until run-time   Dim Zombies   As Integer ReDim Zombies NumberOfZombies    Or you could do everything with one statement if you re creating an array that s local to a procedure   ReDim Zombies NumberOfZombies  As Integer   Fixed-size arrays require the number of elements contained to be known at compile-time  This is why you can t use a variable to set the size of the array   by definition  the values of a variable are variable and only known at run-time   You could use a constant if you knew the value of the variable was not going to change   Const NumberOfZombies   2000   but there s no way to cast between constants and variables  They have distinctly different meanings

User · Answer

You have to use the ReDim statement to dynamically size arrays    Public Sub Test       Dim NumberOfZombies As Integer     NumberOfZombies   20000     Dim Zombies   As New Zombie     ReDim Zombies NumberOfZombies   End Sub   This can seem strange when you already know the size of your array  but there you go

[arrays] Dynamically Dimensioning A VBA Array?

Examples related to arrays

Examples related to vba