Dim variable as range syntax

[parry]

Hi, I am having trouble understanding ranges in VBA.

I have the following code where I am selecting cells a1:a3 first by using a string to define the range then a range type. The string works fine but the range type comes up with runtime error 1004:'Method Range of Object'_Global Fail. Clicking on Help shows no topic grrrrr.

Could someone please explain how you are supposed to declare then use range variables. Im stuck and searching VB help showed nothing useful.

Sub test()
Dim rngtest As Range
Dim strtest As String

Set rngtest = Range("a1", "a3")
strtest = "a1:a3"

Range(strtest).Select ' works
Range(rngtest).Select ' runtime error 1004

End Sub

 

[XL-Dennis]

parry,

Dim rntest as Range gives us access to rntest as a range-object.

When we refer to this range-object we don´t need to refer to it as a range, i e Range(rntest).<pre>
Sub test()
Dim rngtest As Range
Dim strtest As String

Set rngtest = Range("a1:a3")
strtest = "a1:a3"

Range(strtest).Select
rngtest.Select
End Sub</pre>

Hopefully I has explained it in a understandable way.

HTH,
Dennis

_________________
"Windows was not able to find any keyboard. Press F1-button to try again or F2-button for cancel."
This message was edited by XL-Dennis on 2002-09-01 04:54

 

[parry]

Thank you very much Dennis. Just to make sure I understand ...

With the range defined as an object (does the "set" do that or the fact I used "as range"?) I could go rngtest.clear or rngtest.select etc. Cool.

So the error came up because my code was really saying Range(Range("a1:a3")).select

Please confirm my understanding

 

[XL-Dennis]

Parry,

With the range defined as an object (does the "set" do that or the fact I used "as range"?) I could go rngtest.clear or rngtest.select etc. Cool.

"As range" and You can use "Set" only with object type variables.

So the error came up because my code was really saying Range(Range("a1:a3")).select

Correct

Kind regards,
Dennis
This message was edited by XL-Dennis on 2002-09-01 06:07

 

[parry]

Superb! Thanks very much Dennis, you have removed my frustrations with ranges so I can now start growing back my hair again

Do you happen to know a site you could recommend that explains a bit more about what you can Dim. I used to think you could only "Dim" as a data type (single, boolean etc) until I saw people using "as range".

 

[XL-Dennis]

parry,

I´m not aware of websites that explicit deals with the Dim-statement.

Have You explored the directhelp?
(Highlight "Dim" and push the F1-button)

If already done, the only good suggestion I can give is to search in the archieves for this board.

 

[Richie(UK)]

Hi parry,

See if you find any of these useful:

http://www.beyondtechnology.com/vba008.shtml

http://www.cpearson.com/excel/variables.htm

http://envar.com/samp01.htm

HTH

 

[parry]

Cool thanks Richie.