NB. {Edit}
I subsequently found strange things happening in my project when I used this approach. Bizarre errors in code that had been working for years. When I removed this line of code it went back to working as before. I leave this here for information, but you have been warned.
Sometimes we need to determine if a declared array has been
ReDim
med yet. If we try to reference it beforehand then it will crash. Not a good situation :-(Here's an excerpt from the help file that may help to explain why this might occur :
ReDim Help:I suspect they may have left out the
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).
Static
statement, but that's another matter - not our issue here.I will introduce and explain a way that allows the code to determine for itself, without triggering and capturing an error, whether or not the array variable has yet been initialised. For my purposes in this article I will refer to the act of
ReDim
ming as initialisation. So, a dynamic array variable has no storage space allocated until it is initialised, or ReDim
med, but after its first ReDim
it becomes initialised and does have storage space allocated.ReDim Help:See the explanation below for how we'll take advantage of this to determine the initialised status of the dynamic array variable.
Used at procedure level to reallocate storage space for dynamic array variables.
Explanation
A dynamic array variable has a pointer value of zero (0) until it is initialised, at which point the pointer value becomes non-zero. For our purposes we don't care about the value except as far as whether or not it's zero.
Generally, when an array variable is referenced without any qualifications, such as an element identifier specified for a dimension, the code will fail as directly referencing an array pointer is not allowed in VBA. That is to say, unless as a parameter to something like
LBound()
or UBound()
. However, it is perfectly possible to reference it after the Not
operator. We will use this to determine if it's been set yet or not.If the pointer value is zero then
Not
of the pointer value is -1 - or all 1 bits. This isn't as easy to test as all zero bits (Zero.) so we use Not Not
instead to return the original value of the pointer. We can compare this to zero, or we can even use CBool()
, to determine if the dynamic array variable is unset yet.NB.
What we cannot do is simply to test it as is.
Not Not Not {dynamic array variable}
is equivalent to Not {dynamic array variable}
, but both of these will take the True path whether the original value of the pointer is zero or not. So, comparison with zero or the use of CBool()
is indicated.Code Illustration
Expand|Select|Wrap|Line Numbers
- Public Sub ReDimTest()
- Dim astrVar() As String
- If (Not Not astrVar) = 0 Then Debug.Print "A Uninitialised"; (Not Not astrVar);
- If Not Not astrVar Then Debug.Print " B Initialised"; (Not Not astrVar);
- ReDim astrVar(0 To 1)
- If (Not Not astrVar) = 0 Then Debug.Print " C Uninitialised"; (Not Not astrVar);
- If Not Not astrVar Then Debug.Print " D Initialised"; (Not Not astrVar);
- astrVar = Split("")
- If (Not Not astrVar) = 0 Then Debug.Print " E Uninitialised"; (Not Not astrVar);
- If Not Not astrVar Then Debug.Print " F Initialised"; (Not Not astrVar);
- Debug.Print " G ";UBound(astrVar);
- Debug.Print " H";astrVar(0);
- End Sub
Expand|Select|Wrap|Line Numbers
- Results:
- A Uninitialised 0 D Initialised 204031032 F Initialised 204031032 G -1 {Crashes here}
In the above code you'll notice that
UBound(astrVar)
returns a value of -1, which is below the lower bound of zero. Test this if you need to ensure that your code doesn't fall foul of any such situation.Conclusion
I hope you find this useful :-)