Looping through a multidimensional array...

ease20022002

Board Regular
Joined
Jun 4, 2005
Messages
233
Hello,

I am populating an array as follows and I don't want to change the way I am populating the array. I am in VB 6.0. Here is the variable array I am populating:

Code:
varTable = Array(Array("LossCentral", "PremCentral"), Array("LossArchive", "PremArchive"))

When the array is populated it doesn't appear to be a 2 dimensional array (multidimensional). I have tried diming the array as varTable(0 to 1, 0 to 1), but then I can't populate the varTable variable with the preceding code syntax. When I redim varTable to

Code:
ReDim varTable(UBound(Array("LossCentral", "PremCentral")), UBound(Array("LossArchive", "PremArchive")))

it becomes a 2 dimensional array (multidimensional) so I see in the Locals Window varTable (0 to 1, 0 to 1) , but once I populate the array with the previous code syntax (1st code syntax), in the Locals Window it goes back to a 1 dimensional, varTable(0 to 1) but the variable has all the values as the two dimensional variable has. Meaning in the Locals Window I see the varTable (0 to 1), but when I drill into the variable, I see (0, 0) = "LossCentral", (0, 1) = "PremCentral", (1, 0) = "LossArchive" and (1, 1) = "PremArchive".

This variable fits into an SQL statement with other 1 dimensional variable arrays and some variables. I need varTable to be multidimensional. Here is the SQL that the varTable fits into:

Code:
For x = 0 To UBound(varDatabase) ' nested looping mechanism to assure that both loss and prem tables are deleted from each DB
        
    For y = 0 To UBound(varTable)
            
          strSQL = "DELETE * FROM " & varTable(x, y) & " IN '" & varDatabase(x) & "';                      
                              
        DoCmd.RunSQL (strSQL) ' runs SQL
            
    Next y
       
Next x

There is more SQL but the basics are there.

I guess the question is, how do I loop through a dimensionalized array that appears to be 1 dimension, but infact is two dimesions. I get the subscript is out of range when I run. I.e., I have tried looping through varTable by using: varTable(x,(x,y)), etc., and I can't figure it out. Maybe I can't dimensionalize an array like I did and loop through it. That is all I am trying to do. Loop through varTable when populating the array like I did in the first original syntax that I posted. I also do not want to dimensionalize the array by using something like the following if I don't have to:

Code:
varTable(0, 0) = LossCentral 
varTable(0, 1) = PremCentral
varTable(1, 0) = LossArchive
varTable(1, 1) = PremArchive

Thanks for any help.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

experiment with TRANPOSE
Code:
Sub test()
Dim varTable As Variant
Dim i As Long
Dim j As Long

varTable = Array(Array("LossCentral", "PremCentral"), Array("LossArchive", "PremArchive"))

    With Application
    varTable = .Transpose(.Transpose(varTable))
    End With

    For i = 1 To 2
        For j = 1 To 2
        MsgBox varTable(i, j)
        Next j
    Next i

End Sub
kind regards,
Erik
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi ease20022002

Another suggestion.

Your instruction allocates each of the dimensions dinamically. Refer to each one separately.

Please try:

Code:
Sub ArrayTest()

    vartable = Array(Array("LossCentral", "PremCentral"), Array("LossArchive", "PremArchive"))
    
    MsgBox vartable(0)(0)
    MsgBox vartable(0)(1)
    MsgBox vartable(1)(0)
    MsgBox vartable(1)(1)

End Sub

Hope this helps
PGC
 

ease20022002

Board Regular
Joined
Jun 4, 2005
Messages
233
Unfortunately I am in Access. I guess I should've mentioned that before. My apologies. There is no Transpose function in Access and I don't think I would create an object to use Excel functions.

If you have any other suggestions, I would greatly appreciate any help provided.

Thanks
 

ease20022002

Board Regular
Joined
Jun 4, 2005
Messages
233

ADVERTISEMENT

Thanks guys, I figured it out.

I started to look at the locals window and varTable was populated as varTable(0) (1), which pcg01 posted, but I didn't realize.

Thanks guys for both your help. I am will be using the transpose functionality b.c I see where I can use it.

Thanks again to both you guys. Thanks!
 

ease20022002

Board Regular
Joined
Jun 4, 2005
Messages
233

ADVERTISEMENT

Yea, I know there is one, but since it was a looping issue I figured I would come to the experts.

Thanks again!
 

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
I am having a similar problem. I'm trying to loop through a 2d array. The 1d will always be 25, the 2d will have different amounts. Quite often the members of the 1st dimensional will be empty which is the point of the isarray(sent) code. I'm getting a subscript out of range at the part which says for j = 1 to ubound(sent,2)

Code:
For i = 1 To 25
    If IsArray(sent(i)) Then
        For j = 1 To UBound(sent, 2)
            If concat_multi = "" Then
            concat_multi = sent(i, j)
            Else
            concat_multi = concat_multi & " & " & sent(i, j)
            End If
        Next
        ActiveCell.Offset(1) = concat_multi
        concat_multi = ""
    End If
Next

Here is a screen shot

Screenshot2014-10-13at110627PM_zps144c0ffa.png
 

kylefoley76

Well-known Member
Joined
Mar 1, 2010
Messages
1,550
Problem solved.

For j = 1 to ubound(sent(i))

is the proper code. Also changed to

sent(i)(j)
 

Forum statistics

Threads
1,136,308
Messages
5,674,990
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top