Check if variable is range

[Michiel via OfficeKB.com]

This is probably very simple but I just don't see it.

How can I see if a (variant) variable is a range or something else (e.g. a
string)

Sub WhatIsIt()
Dim vrString As Variant
Dim vrRange1 As Variant
Dim vrRange2 As Variant


vrString = "This is a string"
Set vrRange1 = Range(Cells(1, 1), Cells(1, 2)) 'Note TWO cells included
Set vrRange2 = Range(Cells(1, 1), Cells(1, 1)) 'Note ONE cell included

'vrString.copy will not work since vrString is a string
'vrRange1.copy will work since vrRange1 is a range
'vrRange2.copy will work since vrRange1 is a range

'If isarray(vrString) then vrString.copy will work since vrString is not an
array
'If isarray(vrRange1) then vrRange1.copy will work since vrRange1 is an
array
'If isarray(vrRange2) then vrRange2.copy will work since vrRange2 is NOT an
array

End Sub

Any ideas?

 

[Dave Peterson]

Maybe you could use:

MsgBox TypeName(vrString)
MsgBox TypeName(vrRange1)
MsgBox TypeName(vrRange2)

To see if that gives you want you need.

 

[Michiel via OfficeKB.com]

Hi Dave,

I was right, it is indeed very simple.
Thank you very much. This will do!

M.