Is An Array Allocated?
This page describes a VBA procedure you can use to determine whether a dynamic array or an array in a Variant has been allocated.
There are two types of arrays: static arrays, in which the dimensions of the array are set in
the Dim statement, and dynamic arrays, in which the array is allocated and dimensioned with the
ReDim statement. An array is said to be allocated if it consumes memory, has valid lower and
upper bounds, and contains data (even if that data is the default values for the data type of the array, such as empty strings
of an array of String type variables). A static array is by
definition always allocated. You can never resize, reallocate, deallocate, or release the memory consumed by a static array.
With a dynamic array, however, you can resizse, reallocate, deallocate, and release the memory of the array. (A Varaint type variable that
contains an array always contains a dynamic array.)
At times, you may need to test whether a dynamic array has been allocated or whether it has never been allocated or has been deallocated
with the Erase statement. In nearly all cases, you can test whether a dynamic array has been allocated by
simply attempting to read the LBound of the array. If an error occurs, then the array is not allocated. If no error occurs, the array has
been allocated. For example,
Dim Arr() As Long
Dim N As Long
On Error Resume Next
N = LBound(Arr)
If Err.Number = 0 Then
Debug.Print "Array is allocated."
Debug.Print "Array is not allocated."
However, certain funcitons will set the LBound and UBound of an array even when the array is not allocated. For example, the
Split function behaves this way. The test described above would treat this as an allocated array
because retrieving the LBound of the array doesn't cause an error. This is wrong, though
array isn't properly allocated. For example,
Dim S As String
Dim V As Variant
S = vbNullString
V = Split(S,",")
Debug.Print IsArray(V), LBound(V), UBound(V)
This illustrates a condition under which both the LBound and UBound contain values
and retrieving them does not throw an error, but the array is not properly allocated.
With a normal array, attempting LBound(Arr) would cause a run time error. However, with Split and
a handful of other functions, Lbound(Arr) and UBound(Arr) do not cause errors when the array is
unallocated. This is a side-effect of the basic fact that, in VB and VBA, arrays are really structures of a type called SafeArray,
which manages bounds and contains pointers to the actual data. You don't work with SafeArrays directly in VB/VBA. It is all handled behind
the scenes. In the case of an array handled by the Split function, you need to test whether
LBound is greater than UBound.
The function below, IsArrayAllocated will accurately return True or
False indicating whether the array is allocated. This function will work for both static and dynamic
arrays of any number of dimensions, and will correctly work for unallocated arrays with valid (non-error-causing)
LBound values, such as those arrays set by the Split function.
Function IsArrayAllocated(Arr As Variant) As Boolean
On Error Resume Next
IsArrayAllocated = IsArray(Arr) And _
Not IsError(LBound(Arr, 1)) And _
LBound(Arr, 1) <= UBound(Arr, 1)
This function will return True if Arr is a valid and allocted array. Under any other
circumstance, it will return False. It works for array of any data type and with any number of dimensions.
||This page last updated: 18-April-2009.