Declare a range as a constant rather than a variable?

[bluto32]

I have a feeling I am going to kick myself once this is pointed out to me...

I have written some lengthy code which does what I want, but I would like to "polish" it up, and get into good habits with using objects properly.

In the code below, I am happy that "a" is a variable which has been set to 10 for now, but can be changed later. "b" is a constant, and cannot be changed from its initial value of 25.

Dim a As Integer
a = 10
 
Const b as Integer = 25

But... why does it not work with ranges?

Dim c As Range
Set c = Range("A1:B4")
 
Const d As [COLOR=red]Range[/COLOR] = Range("A1:B4")

I get the error "Expected: type name", and it's the red bit which seems to be causing the problem.

Bluto

 

[Norie]

Not sure how using a constant instead of a variable here would be good practice.

Anyway you can't declare a Range as a constant because it isn't constant.

 

[RoryA]

Constants can only be data types, not Objects.

 

[bluto32]

Thank you - makes more sense now.

Bluto

 

[Kenneth Hobson]

You could make it a Public variable.

Dim abRange as Range

Sub InitialSetup
  Set abRange = Worksheets(1).Range("A1:B4")
End Sub

In another routine such as an Open Event or another sub:

Private Sub Workbook_Open()
  InitialSetup
End Sub

 

[bluto32]

Thank you.

I'm just going to stick with the standard "Dim" and "Set" lines, given that objects are not allowed to be declared as constants.

Bluto