CODEWITHSUNDEEP
arraysexcelvbaglobal-variables
Fission Chips
Fission Chips

Reputation: 55

Public array variable of variable size

I'd like to have a global array variable in Excel VBA whose array size depends on a second global variable.

I'm trying something like this..

Public int1 As Integer

Sub foo()
    int1 =  '##read from ini file
    Public varArray(1 To int1) As Variant
    For i = 1 To int1
        varArray(i)= '##read from ini file
    Next i
    Call SomeProcedure '##in another module where varArray is used
End Sub

Now obviously this doesn't work since global formulas must be declared outside the sub, and it seems that also Arrays need a constant when I give them a range. Is there any way I can do it by just declaring

Public varArray() As Integer

and then give it a number of elements later? It doesn't seem to let me do that, and nothing else I tried...

Upvotes: 2

Views: 3335

Answers (1)

Eric Harlan
Eric Harlan

Reputation: 384

As you mentioned, the array has to be declared outside the sub to be global.

To resize the array, use the ReDim command inside of another function, after you determine how big the array needs to be.

Dim varArray() As Variant

Public Sub Foo()
    'Determine size of array
    'For example, let's say you determine you need 15 elements
    ArraySize = 15 'Your variable here, instead of 15

    ReDim varArray(1 To ArraySize)

    'Debug Print statement to confirm array dimensions
    Debug.Print LBound(varArray) & " - " & UBound(varArray)
End Sub

Upvotes: 1

Related Questions