Check if dynamic array is initialized or contains value - VBA Excel

Question

Is there a way to tell if some dynamic array, for example arr() as String is initialized? IsEmpty and IsNothing seems not to work with dynamic arrays. Currently, I declare separate boolean variable and set to false, when array is initialized I change value to true. Is there a built-in function?

To check whether an array is initialized, you can check LBound or UBound. To check whether its empty, [here is a similar question](https://stackoverflow.com/q/26290781/9808063) and [this](https://stackoverflow.com/q/206324/9808063) and [this](https://stackoverflow.com/q/38784430/9808063) — Naresh, May 17 '20 at 13:03

freeflow · Answer 1 · 2022-05-02T09:36:35.603

You can test if an array has been populated by testing for an error when you read a value from the array.

    Option Explicit
    
    Public myArray() As String
    
    Public Sub ttest()
    


    
        If ArrayIsEmpty(myArray) Then
            Debug.Print "Its empty"
        Else
            Debug.Print "Oh no there's something there"
            
        End If
    
    End Sub
    
    Public Function ArrayIsEmpty(ByVal ipArray As Variant) As Boolean
    
        On Error Resume Next
        Dim myDummy As Variant
        myDummy = ipArray(0)
        ArrayIsEmpty = Not Err.Number = 0
        On Error GoTo 0
        
    End Function

Edited 2 May 2022

Thanks for whoever upvoted this, however I'm now aware that the canonical method to do this test is described by C.Pearson

http://www.cpearson.com/excel/isarrayallocated.aspx

as

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)

Check if dynamic array is initialized or contains value - VBA Excel

1 Answers1