Problem getting the value of a Named Range in code

  • Thread starter Thread starter tbone
  • Start date Start date
T

tbone

I'm not very experienced with Excel VBA yet, so I often use the
immediate window to try out various syntax before coding it up.

I found something that works in the immediate window when nothing is
running:

? Range("OnSiteRate").value

does NOT work when the code is run or in the immediate window with the
code paused due to the error. I found a workaround:

? ThisWorkbook.Names("OnSiteRate").RefersToRange

but I'm curious as to why this doesn't work in both circumstances.

Thanks
tbone
 
Both methods work for me.

If you tried to used

Range("OnSiteRate").value

in a sheet module and the named range "OnSiteRate" was on another sheet,
then you have to qualify it with the sheet name where it exists because an
unqualifed "Range" reference in a sheet module is the same as saying

me.Range

In a general module, you don't have this problem.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with progamatically defined named ranges 3
Named Range maker code 9
for each in a range 1
How to get named range value? 5
Getting the entry of a Named Range 4
Problem using a named range 5
A range variable consisting of multiple ranges. 7
Modifying a Defined Name's Contents 10

Back
Top