Excel VBA - using a Named Range in Sheet code. mystery

I know I can get the value of a workbook-scoped Named Range from anywhere. Range("MyRange").Value

But, I have a Worksheet_Change where I have a need to get the value in that Named Range. It crashes.
I can get the value in the Immediate Window, however.

I solved it by explicitly referencing it Sheets("Runtime").Range("MyRange").Value
That worked!

But why? It's a workbook-scoped Named Range!

Also tried making the Worksheet_Change procedure not a Private one. Same problem.

Thanks for any insights on this.
hindersalivaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Worksheet change event is placed on a Sheet Module so any range used there must belong to that specific sheet and if you reference a range which belong to another sheet you have to explicitly qualify it with the sheet reference.

You can access that named range on a standard module though.
0
Wayne Taylor (webtubbs)Commented:
Use [MyRange].Value instead.
0
Fabrice LambertFabrice LambertCommented:
Beware that you can have the same name for different ranges (according they are in different worksheets), wich can lead to nasty surprises.
So it is better to fully qualify your named ranges.

And I can't stress it enough:
Never ever use objects such as ActiveWorkbook, ActiveSheet, ActiveCell, ActiveChart or Selection. Because their value is user dependant, and by nature are chaotic and unpredictables.
As a developper, you don't want to use chaotic objects.
Same goes with the Sheets collection (wich is the same as ActiveWorkbook.Sheets) or Range (wich is the same as ActiveWorkbook.ActiveWorksheet.Range).

Always refer explicitly to the objects you intend to use.
0
hindersalivaAuthor Commented:
Subodh Tiwari, but I'm referencing a workbook-scoped Names Range. Surely, therefore it does not need explicit referencing? Even from a Sheet Module?
0
Wayne Taylor (webtubbs)Commented:
Surely, therefore it does not need explicit referencing? Even from a Sheet Module?

No. The use of Range() without a sheet reference makes assumptions, and in this case it assumes the range you are referencing is in the worksheet that contains the code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.