Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

Variable type

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Alan Beban - 17 Sep 2007 00:35 GMT
It is claimed that, after

Set rng = Range("C1:C3")

with no variable declaration, rng is a Variant containing a Range type
variable of the same name, rather than that rng is simply a Range type
variable.

1. How can this claim be tested?

2. Under what circumstances would it ever make a difference?

Simply using Typename to resolve the difference is not definitive,
because part of the claim is that Typename(rng) returns the type of the
Range type variable within the Variant rather than the type of the Variant.

Alan Beban
Chip Pearson - 17 Sep 2007 02:12 GMT
Alan,

The only way I can think of to test whether Rng is declared explicitly as a
Range rather than as a Varaint is to attempt to set Rng to another type of
object. If this does not cause an error, then Rng is a Variant. If it fails,
Rng is declared as a Range. For example,

Sub AAA()
   Dim R1 ' Variant
   Dim R2 As Range

   Set R1 = Range("A1")
   Set R2 = Range("A1")

   On Error Resume Next
   Err.Clear
   Set R1 = Worksheets(1)
   If Err.Number <> 0 Then
       Debug.Print "R1 Is Not A Variant"
   Else
       Debug.Print "R1 Is A Variant"
   End If

   Err.Clear
   Set R2 = Worksheets(1)
   If Err.Number <> 0 Then
       Debug.Print "R2 Is Not A Variant"
   Else
       Debug.Print "R2 Is A Variant"
   End If
End Sub

In this code, the result is that R1 is recognized as a Variant because
setting it to a Worksheet does not raise an error. R2 is recognized as Range
because you get an error 13 Type Mismatch when attempting to set it to a
Worksheet.

In the real world, though, I'm not sure that the distinction is meaningful.

Signature

Cordially,
Chip Pearson
Microsoft MVP  - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> It is claimed that, after
>
[quoted text clipped - 13 lines]
>
> Alan Beban
Alan Beban - 17 Sep 2007 18:13 GMT
Thanks, Chip.

Alan Beban

> Alan,
>
> The only way I can think of to test whether Rng is declared explicitly
> as a Range rather than as a Variant is to attempt to set Rng to another
> type of object. If this does not cause an error, then Rng is a Variant.
> If it fails, Rng is declared as a Range. . . .
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.