Variable type

[Alan Beban]

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]

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.


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

 

[Alan Beban]

Thanks, Chip.

Alan Beban