CODEWITHSUNDEEP
vba
Kapol
Kapol

Reputation: 6463

Find position in a two-dimensional array

I have a two-dimensional array:

(1, 1) = X  (1, 2) = [Empty]  (1, 3) = [Empty]
(2, 1) = Y  (2, 2) = [Empty]  (2, 3) = [Empty]
(3, 1) = Z  (3, 2) = [Empty]  (3, 3) = [Empty]

I want to store data in 2nd and 3rd column, where the row number is determined by matching values in the first column against some specific value provided. Is there a way to find the row number of the array where Z is present, without having to loop through the whole column? I'm looking for an equivalent of using WorksheetFunction.Match on a one-dimensional array.

To solve my problem, I can create two arrays, where the first one will have one dimension and will store values to look in, and the second one will store the rest of columns. I'd rather have just one, though.

Upvotes: 0

Views: 9238

Answers (3)

Guest
Guest

Reputation: 21

'To determine if a multi-dimension array is allocated (or empty)
'Works for any-dimension arrays, even one-dimension arrays
Public Function isArrayAllocated(ByVal aArray As Variant) As Boolean

On Error Resume Next
isArrayAllocated = IsArray(aArray) And Not IsError(LBound(aArray, 1)) And LBound(aArray, 1) <= UBound(aArray, 1)
Err.Clear: On Error GoTo 0

End Function

'To determine the number of dimensions of an array
'Returns -1 if there is an error
Public Function nbrDimensions(ByVal aArray As Variant) As Long
Dim x As Long, tmpVal As Long

If Not IsArray(aArray) Then
    nbrDimensions = -1
    Exit Function
End If

On Error GoTo finalDimension
For x = 1 To 65536 'Maximum number of dimensions (size limit) for an array that will work with worksheets under Excel VBA
    tmpVal = LBound(aArray, x)
Next x

finalDimension:
nbrDimensions = x - 1
Err.Clear: On Error GoTo 0

End Function

'*****************************************************************************************************************************
'To return an array containing al the coordinates from a specified two-dimension array that have the searched item as value
'Returns an empty array if there is an error or no data
'Returns coordinates in the form of x,y
'*****************************************************************************************************************************
Public Function makeArrayFoundXYIn2DimArray(ByVal itemSearched As Variant, ByVal aArray As Variant) As Variant
Dim tmpArr As Variant, x As Long, y As Long, z As Long

tmpArr = Array()
If IsArray(aArray) Then
    If isArrayAllocated(aArray) And nbrDimensions(aArray) = 2 Then
        z = 0
        For x = LBound(aArray, 1) To UBound(aArray, 1)
            For y = LBound(aArray, 2) To UBound(aArray, 2)
                If itemSearched = aArray(x, y) Then
                    If z = 0 Then
                        ReDim tmpArr(0 To 0)
                    Else
                        ReDim Preserve tmpArr(0 To UBound(tmpArr) + 1)
                    End If
                    tmpArr(z) = CStr(x) + "," + CStr(y)
                    z = z + 1
                End If
            Next y
        Next x
    End If
End If
makeArrayFoundXYIn2DimArray = tmpArr
Erase tmpArr

End Function
shareeditflag

Upvotes: 0

Guest
Guest

Reputation: 21

Try this function 

Public Function posInArray(ByVal itemSearched As Variant,ByVal aArray As Variant) As Long  

Dim pos As Long, item As Variant  

posInArray = 0  
If IsArray(aArray) Then  
If Not isEmpty(aArray) Then  
    pos = 1  
    For Each item In aArray  
        If itemSearched = item Then  
            posInArray = pos  
            Exit Function  
        End If
        pos = pos + 1  
    Next item  
    posInArray = 0
End If  
End If  

End Function

Upvotes: 2

SWa
SWa

Reputation: 4363

You can use Index() for working with areas in arrays which then allows you to use match. However, I've always found Excel functions to be extremely slow when used on VBA arrays, especially on larger ones.

I'd hazard a guess and and say that actually looping through would be your best bet here. Alternatively, depending on your use case use a different storage mechanism, something with a Key/Value lookup like a collection or Scripting.Dictionary would probably give you the best performance

EDIT

For the record, I again state that I wouldn't do it like this, it's slow on large arrays, but you can do:

Sub test()
    Dim arr(1 To 3, 1 To 3)

    arr(1, 1) = "X"
    arr(2, 1) = "Y"
    arr(3, 1) = "Z"

    With Application
        MsgBox .Match("Z", .Index(arr, 0, 1), 0)
    End With

End Sub

Upvotes: 1

Related Questions