CODEWITHSUNDEEP
arraysvba
sooprise
sooprise

Reputation: 23187

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

Upvotes: 30

Views: 136197

Answers (5)

Ho Wai Yan Arthur
Ho Wai Yan Arthur

Reputation: 21

There is a stupid way to do it XD I am breaking an address into different cell by counting character.

Just show you directly.

q = 1
ReDim Sentence1(q) As String
ReDim Sentence1T(q) As String


Sentence1(q) = Result(0)

    For p = 1 To WordCount - 1
        Sentence1(q) = Sentence1(q) & " " & Result(p)
        
            If Len(Sentence1(q)) > 40 Then
            
                For r = 1 To q
                    Sentence1T(r) = Sentence1(r)
                Next r
                
                q = q + 1
                ReDim Sentence1(q) As String
                
                 For r = 1 To q - 1
                    Sentence1(r) = Sentence1T(r)
                Next r
                
                ReDim Sentence1T(q) As String
                
            End If
    Next p

So every time you redim it, you lost your data. Just put it in another array. Hope you like it!

Upvotes: 0

carny666
carny666

Reputation: 2410

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)

Upvotes: 1

Fink
Fink

Reputation: 3436

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.

Upvotes: 2

Chris Spicer
Chris Spicer

Reputation: 2194

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!

Upvotes: 4

Cody Gray
Cody Gray

Reputation: 244722

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.

Upvotes: 59

Related Questions