You can resize dynamic arrays in VBA using the redim statement and its variations. But though it seems simple enough, it’s got its tricks you need to master.
Resize, but don’t delete, an array in VBA
Every time you redim, your array is cleared, and you lose the information contained in it.
That is, unless you use redim preserve. In that case VBA will keep the values in the array and resize it. For example, this changes your array size to 100 elements, without losing the data that’s already there:
redim preserve my_array(100)And some common pitfalls with redim preserve:
- First, you can only resize dynamic arrays: those that are declared in your sub without a specific size. If you specified a size (e.g. dim my_array(15) as variant) you can’t resize. Solution to this is simple: declare all your arrays as dynamic, and then immediately redim them to set the initial size you want.
- Second, if you use redim preserve, you can only resize the last dimension. For example in 2D arrays, you can only add columns. Try to add rows, and you’ll get an error. The solution to this one, I’ll explain in a later section.
Related to dimensions, there’s another common pitfall, and this one had me stuck for a good 2 hours figuring it out. Turns out you can’t change the start index (lower bound) during a redim preserve. Let’s say I have an array that’s 15×15 and it is numbered starting at 0. And I’m going to add more columns.
This is not valid:
Redim preserve my_array(0 to 15, 1 to 20)I’m telling VBA to redim and at the same time, change the columns count to start at 1. That change from 0 to 1 on the columns lower bound will throw a subscript out of range error. If you look at it, it might look normal: you’re not changing the number of rows, you’re changing columns, and it’s valid to change columns. The error is hidden in the columns parameter, when you specify “1 to”… not real easy to spot if you’re not paying attention.
Specifically what got me that time, was something like this:
Redim preserve my_array(15, 20)When I ran that, I got a subscript out of range error. I ran it 10 times, no way to get rid of the error. I checked spelling, checked syntax, everything was fine… and still, I got the error every time. I was keeping the first dimension constant, and increasing only the second. Still, subscript out of range.
It wasn’t until several hours later that I figured it out.
I had created my array from an Excel range, using the method I described in another post. But, when you do that, your array doesn’t count its elements from 0: Excel automatically starts counting at 1, no matter what your Option Base is set to.
So, in that case, my array was created internally by Excel through an equivalent to this:
Dim my_array(1 to 25, 1 to 15)In fact, checking the Watch window on the VBA debugger, it was perfectly clear that the count was starting at 1.
Now, here comes the roundhouse kick you never expect: Option Base was set to 0 (default) in my VBA module. So when I said redim preserve and didn’t specify a lower bound, I was actually doing this:
Redim preserve my_array(0 to 25, 0 to 20)And that is not allowed. Not only was I changing the lower bound on the second dimension, I was also inadvertently trying to modify the first dimension.
I changed the code to this, and it worked perfectly:
Redim preserve my_array(1 to 25, 1 to 20)One thing you could do, if you wanted to avoid this error altogether, is use Lbound and Ubound in your redim statements.
Redim preserve my_array(LBound(my_array,1) to UBound(my_array,1), LBound(my_array,2) to 20)That makes your code a bit harder to read and implement. But it should keep you safe from errors, especially if you’re combining arrays you create with arrays Excel creates.
Changing the number of rows in a 2D array using transpose
Here’s another neat trick for 2D arrays. Let’s say you have a 2D array: rows and columns. It’s got data you don’t want to lose, and on top of that, you want to add rows. That’s going to be a problem: if you remember, when you use redim preserve you can only resize the last dimension, which would be columns. You can’t modify rows.
That is, unless you swap rows and columns (called “transposing”). When you transpose a 2D array, rows become columns and columns become rows, so effectively your rows become the last dimension in the array, and you can resize them.
The big problem? VBA doesn’t have a universal transpose function. Excel VBA does, but it’s an internal worksheet function, so it appears in Excel VBA and nowhere else. So if you’re working VBA in Project, Word, Powerpoint or whatever, you’re going to have to go a little more complicated with your solution. More on that later.
Let’s start with the simple case: we’re doing VBA in Excel. Transposing is as simple as this:
my_array = worksheetfunction.transpose(my_array)After that, use redim preserve and add your rows.
Don’t forget to run another transpose after you redim, to bring everything back to its original location:
my_array = worksheetfunction.transpose(my_array)
redim preserve my_array(25,40)
my_array = worksheetfunction.transpose(my_array)In that last example, I transpose, then change the number of rows (which are now in dimension #2) to 40, and then transpose back to the original shape. And I end up with a 40×25 array.
Of course, this is only valid for 2D arrays. If you have 3D or 4D arrays, you can’t transpose them, at least not that easy.
Ok, now for the more complicated case. Typical situation: let’s say we’re writing code in MS Project VBA (or somewhere else), and we need to resize a 2D array, and change the number of rows. So we need to transpose.
In Project we don’t have a transpose function… but we do have a COM (common object model), that lets us call Excel functions.
So, we could do something like this:
my_array = Excel.WorksheetFunction.Transpose(my_array)That tells MS Project VBA to call transpose via Excel.
But, for this to work, in our VBA editor we need to go into Tools – References and check “Microsoft Excel 16.0 object library”. This will tell Project (or whatever we’re using) to enable and recognize commands for Excel VBA in the editor.
On top of that, we’re going to have to enable the reference every time we create a new file. Even if the import our previous module into that file, the reference won’t be enabled by default.
Plus, there’s a hidden danger here too: when we call transpose, Excel is going to change our array bounds. As an example, I created an array in MS Project that’s 15×20 and set a watch on it. Initially the lower bound is set according to Option Base, at 0:
Now watch what happens when I run it through the Excel transpose function:
And when I switch it back through another transpose:
My array is the same size, but the lower and upper bounds have changed. Excel changed the array count to start at 1 instead of 0. And that could be a serious problem, which could lead to values being left out of calculations.
So be careful when using this method: always check your bounds using Ubound and Lbound, don’t just trust that they’ll be set at their defaults.
Leave a Reply