current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
1  
It works! I had no idea ReDim Preserve could only be used for the 2nd dimension, thanks! –  Nuno Nogueira Nov 24 '13 at 1:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.