Solved

Getting Defined name values while active sheet is chart tab

Posted on 2011-03-18
15
349 Views
Last Modified: 2012-05-11
I'm getting invalid results when I try to interrogate a defined name when the active sheet is a chart tab.

I was using Evaluate(definedName) to get a non-range value.

What is the proper way to retrieve value from Defined Names while the active tab is a chart sheet?

Cheers,

Dave
0
Comment
Question by:dlmille
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
15 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35169263
Try this:

ThisWorkbook.Range("DefinedName").Value

Kevin
0
 
LVL 42

Author Comment

by:dlmille
ID: 35169516
The Defined name contains a text string which cannot be referenced with the Range method.  One of the msn developer help pages suggested using Evaluate.  That works from the debugger, but its not working in the macro code associated with the chart calculate event.  What happens on initialization (when I debug print the outcome), it prints the string data I have at that defined name, but then the event no longer works afterward.  I believe the Evaluate is messing it up.  I need another way to access the defined name values than Evaluate.

Dave
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35169603
All names can be accessed with the Range method. However, I was incorrect above - the Range method is not exposed as path of the Workbook class. You have to use the Names collection:

   Evaluate(ThisWorkbook.Names.Item("DefinedName").Value)

Kevin
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Author Comment

by:dlmille
ID: 35169807
No Joy - doesn't even work in the debug window.  I'm sure you tested this.  I'll create a blank workbook with this simple command and we can play with that...

see pic,

Dave
Picture1.png
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35169811
Yes, I did test it.

What, exactly, is the value of the defined name?

Kevin
0
 
LVL 42

Author Comment

by:dlmille
ID: 35169825
you can see it on the print screen.  WHen I did debug.print [definedName] it displayed just below.  Its a text string...

Dave
0
 
LVL 42

Author Comment

by:dlmille
ID: 35169834
Here you go.  I pared this app down to the minimum.

Its failing on the sub eventFixMyChart, in the fixMyChart module.  I've indicated in the comments where, but its where I'm trying to evaluate the value of what's stuck in the range name...  _chartwhatever_LabelRange_Settings <- Trying to get contents in a name that looks like this...

Dave
test.xlsm
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35169845
I tried to reproduce and can't. I used 2007 with a chart sheet active.

Kevin
0
 
LVL 42

Author Comment

by:dlmille
ID: 35169851
Well, hopefully what I sent you will enable that.  Note the debug.print of the data only kicks off once, then after that the calculate event never pushes it to this routine again.  If you comment out the problem line, everything's peachy.  There's something about working with names while the chart sheet is active....  But there must be a way...

Dave
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35169863
I believe the problem is that the Names collection doesn't handle names with periods. The same code works with your other names such as "_Chart1_RangeLabels_Settings".

The other problem is that these are worksheet names versus workbook names so you have to go after them from the worksheet object:

   Evaluate(Sheet1.Names.Item("DefinedName").Value)

Kevin
0
 
LVL 42

Author Comment

by:dlmille
ID: 35169886
Periods aren't the problem, and don't come into play with this particular error (at least at this exact moment, working with the chart sheet - but don't think it was a problem with the embedded charts that they support...)  

The code is actually failing on the testRangeSettings = Evaluate(rangeLabelSettings) , but perhaps with the refinement you suggest, it will succeed.

PS -  the assignment to the range, rangeLabelName works perfectly, but of course, that's a range, not a text string...  Why the scope is not needed with this but IS needed for the string, is puzzling still.

Dave
0
 
LVL 42

Author Comment

by:dlmille
ID: 35169890
What I'm trying to do is define range names that describe the chart affected, then return the Category/XValues range that's stored there.  That part works.  The enhancement I'm making is to store settings in a similar defined name (storing it works, but retrieving it is the problem) so that my chart wizardry can act on those stored settings...

My scheme was working, until this stumbling block.  I might be able to retrieve the scope of the labelrangesettings so I can build the range name via the chart's  category/XValues range so ----

anyway, I need to do that before I can test your proposed solution...

Dave
0
 
LVL 42

Author Comment

by:dlmille
ID: 35169993
We're golden. I fully qualified the defined name reference and then used evaluate like this:

    Set XValuesRange = getXValuesRange(activechart)
    rangeLabelName = "_" & Replace(activechart.Name, " ", ".") & "_RangeLabels"
    rangeLabelSettings = "'" & XValuesRange.Parent.Name & "'!_" & Replace(activechart.Name, " ", ".") & "_RangeLabels_Settings"

and the Evaluate(rangeLabelSettings) worked correctly.

Any consequences to this approach versus the Evaluate(sheet.names.item(name).value) approach you suggested?

Finally, I don't need to do Evaluate anymore.  With the fully-qualified name definition:

testRangeSettings = ActiveWorkbook.Names(rangeLabelSettings).RefersTo

works properly so I don't have to use Evaluate (which can cause problems - at least it created memory problems for me after calling it a few thousand times in a loop, recently, lol)

Dave
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35170411
If it works then you're good. I don't see any issues at this point. I still don't like the periods in the name as they do cause problems but you are not concerned.

Kevin
0
 
LVL 42

Author Comment

by:dlmille
ID: 35171618
I'm indifferent and can make them underscores - will do to avoid the unanticipated.

Thanks,

Dave
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question