Redim an Array

Array variables can be static or dynamic. That’s determined by the Dim statement. If you specify dimensions when you declare the variable, it’s static and always will be. If you leave the dimensions blank in the Dim statement, it’s dynamic and can be changed.

Dynamic arrays can be changed using the Redim statement.

If you use Redim, all the data in your array is lost, unless you use the Preserve keyword. This keeps the data in tact, but limits what you can change with a Redim. For instance, when you use Preserve, you can only change the last dimension of the array. Sometimes you have to organize your array horizontally to accomodate this restriction.

Preserve is an expensive keyword, so you use it sparingly. Many people will Redim their arrays in blocks to avoid having to do it in every iteration of a loop.

I’m not a big fan of the block Redim, but if you have a really time intensive procedure and this shaves some valuable milliseconds, then go for it. If there’s a way to figure out the upper bounds of the array before you add data, then you may save time there also.

12 thoughts on “Redim an Array

  1. Other ways of doing this:
    1. Use a collection. Easy to add elements, but it’s very slow. Still this is the best method if you are planning to switch to a language that has a vector type like C++.
    2. Computer Science classes teach that the rule of thumb is you should double the array’s size each time you find you are out of space.

  2. Some important points from the VBA help:

    If you use the Preserve keyword, you can resize only the last array dimension and you can’t change the number of dimensions at all.

    Similarly, when you use Preserve, you can change the size of the array only by changing the upper bound.

    If you pass an array to a procedure by reference, you can’t redimension the array within the procedure.



  3. > If you pass an array to a procedure by reference, you can’t redimension the array within the procedure.

    I’ve seen that in the help before, but I’ve never seen where it causes a problem.

    I’ve always been able to do this:

    Dim arrTest() As Long

    ReDim arrTest(1)
    MsgBox “UBound(arrTest) = ” & UBound(arrTest)
    Procedure1 arrTest
    MsgBox “UBound(arrTest) = ” & UBound(arrTest)

    Sub Procedure1(ByRef arr() As Long)
    ReDim arr(10)
    MsgBox “UBound(arrTest) = ” & UBound(arr), , “Procedure1?
    End Sub

    Could someone please explain when this wouldn’t work? Or why the help file says that, even though it is apparently not true?

  4. I regularly use sub procedures to resize arrays. In fact, if I couldn’t do that, I’d say 90% of my VBA code would fail to work.

    If you are using arrays to create lists of data so you may paste the entire list into an Excel spreadsheet using a single paste operation, then it’s generally a matter of transposing the array, using ReDim Preserve, adding your extra information, and then transposing it back to the way it was.

  5. I have just come across a curious problem with re-dimming.

    I have the following code:

    Option explicit
    Function Elastic( …
    ‘ Dim statements with no reference to RLayers()

    ReDim RLayers(1 To NumReo + 1, 1 To 18)

    End Function

    On my old computer this works without complaint.

    On my new computer with the same Excel (2007) and the same OS (Vista) I get the compile error:

    “Can’t find project or library”

    I can make the problem go away by inserting a Dim statement (Dim RLayers() as double), which obviously I should have done in the first place, but I’m curious as to why I’m getting this different behaviour.

    I can’t see any differences in the VBA references list in the two computers.

    Any other ideas what might cause the difference in behaviour?

  6. Doug –

    I always thought one had to Dim a variable before it could be ReDimmed, but a quick test in 2003 showed that I was wrong (which happens so rarely!).

    I have heard of a number of unexpected VBA changes in 2007, which are pretty much the same as this: minor but annoying.

  7. Jon – it doesn’t seem to be an XL 2007 thing though. On my old computer the Redim works without a Dim statement in both 2000 and 2007, but on the new computer it doesn’t work in 2007. I haven’t installed any older versions yet.

    I thought it might have something to do with the Perfmon utility, which is installed on the old computer and not the new one, but disabling this and removing the reference to it seems to make no difference.

  8. Dick — I was curious why you’re not a fan of block ReDim?

    I was considering such an approach as I am potentially dealing with a fair amount of data and want to know what people thought of this. Is it the extra work(code) or is it something else?

    Great thread.

  9. Bill: Here’s my coding philosophy

    1. Code so that it works
    2. Code so that it’s pretty
    3. Code so that it’s fast

    At #3, I put a timer on the code and work on the biggest time sinks until it’s fast enough. Expensive though it may be, I’ve *never* had to change to block redims to improve performance. It’s not that I’m not a fan (although that’s exactly what I said), I’m just not a fan of coding for speed before measuring. If I ever work with a large enough data set, individual redims may make the cut, although it’s as likely I will find a faster way to compute the upper bound outside of the loop.

  10. Dick Kusleika wrote:
    – begin quote –
    Bill: Here’s my coding philosophy

    1. Code so that it works
    2. Code so that it’s pretty
    3. Code so that it’s fast
    — end quote —

    Glad to see “it’s fast” is last. {grin}

    I find it both sad and hilarious that there are so many who want to make their solution “fast.” The result often is that that they get the wrong result very, very fast. {vbg}

    If by “pretty” you mean readable and maintainable, you will find that if you make it #1, the “it works” will follow naturally. So,

    1. Invest the time to find / create a good algorithm
    2. Implement it in a readable and maintainable manner
    3. If the performance is not acceptable
    … 3.1 See if tweaking the existing implementation does the job w/o sacrificing too much of #2.
    … 3.2 If not, try and find a better algorithm
    … 3.3 If none can be found, stick with 3.1

  11. I learned something again! Now I know why my redim statement wasn’t working … Only the last dimension can be changed. All this time I must’ve been lucky that it just worked out that way. Today I tried to do something different and realized I can’t.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.