DataLabel.Formula

[WernerGg]

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

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

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?
.

 

[WernerGg]

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 Rory has told you already, you missed the point. I dont want .Text to act on formulas instead of values. I think its a bug, that it allows to set a formula. (But a highly welcome bug of course because this bug is the only way to assign a formula/reference to a data label.)

What I want is a DataLabel.Formula (and .FormulaR1C1, .FormulaLocal etc) property acting on the data labels formula. Just like at all other places of the Excel object model where formulas are involved.

And this properties do not exist in xl2007. Do they exist in xl2010?

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.

This is a funny remark. Of course you did. And you do it still all day. Or can you do VBA programming without things like

...Range("A1") = "foo": Set mySheet = ActiveSheet: Set parts = New Collection?

? This are all classes.

But I wont start an OO discussion here. We did this 30 to 20 years ago and since then everything is clear about that.

You mention semantics - now that might be more philosopical and of course it's interesting to apply it to existing languages.

Possibly my englisch is too bad. With semantics I mean the meaning of something. This has nothing to do with a programming language.

E.g the simple question "what is a line in an Excel chart?" If you look at the Excel object model (btw: it's a class model, not an object model - sorry about that) you will find that it is an incredible complicated thing and it is by far not clear what the meaning of all its attributes is.

But when you act on a line in a VBA program you must know the semantics of all that stuff. What is the associated Border? What is the BackColor in the FillFormat associated. What does the LineFormat.Style mean and so on.

 

[RoryA]

(btw: it's a class model, not an object model

I'm not entirely sure I'd agree with that, since you can't do a lot of the stuff you can normally do with classes. 'Object' seems closer, to me, though there are certainly class-like behaviours.

 

[WernerGg]

Probably that "Object Model Semantics" things are at the wrong place here. I have another thread for that: http://www.mrexcel.com/forum/showthread.php?t=502912

 

[WernerGg]

I'm not entirely sure I'd agree with that, since you can't do a lot of the stuff you can normally do with classes. 'Object' seems closer, to me, though there are certainly class-like behaviours.

Rory we are certainly not far from each other in this question. And its not such important if MS names it "class" or "object". In fact the model has "classes" that define properties and behaviour of things. And we instantiate "objects" as individual realizations of such classified things. Hence its OO.

But I really did not want to start again one of these endless discussions about OO.

 

[Norie]

Werner

I think a lot of the time people use class/object interchangeably.

Perhaps that's not fundamentally right but then again perhaps it's not that important.

I think it can actually be easier to not define things so 'restrictevly' (that's probably a new word), doing so might confuse rather than clarify.

PS You did sort of say 'Classes and OO have nothing to do with...", perhaps I misintepreted that.

 

[WernerGg]

I just want to tell you that there are two solutions for getting the formula out of a data label (or chart title) in xl2007 VBA. Both not nice but the best we have found until now.

One is by Rob Brockett. It's a sort of "brute search" solution. Working and tested and needs no modification of the sheet. See http://www.thecodecage.com/forumz/m...-formula-links-chart-titles-2.html#post740700

This other is until now just an idea by myself. See http://www.thecodecage.com/forumz/m...ing-visual-basic-applications.html#post741436 It needs a modification of the referenced cells but will then work fast.

 

[Norie]

Werner

Doesn't that first thread at codecage mainly deal with the chart Title?

It and the other thread are both interesting but the code being used, and some of the methods used, eg named ranges, Excel4Macros etc, seem a bit like overkill.

One thing I still don't quite get is why people seem surprised that ExecuteExcel4Macro doesn't work perfectly.

As far as I'm aware it's only included for some sort of backward compatibility.

Perhaps a bit like the inclusion of DATEDIF (or is it DATEDIFF) for some sort of Lotus 123 compatibility as far as I know.

 

[WernerGg]

Werner
Doesn't that first thread at codecage mainly deal with the chart Title?

Yes of course. But that's exactly the same problem.

It and the other thread are both interesting but the code being used, and some of the methods used, eg named ranges, Excel4Macros etc, seem a bit like overkill.

I would be glad if you had a simple solution. The named range is not important. You can of course use a direct reference like "Konfig!A7" instead. But your VBA will then become dependent on the sheet layout. Excel4Marcro is not used, because it does not work.

One thing I still don't quite get is why people seem surprised that ExecuteExcel4Macro doesn't work perfectly.

As far as I'm aware it's only included for some sort of backward compatibility.

Perhaps a bit like the inclusion of DATEDIF (or is it DATEDIFF) for some sort of Lotus 123 compatibility as far as I know.

[/Quote]
Nobody wants ExcecuteExcel4Marco back again. But it would be a valid solution to the problem. Hence its better than nothing. Currently there is nothing in xl2007. What I want is a DataLabel.Formula read/write property.

 

[Norie]

Werner

I wasn't mentioning ExecuteExcel4Macro in relation to your code, just generally.

All I'm saying is that I'm surprised that people are surprised using it sometimes doesn't work perfectly.

I've used it myself, most recently when trying to speed up some page setup code.

Afraid to say it didn't work, but that was probably because either the way I was using it was incorrect or that even if I was using it correctly it wouldn't
have made a difference.

 

[RoryA]

In 2010 you don't need it to speed up page setup stuff, but in earlier versions it's invaluable.