[arrays] 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

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)

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.