do something based on the type of variable

excel, vba

Solution

To test if a variable is empty, use the IsEmpty function.

If IsEmpty(b) Then
    Debug.Print "b is Empty"
End If

To test if a variable is an array, use the VarType function.

If VarType(b) And vbArray Then
    Debug.Print "b is an array"
End If

Problem

I have a function in VBA that usually returns an array. If there is nothing to return it returns an empty variable (`b = empty`). I have some code to loop though the array but I get an error if the variable is not an array. How can I make an if statement that doesn't cause its own error. I have tried ``` if not b = empty then 'do the loop end if ``` but this gives an error when b is an array Similarly I get error for `b = null`, `b = nothing`, `b(1,1) = ""` etc. What is the best way to check this?

Original source