Public array variable of variable size

Question

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...

But you would simply redim the array inside the sub foo() ReDim varArray(1 To int1) As Variant — Scott Craner
– Scott Craner, Commented Jan 19, 2017 at 20:21

Eric Harlan · Accepted Answer · 2017-01-19 20:34:08Z

1

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

answered Jan 19, 2017 at 20:34

Eric Harlan

3842 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fission Chips Over a year ago

Thanks very much, just what I was looking for.

Collectives™ on Stack Overflow

Public array variable of variable size

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related