Multiplie (4 dimension) array & match function - VBA/Excel

[VBA-Noob]

Hi,

Does someone now how to script an 4 dimension array in Excel VBA? Is this impossible?

I also wonder if there is an match function that can check positions to in arrays? Like,

Dim impossibleArray ()
impossibleArray(Match funktion here)

Need to be an VBA script, not an excel formula.

Cheers!

 

[BiocideJ]

I can answer the first part of you question, but the second part is unclear what you want.

The first line of this defines the 4 dimensional array where each dimension contains 10 elements.
The rest of the code is just an example of loading data into the subsequent array (the last array element ImpArr(10,10,10,10) = 10000

Sub test()
Dim ImpArr(1 To 10, 1 To 10, 1 To 10, 1 To 10)
Dim x As Long
x = 1
For dim1 = 1 To 10
            
    For dim2 = 1 To 10
            
        For dim3 = 1 To 10
            
            For dim4 = 1 To 10
                ImpArr(dim1, dim2, dim3, dim4) = x
                x = x + 1
            Next
        Next
    Next
Next
End Sub

As far as the match question, can you explain exactly what you are looking for? Are you wanting to lookup an individual value to see what position it exists in the array?

 

[VBA-Noob]

I can answer the first part of you question, but the second part is unclear what you want.

The first line of this defines the 4 dimensional array where each dimension contains 10 elements.
The rest of the code is just an example of loading data into the subsequent array (the last array element ImpArr(10,10,10,10) = 10000

Sub test()
Dim ImpArr(1 To 10, 1 To 10, 1 To 10, 1 To 10)
Dim x As Long
x = 1
For dim1 = 1 To 10
            
    For dim2 = 1 To 10
            
        For dim3 = 1 To 10
            
            For dim4 = 1 To 10
                ImpArr(dim1, dim2, dim3, dim4) = x
                x = x + 1
            Next
        Next
    Next
Next
End Sub

As far as the match question, can you explain exactly what you are looking for? Are you wanting to lookup an individual value to see what position it exists in the array?

Thank you so much! I know that there is an match-function that can be used in Arrays, I want it to match an special value with an animal. The value and the animal is in those multiplie arrays.

 

[VBA-Noob]

I can answer the first part of you question, but the second part is unclear what you want.

The first line of this defines the 4 dimensional array where each dimension contains 10 elements.
The rest of the code is just an example of loading data into the subsequent array (the last array element ImpArr(10,10,10,10) = 10000

Sub test()
Dim ImpArr(1 To 10, 1 To 10, 1 To 10, 1 To 10)
Dim x As Long
x = 1
For dim1 = 1 To 10
            
    For dim2 = 1 To 10
            
        For dim3 = 1 To 10
            
            For dim4 = 1 To 10
                ImpArr(dim1, dim2, dim3, dim4) = x
                x = x + 1
            Next
        Next
    Next
Next
End Sub

As far as the match question, can you explain exactly what you are looking for? Are you wanting to lookup an individual value to see what position it exists in the array?

How can I make an for each loop in these arrays?

 

[BiocideJ]

You can't use For ... Each loops, but you can use the array Upper/Lower bounds to initiate loops as I do below.
This is conceptually what I see as being possible for a Match function. I'm still not completely convinced that I fully understand what you are trying to do, but as I understand it, this should work.
NOTE: I don't load any values into AnimalArr so the function won't exactly work as is.

Sub test()
Dim AnimalArr(1 To 10, 1 To 10, 1 To 10, 1 To 10) As Variant
Dim IndexArr(1 To 10, 1 To 10, 1 To 10, 1 To 10) As Variant
Dim x As Long
x = 1
For dim1 = 1 To 10
            
    For dim2 = 1 To 10
            
        For dim3 = 1 To 10
            
            For dim4 = 1 To 10
                IndexArr(dim1, dim2, dim3, dim4) = x
                x = x + 1
            Next
        Next
    Next
Next
CustomMatch AnimalArr, "Tiger", IndexArr
End Sub

Private Function CustomMatch(arr As Variant, lookup_val As Variant, matching_arr As Variant)
    
    'arr is assumed to be 4-dimensional
    For I = LBound(arr(1)) To UBound(arr(1))
        For J = LBound(arr(2)) To UBound(arr(2))
            For K = LBound(arr(2)) To UBound(arr(2))
                For L = LBound(arr(2)) To UBound(arr(2))
                    If arr(I, J, K, L) = lookup_val Then
                        CustomMatch = matching_arr(I, J, K, L)
                        Exit Function
                    End If
                Next L
            Next K
        Next J
    Next I
End Function