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). 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
Err.Clear
N = LBound(Arr)
If Err.Number = 0 Then
' array is allocated
Else
' array is not allocated
End If
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.
Dim Arr As Variant
Dim S As String
S = vbNullString
Arr = Split(S,";")
Debug.Print LBound(Arr), UBound(Arr)
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 handle 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 = Not (IsError(LBound(Arr))) And _
IsArray(Arr) And _
(LBound(Arr) <= UBound(Arr))
End Function
This page last updated: 2-August-2007