Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
If the Public range constant won't work, why not use a Public constant String
to hold the Range Address ?? -- Gary''s Student - gsnu200839 "slarbie" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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? "Gary''s Student" wrote: If the Public range constant won't work, why not use a Public constant String to hold the Range Address ?? -- Gary''s Student - gsnu200839 "slarbie" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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." -- Rick (MVP - Excel) "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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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 -- A. Ch. Eirinberg "slarbie" wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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! "Howard31" wrote: 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 -- A. Ch. Eirinberg "slarbie" wrote: 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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! "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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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" -- A. Ch. Eirinberg "slarbie" wrote: 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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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? "Howard31" wrote: 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" -- A. Ch. Eirinberg "slarbie" wrote: 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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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. -- A. Ch. Eirinberg "slarbie" wrote: 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? "Howard31" wrote: 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" -- A. Ch. Eirinberg "slarbie" wrote: 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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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 -- Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility Free and Pro versions "slarbie" wrote in message ... 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? "Howard31" wrote: 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" -- A. Ch. Eirinberg "slarbie" wrote: 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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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. "Tim Zych" wrote: 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 -- Tim Zych http://www.higherdata.com Workbook Compare - Excel data comparison utility Free and Pro versions "slarbie" wrote in message ... 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? "Howard31" wrote: 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" -- A. Ch. Eirinberg "slarbie" wrote: 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! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Public Range Constant - how do I?
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you update a public constant | Excel Programming | |||
range * constant | Excel Programming | |||
Public constant in many workbooks | Excel Programming | |||
Public Function IsStrikeThrough(rng As Range) | Excel Programming |