Prev: Hide error messages?
Next: Need suggestions on layout to report/pivot
From: Michiel via OfficeKB.com on 7 May 2010 16:08
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.aspx/ms-excel/201005/1

From: Dave Peterson on 7 May 2010 17:49
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.aspx/ms-excel/201005/1

--

Dave Peterson
From: Michiel via OfficeKB.com on 10 May 2010 09:12
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.aspx/ms-excel/201005/1

 | 
Pages: 1
Prev: Hide error messages?
Next: Need suggestions on layout to report/pivot