Search code examples
arraysvba

Dynamically Dimensioning A VBA Array?

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
Solution

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