ExcelBanter - View Single Post - Public Range Constant

Alberto Ast[_2_]

nice tip thanks

"Tim Zych" wrote:

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

"slarbie" wrote in message
...
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!