Check length of Variant object

Question

I want to find out if there are any elements in a list after applying filter to it. The list is of type variant which seems to be a problem. Most suggestions are in line with this post: but it does not work for me. What am i doing wrong?

Dim message As Variant
Dim tempMessage As Variant
Dim a As Long
message = Split("AACP;CP;sBcp;ccffcp", ";")
tempMessage = Filter(message, "CP", False, vbTextCompare)
If IsNull(tempMessage) = True Then
    Debug.Print "EMPTY"
Else
    Debug.Print tempMessage(0)
End If

Bathsheba · Answer 1 · 2017-10-29T06:40:17.230

3

You have a couple of ways:

VarType(tempMessage) will be vbEmpty if it is completely blank.
VarType(tempMessage) And vbArray will be non-zero if it's an array type (even with zero elements. Zero length arrays are allowed in VBA). Here I'm using And in the logical context.

If you've established it's an array, then you can take a guess at the dimensionality. (VBA provides no function for this, but luckily you should know from the documentation of Filter).

If you've established the variant is an array then use UBound(tempMessage) - LBound(tempMessage) + 1 to get the number of elements.

edited Oct 29 '17 at 06:40

answered Dec 10 '14 at 08:31

Bathsheba

231,907
34
361
483

VarType(tempMessage) returns 8200 and UBound(tempMessage) - UBound(tempMessage) + 1 returns 1 always, even if there are no items... – user3748315 Dec 10 '14 at 08:48
8200 is vbArray + vbString so is therefore an array type. Do you know what I mean when I say *logical* `And`? – Bathsheba Dec 10 '14 at 08:51
No I dont :( I this check seems to work: If UBound(tempMessage) > -1 Then – user3748315 Dec 10 '14 at 08:56
1

I think you mean: UBound(tempMessage) - LBound(tempMessage) + 1 – Haider Oct 28 '17 at 21:14
@hayder Oops yes I did. Thanks. Btw always feel feee to male such edits yourself. – Bathsheba Oct 29 '17 at 06:40

Check length of Variant object

1 Answers1