Excel VBA: two dimensional array with variable and time stamp

Question

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

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.

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?

Answer 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,

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.