VBA Variant - IsNull and IsEmpty both false for empty array

1
votes

I have a VBA class that contains a number of variants. These variants are there to hold Y x Z size arrays, but they're optional inputs (and so often the variants will never get used or initialized).

A function within the same class calls on each of these variants to perform a calculation. BUT, I need it to skip the variants that are blank. When I try to use IsNull(xyz) and IsEmpty(xyz), both return false. Looking at the watch on xyz, it has:

Expression: xyz
Value:
Type: Variant/Variant()
Context: className

Totally blank under value.

Any ideas how I can test/return a boolean if these things are empty? Or even a boolean if they're full, that would work too.

Thanks

Edit: I should add, that IsMissing just completely crashes excel...

3
vbaexcel
You could either declare the array without dimensions, or explicitly check each element. - Ron Rosenfeld
I never declare the variable. Are you suggesting that I should declare it as an Variant()? [the multi-dimension array contains multiple data types] - keynesiancross
I never declare the variable. I personally don't think it's good practice, it could save some time when writing but code might be much more difficult to debug. - Vincent G
I declare it as a Variant(). But I don't give a size to the array etc - keynesiancross
@keynesiancross In that case, you could test of an 'out of range' error on LBOUND(variable) Or, perhaps, combine ISARRAY and LBOUND looking for True and Error 9 - Ron Rosenfeld

3 Answers

3
votes

Very dirty workaround but something like this might do the trick:

Function IsEmptyArray(testArr As Variant) As Boolean

Dim test As Long
Dim ret As Boolean

ret = False

    On Error Resume Next
    test = UBound(testArr)

    If Err.Number = 9 Then
        ret = True
    End If

    Err.Clear
    On Error GoTo 0

    IsEmptyArray = ret

End Function
2
votes

One method I've used in the past is to test whether or not the array is filled using a helper function. I join the array using an empty string delimiter and test that the length is greater than zero. It has worked in my circumstances, but I'm not sure if there are any flaws in the logic.

Below code returns true if the array is empty and false if it is not:

Function TestIfArrayIsEmpty(vArray As Variant) As Boolean
    TestIfArrayIsEmpty = (Len(Join(vArray, "")) = 0)
End Function
-1
votes

You can use vartype(variable_name. You can check vartype(varriable_name) = vbArray or VbEmpty or VbNull Then check LBound and UBound of eac dimension for example

LBound(variable, 1) and UBound(variable_name, 1) to check the lower and higher indexes of the array.