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