×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Defining global multidimensional array

Defining global multidimensional array

Defining global multidimensional array

(OP)
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

CODE

Public GlobalArray() As Variant 

CODE

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?

RE: Defining global multidimensional array

You have never stated how big your array needs to be.
You need to ReDim your GlobalArray

CODE

...
Dim V As Integer

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

ReDim GlobalArray(4, 15)

For Each vItem In OldRoles
... 

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


---- Andy

There is a great need for a sarcasm font.

RE: Defining global multidimensional array

(OP)
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.

RE: Defining global multidimensional array

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...? smile )


---- Andy

There is a great need for a sarcasm font.

RE: Defining global multidimensional array

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

CODE -->

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 Redim Preserwe) in the loop.

combo

RE: Defining global multidimensional array

(OP)
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.

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close