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

 

[Norie]

Werner

I do realise this, I can read and I can also write, I've clearly stated I'm using Excel 2010.

Again I apologise for any offence given and my obvious ignorance.

Oh, and I did mention I would test it on other versions and also that when I did it didn't work.

 

[Jaafar Tribak]

I made the error handling code of my routines

• GetSelectableFormula_DoesAll
• GetSelectableFormula_Open,
  GetSelectableFormula,
  GetSelectableFormula_Close

a bit more robust. You can find the workbook with the code and a test procedure in the MS Windows/Office Live/Office SkyDrive cloud:
http://cid-756851a4f36096af.office.live.com/view.aspx/Public Documents/getFormulaXL2007.xlsm



It's a bit tricky to download from the MS cloud:

• Click the link
• An Excel Web-App will start which shows the workbook. But this is useless, because it can handle no VBA. Don't use the App's File menue for download. This will give you a castrated version without the code.
• Left click the file name "getFormulaXL2007.xlsm" on the very top above the Excel Web-App.
• This will lead to another page where you can see and download the pure file.

And I registered like Jaafar on Box.net. You can find the workbook there:
http://www.box.net/shared/xl3l6q0g5z
The download is straight forward.

Thanks for the link WernerGg.

I am glad you got there in the end.