Looping through a multidimensional array...


Board Regular
Jun 4, 2005

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:

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

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:

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:

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

Thanks for any help.

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

experiment with TRANPOSE
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,
Upvote 0
Hi ease20022002

Another suggestion.

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

Please try:

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

Upvote 0
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!
Upvote 0
Yea, I know there is one, but since it was a looping issue I figured I would come to the experts.

Thanks again!
Upvote 0
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)

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)
            concat_multi = concat_multi & " & " & sent(i, j)
            End If
        ActiveCell.Offset(1) = concat_multi
        concat_multi = ""
    End If

Here is a screen shot

Upvote 0

Forum statistics

Latest member

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