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.

IsArrary() and IsEmpty() can both be used to address this issue. — Excel Hero
– Excel Hero, Commented Sep 8, 2020 at 18:27
Just insert aCols = Array() before redimming, then you can check the UBound(aCols) > -1condition @YasserKhalil — T.M.
– T.M., Commented Sep 8, 2020 at 18:44

omegastripes · Accepted Answer · 2020-09-08 18:45:01Z

1

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.

answered Sep 8, 2020 at 18:45

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Handle an array to avoid errors

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related