返回数组Excel VBA中元素的索引 [英] Return Index of an Element in an Array Excel VBA

问题描述

我有一个数组 prLst，它是一个整数列表.整数未排序，因为它们在数组中的位置代表电子表格上的特定列.我想知道如何在数组中找到一个特定的整数，并返回它的索引.

I have an array prLst that is a list of integers. The integers are not sorted, because their position in the array represents a particular column on a spreadsheet. I want to know how I find a particular integer in the array, and return its index.

似乎没有任何资源可以告诉我如何不将数组转换为工作表上的范围.这似乎有点复杂.这对 VBA 来说是不可能的吗?

There does not seem to be any resource on showing me how without turning the array into a range on the worksheet. This seems a bit complicated. Is this just not possible with VBA?

推荐答案

Dim pos, arr, val

arr=Array(1,2,4,5)
val = 4

pos=Application.Match(val, arr, False)

if not iserror(pos) then
   Msgbox val & " is at position " & pos
else
   Msgbox val & " not found!"
end if

更新为显示使用 Match(带有 .Index)在二维数组的维度中查找值:

Updated to show using Match (with .Index) to find a value in a dimension of a two-dimensional array:

Dim arr(1 To 10, 1 To 2)
Dim x

For x = 1 To 10
    arr(x, 1) = x
    arr(x, 2) = 11 - x
Next x

Debug.Print Application.Match(3, Application.Index(arr, 0, 1), 0)
Debug.Print Application.Match(3, Application.Index(arr, 0, 2), 0)

<小时>

这里值得说明@ARich 在评论中指出的内容 - 如果您在循环中执行它，使用 Index() 对数组进行切片具有可怕的性能.

---

it's worth illustrating here what @ARich pointed out in the comments - that using Index() to slice an array has horrible performance if you're doing it in a loop.

在测试中(下面的代码)，Index() 方法比使用嵌套循环慢了近 2000 倍.

In testing (code below) the Index() approach is almost 2000-fold slower than using a nested loop.

Sub PerfTest()

    Const VAL_TO_FIND As String = "R1800:C8"
    Dim a(1 To 2000, 1 To 10)
    Dim r As Long, c As Long, t

    For r = 1 To 2000
        For c = 1 To 10
            a(r, c) = "R" & r & ":C" & c
        Next c
    Next r

    t = Timer
    Debug.Print FindLoop(a, VAL_TO_FIND), Timer - t
    ' >> 0.00781 sec

     t = Timer
    Debug.Print FindIndex(a, VAL_TO_FIND), Timer - t
    ' >> 14.18 sec

End Sub

Function FindLoop(arr, val) As Boolean
    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        If arr(r, c) = val Then
            FindLoop = True
            Exit Function
        End If
    Next c
    Next r
End Function

Function FindIndex(arr, val)
    Dim r As Long
    For r = 1 To UBound(arr, 1)
        If Not IsError(Application.Match(val, Application.Index(arr, r, 0), 0)) Then
            FindIndex = True
            Exit Function
        End If
    Next r
End Function

这篇关于返回数组Excel VBA中元素的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！