Looping through a multidimensional array...

[ease20022002]

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:

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.

 

[erik.van.geit]

Hi,

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,
Erik

 

[pgc01]

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
PGC

 

[ease20022002]

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]

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!

 

[erik.van.geit]

did you know there is a forum about Access ?

 

[ease20022002]

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

Thanks again!

 

[kylefoley76]

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

 

[kylefoley76]

Problem solved.

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

is the proper code. Also changed to

sent(i)(j)