VBA Variant - IsNull and IsEmpty both false for empty array

Question

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

You could either declare the array without dimensions, or explicitly check each element. — Ron Rosenfeld
– Ron Rosenfeld, Commented May 10, 2016 at 15:02
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
– keynesiancross, Commented May 10, 2016 at 15:08
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
– Vincent G, Commented May 10, 2016 at 15:14
I declare it as a Variant(). But I don't give a size to the array etc — keynesiancross
– keynesiancross, Commented May 10, 2016 at 15:21
@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
– Ron Rosenfeld, Commented May 10, 2016 at 16:38

SierraOscar · Accepted Answer · 2016-05-10 15:36:53Z

4

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

answered May 10, 2016 at 15:36

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

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

Comments

basodre · Accepted Answer · 2016-05-10 15:32:02Z

2

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

answered May 10, 2016 at 15:32

basodre

5,7901 gold badge18 silver badges25 bronze badges

5 Comments

keynesiancross Over a year ago

Oh - found an error... I'm getting an invalid procedure call or argument error when I run the test on a non-empty array

basodre Over a year ago

@keynesiancross Just reviewed it. It doesn't work if you pass in multi dimensional arrays, which I just read is what you're doing. Let me see if I can ammend

keynesiancross Over a year ago

Cool - thanks eh. I'm messing with it now, and thinking about going the lazy route of adding an error handler.

basodre Over a year ago

It seems like error handling is the best way to go, as much as I prefer not to do it. We just don't have that much support for arrays in VBA.

SierraOscar Over a year ago

@keynesiancross you can try double transposing the array to make it single dimension and then use this function (assuming it's a 2-D array?)

dgorti · Accepted Answer · 2016-05-10 15:35:16Z

-1

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.

answered May 10, 2016 at 15:35

dgorti

1,2802 gold badges15 silver badges23 bronze badges

2 Comments

keynesiancross Over a year ago

This doesn't work, because both my non-empty and empty variant arrays return vartype = 8204

dgorti Over a year ago

Not sure why it is down voted but yes that's exactly how it should work. The type of the variant is array. Period. You should check the array type to be vbArray. Once you have the array you need to check if it is really empty or not.

Collectives™ on Stack Overflow

VBA Variant - IsNull and IsEmpty both false for empty array

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related