Dynamically Dimensioning A VBA Array?

You can use a dynamic array when you don't know the number of values it will contain until run-time:

Dim Zombies() As IntegerReDim 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.

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!

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.