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

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

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

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

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

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

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

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.

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.

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.