Is there a way to initialize a public array based on a condition?

user_2165379 0
Viewed 74

I have an array with 3 dimensions. I would like to access this array from several modules in Excel VBA. For that reason I use Public to declare it. Currently I use this code:

Public MyData(1 To 400, 1 To 10, 1 To 3000) As Double

I would like to initialize the first and third variable once at startup of my code at a certain value depending on the sizes of my data.

Since ReDim can only be used for the last variable in an array I am trying to do something like this although this is not possible:

If x < 400 and z < 3000 Then    
    Public MyData(1 To 400, 1 To 10, 1 To 3000) As Double 
End If

If x < 200 and z < 1000 Then    
    Public MyData(1 To 200, 1 To 10, 1 To 1000) As Double 
End If

Is there a way to initialize an array based on a condition (the value of x and z below a certain level)?

Thank you!

multidimensional-array vba excel
2 Answers

  1. ReDim can be done for any dimension. ReDim Preserve is restricted to modify only the last dimension, keeping the existing loaded items value. But also for ReDim Preserve a workaround exists: You make the last dimension the one you want to redim preserve and finally transpose the array;

  2. ReDim can be done only for a declared array but, without dimensions setting during its declaration. So, declaring it in the next way:

Public MyData() As Double

will work as you need...

  1. A Public variable can only be placed on top of your module, in the declarations area. So, no any such code is allowed there. You can only ReDim it as you tried, in a module Sub/Function, only if you declare it as I recommended above
user_22333080

Here is a typical ReDim example:

Public multiArray()

Sub initL()
    Dim larry As Long, moe As Long, curley As Long
    
    larry = 7
    moe = 11
    curley = 13
    
    ReDim multiArray(1 To larry, 1 To moe, 1 To curley)
End Sub
user_24746560
Related