Excel VBA - How to Redim a 2D array?
This isn't exactly intuitive, but you cannot Redim(VB6 Ref) an array if you dimmed it with dimensions. Exact quote from linked page is:
The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts).
In other words, instead of dim invoices(10,0)
You should use
Dim invoices()
Redim invoices(10,0)
Then when you ReDim, you'll need to use Redim Preserve (10,row)
Warning: When Redimensioning multi-dimensional arrays, if you want to preserve your values, you can only increase the last dimension. I.E. Redim Preserve (11,row)
or even (11,0)
would fail.
I stumbled across this question while hitting this road block myself. I ended up writing a piece of code real quick to handle this ReDim Preserve
on a new sized array (first or last dimension). Maybe it will help others who face the same issue.
So for the usage, lets say you have your array originally set as MyArray(3,5)
, and you want to make the dimensions (first too!) larger, lets just say to MyArray(10,20)
. You would be used to doing something like this right?
ReDim Preserve MyArray(10,20) '<-- Returns Error
But unfortunately that returns an error because you tried to change the size of the first dimension. So with my function, you would just do something like this instead:
MyArray = ReDimPreserve(MyArray,10,20)
Now the array is larger, and the data is preserved. Your ReDim Preserve
for a Multi-Dimension array is complete. :)
And last but not least, the miraculous function: ReDimPreserve()
'redim preserve both dimensions for a multidimension array *ONLY
Public Function ReDimPreserve(aArrayToPreserve,nNewFirstUBound,nNewLastUBound)
ReDimPreserve = False
'check if its in array first
If IsArray(aArrayToPreserve) Then
'create new array
ReDim aPreservedArray(nNewFirstUBound,nNewLastUBound)
'get old lBound/uBound
nOldFirstUBound = uBound(aArrayToPreserve,1)
nOldLastUBound = uBound(aArrayToPreserve,2)
'loop through first
For nFirst = lBound(aArrayToPreserve,1) to nNewFirstUBound
For nLast = lBound(aArrayToPreserve,2) to nNewLastUBound
'if its in range, then append to new array the same way
If nOldFirstUBound >= nFirst And nOldLastUBound >= nLast Then
aPreservedArray(nFirst,nLast) = aArrayToPreserve(nFirst,nLast)
End If
Next
Next
'return the array redimmed
If IsArray(aPreservedArray) Then ReDimPreserve = aPreservedArray
End If
End Function
I wrote this in like 20 minutes, so there's no guarantees. But if you would like to use or extend it, feel free. I would've thought that someone would've had some code like this up here already, well apparently not. So here ya go fellow gearheads.