Excel VBA：在多维数组中查找值

Excel VBA: Find value in a multi-dimensional array

提问by Nuno Nogueira

myArray is storing data I need to check before I call a Sub procedure. It has 3 columns:

myArray 正在存储我在调用 Sub 过程之前需要检查的数据。它有 3 列：

• Col1: string
• Col2: timestamp
• Col3: string

• Col1：字符串
• Col2：时间戳
• Col3：字符串

I need to check:

我需要检查：

1. If a string, given by the funcion argument, exists in column 1

2. If it does, then what is the time-difference from now to the newest timestamp of the corresponding string in column 2

   For n = 1 To UBound(myArray, 2)
       If myArray(1, n) = myString Then
           myTimeStamp = myArray(2, n) 'find the timestamp of this string in col2
           myTimeDiff = DateDiff("n", myTimeStamp, DateTime.Now()) ' calculate the time difference in minutes
           myIndex = n 'return the array index (n)
       End If
   Next n
   

   Because the array is filling the timestamps sequentially, I know the latest for a given string in col1 is also the newest.

1. 如果 funcion 参数给出的字符串存在于第 1 列中

2. 如果是，那么从现在到第 2 列中相应字符串的最新时间戳的时差是多少

   For n = 1 To UBound(myArray, 2)
       If myArray(1, n) = myString Then
           myTimeStamp = myArray(2, n) 'find the timestamp of this string in col2
           myTimeDiff = DateDiff("n", myTimeStamp, DateTime.Now()) ' calculate the time difference in minutes
           myIndex = n 'return the array index (n)
       End If
   Next n
   

   因为数组按顺序填充时间戳，我知道 col1 中给定字符串的最新也是最新的。

But myIndex is returning an empty string, why?

但是 myIndex 返回一个空字符串，为什么？

回答by CommonGuy

Arrays in VBA usually start with LBound = 0, so your for-loop should be:

VBA 中的数组通常以 LBound = 0 开头，因此您的 for 循环应该是：

For n = 0 to UBound(myArray)