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.
Who is Participating?
Wayne Taylor (webtubbs)Connect With a Mentor 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.
Subodh Tiwari (Neeraj)Connect With a Mentor 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.
Wayne Taylor (webtubbs)Connect With a Mentor Commented:
Use [MyRange].Value instead.
Fabrice LambertConnect With a Mentor Fabrice 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.
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?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.