Array Allocation Test
You've got an array; you want to know whether it's already been allocated. Most code examples you see suggest a clunky procedure and an error trap. This is neater and easier.
I was first made aware of the possibility of this method a couple of years ago and I have investigated it and come to the conclusion that it is sound. I have, however, been unable to find any documentation of any sort to back up — or refute — my findings. so, if the behaviour changes in a future release, I will not know whether it is a bug fixed, or a bug introduced; all I can say, for sure, is that this technique works at the moment.
** Update ** The behaviour did change! I discovered that this mechanism didn't work in the Word 2010 beta, and raised it as a bug. Although no comment was made, I assume from the fact that it was fixed, and does still work, that it is legitimate, although still not documented anywhere.
Without further ado, here's some code demonstrating the technique.
Sub TestArrayAllocation() Dim TestArray1(1 To 3) ' Static (thus allocated) Dim TestArray2() ' Dynamic unallocated Dim TestArray3() ' Dynamic .. ReDim TestArray3(1 To 5) ' .. now allocated
If (Not TestArray1) = True Then MsgBox "TestArray1 is not allocated" Else MsgBox "TestArray1 is allocated" End If If (Not TestArray2) = True Then MsgBox "TestArray2 is not allocated" Else MsgBox "TestArray2 is allocated" End If If (Not TestArray3) = True Then MsgBox "TestArray3 is not allocated" Else MsgBox "TestArray3 is allocated" End If End Sub
That’s it! - If (Not array_name) = True Then ' The array is NOT allocated - What could be simpler? Alright, there’s no need to answer that! How does it work? Why does it work? Read on.
Variables fall, loosely, into one of two camps: those that hold a value, and those that hold a pointer to memory somewhere else. The ones that hold actual data are simple, fixed-length, types such as Long numbers. The ones that hold pointers are the more complex, possibly variable-length, types: objects, strings, and, in particular, arrays.
Before I go any further, a note to all pedants: except in rare cases with small numbers, computers use addresses (or pointers) rather than actual values and there is a level of indirection beyond that which I describe in the following explanation; it is of no consequence here.
When you reference a variable in a VBA statement, the VBA compiler decides what to do, and what information to pass to the instruction in the statement, partly based on the data type. For a Long number, VBA may well use the value being held; for an object variable it will check to see if there is an actual object assigned to the variable and, if so, often invoke the default property of the object and then decide what to do based on its type.
When the variable is an array, sometimes the compiler won’t allow the use at all and sometimes it will use the pointer held (actually a pointer to a structure describing the array, a structure called a SAFEARRAY, but that, too, is incidental to the issue at hand). If the array is unallocated, the pointer stored in the array variable is not set to anything and it has a value of zero.
Now consider the following code:
Dim TestVar As Long MsgBox TestVar
Run it and you will see a message box showing the value 0, zero being the current (default) value of the variable. Now consider this code:
Dim TestVar(1) As Long MsgBox TestVar
Run it and, this time, you will get a runtime error, a Type Mismatch; the message box routine can’t do anything with the pointer to the array that VBA has given it; it’s of the wrong type, hence the mismatch.
Before trying another piece of code, I want to introduce you to the Not operator, and for this you’ll need to know, and probably already do know, that everything in a computer’s memory is held as a series of bits which can be read as numbers, and which are then interpreted according to the context. A bit (short for binary digit) has a value of zero or one, and the Not operator swaps all the bits to the value they’re not: zero becomes one, and one becomes zero. Some more code:
Dim TestVar As Long MsgBox Not TestVar
Run this and you’ll see the value in the message box is now -1, not 0. You don’t actually need to know how this works, but the Long variable contains 32 bits, all zero, interpreted as the decimal number 0. (Not the Long variable) contains 32 bits, now all one, and this is interpreted as the decimal number -1. Guess what the next piece of code is going to be and, a much harder task, guess what the result might be.
Dim TestVar(1) As Long MsgBox Not TestVar
You won’t always get the same number, but I just ran it and it displayed -1961905. (What this actually means is that, in this instance, the array, or, at least, the SAFEARRAY block describing the array, is at address Not -1961905, or 1961904.) Whatever number you see is the result of the Not operation acting on the pointer that, you’ll remember, previously gave an error. VBA passes the pointer to the Not operator, which is happy to accept it, and then return a number, another Long value, which can be successfully passed to the message box.
Now, when a dynamic array has not been allocated, there is no pointer value because there is nothing to point to, and the pointer contains a default value of zero. Where relevant, the zero value will trigger an exception, but the Not operator simply sees it as a number, the number zero, in just the same way as it sees the number zero, the default value of the Long variable. The code, as above, but for an unallocated array, looks like this:
Dim TestVar() As Long MsgBox Not TestVar
Run it and you will get the answer -1. An unallocated array will always give the answer -1; an allocated one will always give a different number.
There’s one more thing you need to know. Many values in variables of a particular type can be coerced into values of a different type in appropriate circumstances. Boolean values (True or False) can be coerced into numbers – True to -1, False to zero, in VBA. One of the circumstances that this happens is in numeric comparisons; VBA recognises that numeric and Boolean comparands are different, but it also recognises that it can handle the situation by coercing the Boolean to a number.
Put it all together, with a pair of parentheses to impose operator precedence, to make sure that the Not operator is evaluated before the equals operator, and you get:
If (Not TestVar) = True Then ...
The condition evaluates to True if, and only if, (Not TestVar) has a value of -1, or True, which, as you have now seen, only happens when the TestVar array is unallocated.
Do be aware that there are various permutations on the basic idea that can be used, and the choice to use True as a comparand is mine alone, as I think it helps towards making the statement more understandable. Feel free to adapt the idea as you see fit.