Public Range Constant - how do I?

  • Thread starter Thread starter slarbie
  • Start date Start date
S

slarbie

In all the modules/procedures of my project, I'd like to be able to refer to
a specific named range as a constant. I imagined this:

Public const MyRange as Range = Range("MyRange")

at the top of a module, but it doesn't work. Can someone help with the
right way to do what I'm trying to get at here? Thanks!
 
If the Public range constant won't work, why not use a Public constant String
to hold the Range Address ??
 
Do you mean one of the below?:

1) Public const MyRange as String = "MyRange"
2) Public const MyRange as String = "Range("MyRange")"

1) means I still have to refer to Range(MyRange) throughout the project. I
was hoping to be able to just refer to it as MyRange without having to Set it
in each procedure.
2) isn't acceptable syntax

Or did you mean something else? Does anyone have a solution to not having
to use the Set statement in each procedure?
 
One way:

In a regular module:
Public MyRange As Range

' In ThisWorkbook module
Private Sub Workbook_Open()
Set MyRange = ThisWorkbook.Names("MyRange").RefersToRange
End Sub

Although MyRange may change since the workbook is opened, so I would modify
it a bit to be able to refresh it on-call:

' In a regular module
Public MyRange As Range

Sub RefreshGlobalRanges()
Set MyRange = ThisWorkbook.Names("Test").RefersToRange
' Other ranges as needed
End Sub

' In ThisWorkbook module
Private Sub Workbook_Open()
Call RefreshGlobalRanges
End Sub
 
From the Remarks section of the help files for the Const Statement...

"You can't use variables, user-defined functions, or
intrinsic Visual Basic functions (such as Chr) in
expressions assigned to constants."
 
Hi Slarbie

Instead of putting 'Public const MyRange as Range = Range("MyRange")', put
the following in the top of general module:

Public My Range As Range 'Don't put the word 'Const'

Than in the Open workbook event, set the range object as follows:

Private Sub Workbook_Open()
Set MyRange = ThisWorkbook.Worksheets("Sheet1").Range("MyRange")
End Sub

Next time you execute Workbook_Open() This will allow you to use 'MyRange'
anywhere in the project

Hope this helps
 
That's it! I had considered the Workbook_open event but didn't make the leap
to combining the public variable declaration with it. Thanks so much!
 
The range is quite static, so the first one works beautifully. I had also
considered the Workbook_open event, but not combining it with public
declaration of the range variable. Thanks so much!
 
Keep in mind that if in any time after setting the range object, the VB goes
into Break mode, the range object will become disassociated from "MyRange",
that means it will become an empty range variable = Nothing and you'll have
to reset it to "MyRange"
 
Thanks again, I was just setting about to figure out why it had stopped
working. Hmmm. I'll have to figure out how I want to deal with that... Is
it preventable if I have sufficiently robust error handling in the
procedures, so break mode doesn't happen?
 
I think the best way to deal with it, is by using Tim Zych method (One of
your respondents) - Set the range in self contained Sub Precedure, like this
you can call it from the Workbook_Open precedure and from anywhere in the
code you want, or just execute it anytime VB goes into break mode.
I know this isn't perfect, its the best I can think of and I use this method
myself when developing.
 
If you use my 2nd solution, you can test for the validity of the range
object, and if Nothing, run RefreshGlobalRanges. Or, just run it at the
beginning of each procedure/function.

Sub RunMacro()
If MyRange Is Nothing Then
' Something bad happened, so refresh everything
Call RefreshGlobalRanges
End if

End Sub

or

Sub RunMacro()
' Refresh everything as a defensive maneuver to:
' - Fix broken references
' - Resize modified ranges
Call RefreshGlobalRanges

End Sub
 
Well, I must be learning from you both, because this is the direction my
thoughts had already gone. Again, many thanks for your time and effort in
helping - I really do appreciate it.
 
nice tip thanks

Tim Zych said:
One way:

In a regular module:
Public MyRange As Range

' In ThisWorkbook module
Private Sub Workbook_Open()
Set MyRange = ThisWorkbook.Names("MyRange").RefersToRange
End Sub

Although MyRange may change since the workbook is opened, so I would modify
it a bit to be able to refresh it on-call:

' In a regular module
Public MyRange As Range

Sub RefreshGlobalRanges()
Set MyRange = ThisWorkbook.Names("Test").RefersToRange
' Other ranges as needed
End Sub

' In ThisWorkbook module
Private Sub Workbook_Open()
Call RefreshGlobalRanges
End Sub


--
Tim Zych
http://www.higherdata.com
Workbook Compare - Excel data comparison utility
Free and Pro versions
Click to expand...
 
Back
Top