Wednesday, 4 February 2009

Detecting an array variant in Excel VBA

This will return 0 for scalar variables and a positive integer for arrays:

InStr(TypeName(varTest), "()")

The return values cast to Boolean TRUE or FALSE and can be used directly in an IF... THEN clause.

Kludgy, but effective. Note that this detects empty arrays: their typename isn't "empty", it is Empty()

The correct approach is to use the native VBA function VarType(), which reads the numeric type constant. An integer is VarType 2 (vbInteger) and the vbArray constant (8192) is used in a bitwise operation to give 8192 for an array of integers: [vbInteger OR vbArray]

The 'correct' approach fails when you try to wrap this logic in a function, for reasons stated in the comments...

Public Function VariantIsArray(varTest As Variant) As Boolean

' Return TRUE if varTest is array

' FALSE for User-defined types

' Note that all ranges of two or more cells are cast to an array variant

' even when you omit the '.Value' property. All object types that expose

' a default property will pass it into our function's parameter, instead

' of a reference to the object itself. There can be no reliable and self

' -contained IsArray() function in VBA, because you have to run a prior

' test using IsObject() in the calling function.

' What, you read the documentation and believed that IsArray() works? Try

' it on an array of objects, a single-member array, and an empty variant.

VariantIsArray = VarType(varTest) And vbArray

End Function