CODEWITHSUNDEEP
vbaexcel
keynesiancross
keynesiancross

Reputation: 3529

VBA Variant - IsNull and IsEmpty both false for empty array

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...

Upvotes: 1

Views: 1896

Answers (3)

SierraOscar
SierraOscar

Reputation: 17637

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

Upvotes: 4

dgorti
dgorti

Reputation: 1240

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.

Upvotes: -1

basodre
basodre

Reputation: 5770

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

Upvotes: 2

Related Questions