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

"packat" <[email protected]> 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

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" <[email protected]> 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
>
>
>

Bob Phillips wrote:
> "packat" <[email protected]> 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

So see my answer.

--
Regards,
Tom Ogilvy


"packat" <[email protected]> wrote in message
news:ppBBd.22020$rL3.19909@trnddc03...
>
>
> Bob Phillips wrote:
> > "packat" <[email protected]> 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
>
>

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" <[email protected]> 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

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

"packat" <[email protected]> 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.

Dorset County Public Information Point?

--
Regards,
Tom Ogilvy

"DorsetPips" <[email protected]> wrote in message
news:[email protected]...
> "packat" <[email protected]> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> Dorset County Public Information Point?
>
> --
> Regards,
> Tom Ogilvy
>
> "DorsetPips" <[email protected]> wrote in message
> news:[email protected]...
> > "packat" <[email protected]> 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.
>
>