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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/201005/1

To check if a variant variable is a range, you can use the VBA function TypeName. Here's an example:

In this example, we create a variant variable called myVar and set it equal to a range of cells. We then use the TypeName function to check if myVar is a range. If it is, we display a message box saying so. If it's not, we display a message box saying that it's not a range.

1. Create a variant variable called myVar.
2. Set myVar equal to a range of cells using the Range function.
3. Use the TypeName function to check if myVar is a range.
4. Display a message box indicating whether or not myVar is a range.

Note that the TypeName function will return the data type of any variable, not just ranges. So if you want to check for other data types, you can modify the code accordingly.

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" wrote:

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?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/201005/1

--

Dave Peterson

Hi Dave,

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

M.

Dave Peterson wrote:
Maybe you could use:

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

To see if that gives you want you need.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/201005/1