Defining global multidimensional array 1

[03Explorer]

I want to have an array that has 5 deep with x values (so it would be V=0-4 and X = open ended)

Here is what I have currently

Public GlobalArray() As Variant

Function ObtainOldWorldData()
Dim OldRoles As Variant
Dim vItem As Variant

OldRoles = Array("cboCRM", "cboPM1", "cboPM2", "cboLC1", "cboLC2", "cboSM1", "cboSM2", "cboSC1", "cboSC2", "cboSC3", "cboCO1", "cboCO2", "cboCO3", "cboAN1", "cboAN2", "cboAN3")
V=0

For Each vItem In OldRoles
    Select Case vItem
        Case "cboCRM"
            GlobalArray(V, 0) = vItem
            GlobalArray(V + 1, 0) = "CRM"
            GlobalArray(V + 2, 0) = 7
    end select
NEXT

I get the error "Script out of range" when I get to the first line below the Case "cboCRM".

Am I declaring the GlobalArray incorrectly?

 

[Andrzejek]

You have never stated how big your array needs to be.
You need to [tt]ReDim[/tt] your [tt]GlobalArray[/tt]

...[red]
Dim V As Integer[/red]

OldRoles = Array("cboCRM", "cboPM1", "cboPM2", "cboLC1", "cboLC2", "cboSM1", "cboSM2", "cboSC1", "cboSC2", "cboSC3", "cboCO1", "cboCO2", "cboCO3", "cboAN1", "cboAN2", "cboAN3")
V = 0
[blue]
ReDim GlobalArray(4, 15)[/blue]

For Each vItem In OldRoles
...

Do you keep the names of your combo boxes in this array...?


---- Andy

There is a great need for a sarcasm font.

 

[03Explorer]

That's my issue. I am uncertain how big the array will be because the end user is going to define how many roles they are going to populate. It could be 15 or it could be 5 or it could be 20. I was hoping to be able to have it be open-ended.

 

[Andrzejek]

There may be other ways to accomplish what you want. But you would need to state your full requirements in plain English.
There are always other ways 'to skin the cat' (and why would you want to skin the poor animal...? )


---- Andy

There is a great need for a sarcasm font.

 

[combo]

So you can calculate the items first and redim according to result:

Dim OldRoles As Variant
Dim OldRolesSize As Variant
Dim vItem As Variant

OldRoles = Array("cboCRM", "cboPM1", "cboPM2", "cboLC1", "cboLC2", "cboSM1", "cboSM2", "cboSC1", "cboSC2", "cboSC3", "cboCO1", "cboCO2", "cboCO3", "cboAN1", "cboAN2", "cboAN3")
OldRolesSize = UBound(OldRoles, 1) - LBound(OldRoles, 1) + 1
Redim OldRoles(4, OldRolesSize)

I don't know what you want to get, anyway you can also dynamically resize the last dimension of array (with [tt]Redim Preserwe[/tt]) in the loop.



combo

 

[03Explorer]

Okay I figured out a work around. What I was hoping to do was take a static form of given fields (not all are mandatory). I want to know how many fields are populated so I can then define the multidimensional array. I made a function that cycles through my given fields and counts those that are populated giving me my array dimension.

Public Function CountOldRoles() As Integer
Dim OldRoles As Variant
Dim i As Integer
Dim vItem As Variant

CountOldRoles = 0
OldRoles = Array("cboCRM", "cboPM1", "cboPM2", "cboLC1", "cboLC2", "cboSM1", "cboSM2", "cboSC1", "cboSC2", "cboSC3", "cboCO1", "cboCO2", "cboCO3", "cboAN1", "cboAN2", "cboAN3")


For Each vItem In OldRoles
    If Not IsNull(Forms![frmAAS].Controls(vItem).Value) Then
        CountOldRoles = CountOldRoles + 1
    End If
Next

End Function

** sorry, I didn't refresh my screen from day prior to see the other option to do what I did... I am leaving mine here for another example of how to count the blank fields.