Handle an array to avoid errors

Question

In userform1, I have the following code

Private Sub cmdOK_Click()
    Dim i As Long
    With Me.ListBox2
        If .ListCount = 0 Then MsgBox "You Have To Select At Least One Column", vbExclamation: GoTo Skipper
        ReDim aCols(0 To .ListCount - 1)
        For i = 0 To .ListCount - 1
            aCols(i) = "[" & ListBox2.List(i, 0) & "]"
        Next i
    End With
Skipper:
    Unload Me
End Sub

and in standard module I declared aCols as public

Public aCols

if listbox2 has no items then aCols became Empty while if there are items the aCols became an array .. Then in another code I am confused of how to avoid errors

If UBound(aCols) > -1 Then

This works fine if aCols is not empty but I encountered errors if aCols is Empty .. How to deal with both cases Simply I need to avoid the errors and deal with aCols either it is empty or either it is an array.

[`IsArray`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/isarray-function) might be helpful. — BigBen, Sep 08 '20 at 18:25
`IsArrary()` and `IsEmpty()` can both be used to address this issue. — Excel Hero, Sep 08 '20 at 18:27
Take a look [here](https://stackoverflow.com/q/59568342/2165759) — omegastripes, Sep 08 '20 at 18:33
Just insert `aCols = Array()` before redimming, then you can check the `UBound(aCols) > -1`condition @YasserKhalil — T.M., Sep 08 '20 at 18:44

score 1 · Accepted Answer · answered Sep 08 '20 at 18:45

I would use Function safeUBound() which looks ugly due to OERN but works fine:

Function safeUBound(a)
    
    safeUBound = -1
    On Error Resume Next
    safeUBound = UBound(a)
    
End Function

Another solution is to assign empty array or empty 2d array to the variable aCols either at the very beginning of the code or at userform initialize.

Handle an array to avoid errors

1 Answers1