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 

Answer 1

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.

Answer 2

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!