Excel VBA：具有变量和时间戳的二维数组

I want my Sub procedure below to push the argument and the time stamp to a two-dimensional array every time it is executed.

我希望下面的Sub过程在每次执行时将参数和时间戳推送到二维数组。

Public Dim myArray() As Variant

Public Sub mySub(argument)
n = n + 1
   //other code here
ReDim Preserve myArray(1 To n, 1 To 2)
myArray(n, 1) = argument
myArray(n, 2) = DateTime.Now()

End Sub

So, basically I want to get an array with 2 columns and n rows, being column 1 the argument used in the procedure and column 2 the time stamp.

所以,基本上我想得到一个包含2列和n行的数组,第1列是过程中使用的参数,第2列是时间戳。

This procedure is being called from a Function but the function is returning #VALUE! and the array is empty. What is wrong with this code?

从Function调用此过程,但函数返回#VALUE!并且数组是空的。这段代码有什么问题?

1 个解决方案

#1

You've run into a quirk of VBA arrays. You can only augment a 2-D array with ReDim Preserve in the second dimension. That is,

你遇到了VBA数组的怪癖。您只能在第二维中使用ReDim Preserve扩充二维数组。那是,

n = n + 1
ReDim Preserve myArray(1 To 2, 1 to n)

will work, but

会工作,但是

n = n + 1
ReDim Preserve myArray(1 To n, 1 To 2)

will result in an error.

会导致错误。

You'll just have to work with your dimensions flipped if you want to use arrays in this way.

如果你想以这种方式使用数组,你只需要处理你的尺寸。

#1