vba 在二维数组中查找位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22478229/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find position in a two-dimensional array
提问by Kapol
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 Zis present, without having to loop through the whole column? I'm looking for an equivalent of using WorksheetFunction.Matchon a one-dimensional array.
我想将数据存储在第 2 列和第 3 列中，其中行号是通过将第一列中的值与提供的某些特定值进行匹配来确定的。有没有办法找到Z存在的数组的行号，而不必遍历整列？我正在寻找等效于使用WorksheetFunction.Match一维数组的方法。
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.
为了解决我的问题，我可以创建两个数组，其中第一个将有一个维度并存储要查找的值，第二个将存储其余的列。不过，我宁愿只有一个。
回答by SWa
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.
您可以Index()用于处理数组中的区域，然后允许您使用匹配。但是，我一直发现 Excel 函数在 VBA 数组上使用时非常慢，尤其是在较大的数组上。
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
我会冒险猜测并说实际循环将是您最好的选择。或者，根据您的用例使用不同的存储机制，像集合或 Scripting.Dictionary 这样的键/值查找可能会给你最好的性能
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
回答by Guest
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
回答by Guest
'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
