DataLabel.Formula

WernerGg

WernerGg

New Member
Joined
Oct 19, 2010
Messages
43
Chart data Series can have DataLabels at all or some Points. Unfortunately no data Range as for X- and Y-Values can be associated to the labels as a whole. But at least one can manually enter a formula like "='MySheet'!$D$17" into a label and such have labels with arbitrary dynamic text.

The Excel DataLabel class does not support this formulas. It has no methods or properties for that. No idea why the <ACRONYM title=Microsoft>MS</ACRONYM> programmers just forgot that and do not repair that since years. Instead the class has two properties .Caption and .Text which provide the value (but not the formula) of the label text.

I know about an old trick using
Code: 
strFormula = ExecuteExcel4Macro("GET.FORMULA(SELECTION())")
but this does no longer work in Excel 2007.

What is missing is a DataLabel.Formula property or methods so that on can write
Code: 
With aDataLabel 
    oldFormula = .Formula 
    newFormula = NowIModifyThat(oldFormula) 
    .Formula = newFormula 
End With

In Excel 2010 this seems to exist but not in earlier versions.

Does anybody know how to get and set the formula of a data label? Does anybody have an undocumented trick or a DLL for that?
.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sort by date Sort by votes
Werner

Why are you so surprised by these things?

Every application, programming language has it's anomalies, non-standard features.

As for the 'trick' not working, that is strange.:)

It works for me in Excel 2000 and 2010, perhaps they just skipped 2007.

By the way I'm not sure the term 'class' is quite right, unless perhaps you are using C++ or similar.

If you are perhaps this will be of interest Exce;:_Chart Class Reference.
<!-- doxytag: class="Excel::_Chart" -->
 
Upvote 0
Norie said:
Werner

Why are you so surprised by these things?

Every application, programming language has it's anomalies, non-standard features.
Click to expand...
I am surprised beacause I have a very high rating for MS designers/programmers. It's not understandable why they made such a poor model for DataLabels.

By the way I'm not sure the term 'class' is quite right, unless perhaps you are using C++ or similar.
Click to expand...

Classes and OO have nothing to do with C++, Smalltalk or Java. OO is just the standard way of thinking and designing software. Every (!) software, even those written in old non-OO languages. Of course MS and VBA have this OO-thinking as well, although some standard OO-features are missing.

Anyway, I don't talk about languages but about design philosophie. And then it is weak to allow setting an objects property (even if in an obscure way
Code: 
aDataLabel.Text = "='MySheet'!$D$17"
) but not reading it back again (
Code: 
aString = aDataLabel.Text
delivers the value of D17 not the formula/reference).

If you are perhaps this will be of interest Exce;:_Chart Class Reference.
Click to expand...
Thanks for that link, but this is just another generated object model documentation with no semantics. See my thread http://www.mrexcel.com/forum/showthread.php?t=502912 for a possible general object model discussion.
 
Upvote 0
Werner

Why should aString = aDataLabel.Text 'deliver' what you seem to want, ie the formula, when you are looking at the Text property?

Perhaps you should try it's Formula, FormulaR1C1, FormulaLocal etc property?

As for the 'class' thing, perhaps I used the wrong term but I could have sworn the last time I programmed in C++, C#, Java I used classes of some sort.

Or was it glasses?:)

Have I been doing something wrong all along?

I can sort of get the OO thing but I think it's more a case of people considering languages that aren't really OO to be fully OO.

And yes you could say every language is OO, or has elements of it but then again you might also say that every language has some sort of procedural element to it.

You mention semantics - now that might be more philosopical and of course it's interesting to apply it to existing languages.
 
Upvote 0
Norie said:
Why should aString = aDataLabel.Text 'deliver' what you seem to want, ie the formula, when you are looking at the Text property?

Perhaps you should try it's Formula, FormulaR1C1, FormulaLocal etc property?
Click to expand...

I think you missed the point. There is no Formula property available. You assign the Formula using the Text property, so it's not unreasonable to expect to be able to read it back using the same property, but you can't.
 
Upvote 0
Rory

There is a Formula property.
 
Last edited:
Upvote 0
I'd say you were using 2010 then?
 
Upvote 0
Oops, my bad.:)

I suppose it is unreasonable not being able to read it back, but then again the formula does seem to be evaluated at some point.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
439
AndyEd
A
D
  • Question
Excel on Mac - file repair issues
Replies
1
Views
1K
dskills
D
D
  • Question
dynamic data changes
Replies
0
Views
120
DSCHROE67
D
A
  • Solved
Calendar issue
Replies
1
Views
1K
AndyEd
A
E
  • Question
Old XLSM files crashing Excel; looking for help to troubleshoot, please
Replies
9
Views
1K
jkpieterse
jkpieterse

Forum statistics

Threads
1,215,360
Messages
6,124,491
Members
449,166
Latest member
hokjock

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top