• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I determine whether an array has been ReDimmed yet?

I'm writing a method that will populate an array with data before pouring it into a worksheet (which is much faster than writing it in directly). But this time I want to preserve the array from one invocation to the next, which means the method must determine the status of the array. If it were an ordinary scalar variable I could use IsEmpty, but that doesn't work on an array. In fact, so far I haven't been able to figure out anything that works on an array that has been named in a Dim statement ("Dim ar()") but not yet hit the ReDim statement. Does anyone know how this can be done? What I want is something like this:
If /*array has not yet been initialized */ Then
  rZ = MaxRows
  ReDim ar(1 to rZ, 1 to cZ)
  /* populate the array */
  End If
But so far I don't see how to see whether the array has been initialized yet.


Well-Known Member
Something like this? ReDim straight away to unlikely dimensions then test for (one of) those unlikely dimensions
Dim ar()
ReDim ar(-1 To 0)
If UBound(ar) = 0 Then
  rZ = 6
  cZ = 3
  ReDim ar(1 To rZ, 1 To cZ)
  ' /* populate the array */
End If
Last edited:
Oh, sure. A little artificial, but it would work.

I was going to give up and just set a static flag meaning "arrays have been ReDimmed" or "arrays have not". This'd work too. What I was actually hoping was that someone could tell me to use a special function, or Null, or something. But as I said, none of the things I thought to try worked.

Marc L

Excel Ninja
As a Variant can be an array or not :​
    Dim V
    If Not IsArray(V) Then
        ReDim V(1 To 5, 1 To 3)
    End If