Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
What is the correct way to use names defined globally in a workbook, in VBA scripts? From Excel, I can access any name defined in any worksheet (sheet1) from anywhere in the workbook. But this is not seem to be the case for VBA. For example: - I have a range A1:A10 with name PayHr in sheet1. - When I crate a sub Initialize() in sheet2: Private Sub Initialize() Set payRange = Me.Range("HrPay") 'payRange is defined globally on the top section. Debug.Print myRange.Cells(1, 1) End Sub This produced a compiler error: Method or data member not found. However, the code works when I define HrPay in sheet2. I tried to add worksheets("sheet1") in the Set line, but not sure if I used the correct syntax, all trails returned errors so far. Thanks, pac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
"packat" wrote in message news:ESABd.30813$h.15240@trnddc04... - I have a range A1:A10 with name PayHr in sheet1. - When I crate a sub Initialize() in sheet2: Private Sub Initialize() Set payRange = Me.Range("HrPay") 'payRange is defined globally on the top section. Debug.Print myRange.Cells(1, 1) End Sub This produced a compiler error: Method or data member not found. However, the code works when I define HrPay in sheet2. Because you Set payRange and then reference myRange? Take a look at http://www.xldynamic.com/source/xld.Names.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
Bob Phillips wrote: "packat" wrote in message news:ESABd.30813$h.15240@trnddc04... - I have a range A1:A10 with name PayHr in sheet1. - When I crate a sub Initialize() in sheet2: Private Sub Initialize() Set payRange = Me.Range("HrPay") 'payRange is defined globally on the top section. Debug.Print myRange.Cells(1, 1) End Sub This produced a compiler error: Method or data member not found. However, the code works when I define HrPay in sheet2. Because you Set payRange and then reference myRange? :-) It was a typo. The code did refer to correct variable name. Take a look at http://www.xldynamic.com/source/xld.Names.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
So see my answer.
-- Regards, Tom Ogilvy "packat" wrote in message news:ppBBd.22020$rL3.19909@trnddc03... Bob Phillips wrote: "packat" wrote in message news:ESABd.30813$h.15240@trnddc04... - I have a range A1:A10 with name PayHr in sheet1. - When I crate a sub Initialize() in sheet2: Private Sub Initialize() Set payRange = Me.Range("HrPay") 'payRange is defined globally on the top section. Debug.Print myRange.Cells(1, 1) End Sub This produced a compiler error: Method or data member not found. However, the code works when I define HrPay in sheet2. Because you Set payRange and then reference myRange? :-) It was a typo. The code did refer to correct variable name. Take a look at http://www.xldynamic.com/source/xld.Names.html |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
If it is a workbook level name (and it sounds like it is) then
one way would be Set payRange = thisWorkbook.Names("HrPay").RefersToRange -- Regards, Tom Ogivy "packat" wrote in message news:ESABd.30813$h.15240@trnddc04... What is the correct way to use names defined globally in a workbook, in VBA scripts? From Excel, I can access any name defined in any worksheet (sheet1) from anywhere in the workbook. But this is not seem to be the case for VBA. For example: - I have a range A1:A10 with name PayHr in sheet1. - When I crate a sub Initialize() in sheet2: Private Sub Initialize() Set payRange = Me.Range("HrPay") 'payRange is defined globally on the top section. Debug.Print myRange.Cells(1, 1) End Sub This produced a compiler error: Method or data member not found. However, the code works when I define HrPay in sheet2. I tried to add worksheets("sheet1") in the Set line, but not sure if I used the correct syntax, all trails returned errors so far. Thanks, pac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
Yes! It works. Thanks!
pac Tom Ogilvy wrote: If it is a workbook level name (and it sounds like it is) then one way would be Set payRange = thisWorkbook.Names("HrPay").RefersToRange "packat" wrote in message news:ESABd.30813$h.15240@trnddc04... What is the correct way to use names defined globally in a workbook, in VBA scripts? From Excel, I can access any name defined in any worksheet (sheet1) from anywhere in the workbook. But this is not seem to be the case for VBA. For example: - I have a range A1:A10 with name PayHr in sheet1. - When I crate a sub Initialize() in sheet2: Private Sub Initialize() Set payRange = Me.Range("HrPay") 'payRange is defined globally on the top section. Debug.Print myRange.Cells(1, 1) End Sub This produced a compiler error: Method or data member not found. However, the code works when I define HrPay in sheet2. I tried to add worksheets("sheet1") in the Set line, but not sure if I used the correct syntax, all trails returned errors so far. Thanks, pac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
Thanks to both Bob and Tom. The link Bob provided is very
useful. packat wrote: What is the correct way to use names defined globally in a workbook, in VBA scripts? From Excel, I can access any name defined in any worksheet (sheet1) from anywhere in the workbook. But this is not seem to be the case for VBA. For example: - I have a range A1:A10 with name PayHr in sheet1. - When I crate a sub Initialize() in sheet2: Private Sub Initialize() Set payRange = Me.Range("HrPay") 'payRange is defined globally on the top section. Debug.Print myRange.Cells(1, 1) End Sub This produced a compiler error: Method or data member not found. However, the code works when I define HrPay in sheet2. I tried to add worksheets("sheet1") in the Set line, but not sure if I used the correct syntax, all trails returned errors so far. Thanks, pac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
"packat" wrote in news:WzBBd.22022$rL3.5596@trnddc03:
Thanks to both Bob and Tom. The link Bob provided is very useful. Glad you found it useful. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
Dorset County Public Information Point?
-- Regards, Tom Ogilvy "DorsetPips" wrote in message ... "packat" wrote in news:WzBBd.22022$rL3.5596@trnddc03: Thanks to both Bob and Tom. The link Bob provided is very useful. Glad you found it useful. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Correct way to use names defined globally in a workbook, in VBA
I wish ;-)
No, it is just the moniker I use on the XNews newsreader, which I used when posting that. Dorset is my county, as you guessed, Pips is just a shortening for Phillips, which is what my wife called me before she adopted the name :-). Bob "Tom Ogilvy" wrote in message ... Dorset County Public Information Point? -- Regards, Tom Ogilvy "DorsetPips" wrote in message ... "packat" wrote in news:WzBBd.22022$rL3.5596@trnddc03: Thanks to both Bob and Tom. The link Bob provided is very useful. Glad you found it useful. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Globally available defined names? | Excel Worksheet Functions | |||
Defined Names in Workbook | Excel Worksheet Functions | |||
Tranferring names defined in one excel workbook to another workboo | Excel Discussion (Misc queries) | |||
How to delete all defined names from a workbook? | Excel Worksheet Functions | |||
How to delete all defined names from a workbook? | Links and Linking in Excel |