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

 

[Jaafar Tribak]

Hi. If the chart is embeeded in a worksheet and the linked cell is in the same worksheet then the following function should work and is pretty quick.

Option Explicit
 
Private Function GetDataLabelFormula _
(Sht As Worksheet, Datalbl As DataLabel) as String
 
    Dim sText As String
    Dim sFormula As String
    Dim sFirstAddress As String
    Dim Cell As Range
 
    sText = Datalbl.Text
 
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
 
    With Sht.UsedRange
        Set Cell = .Find(sText, LookIn:=xlValues)
        If Not Cell Is Nothing Then
            sFirstAddress = Cell.Address
            Do
                sFormula = Cell.Formula
                Cell = "Dummy"
                Sht.Calculate
                If Datalbl.Text <> sText Then
                    Cell.Formula = sFormula
                    GetDataLabelFormula = _
                    "=" & Sht.Name & "!" & Cell.Address
                    Exit Do
                End If
                Cell.Formula = sFormula
                Set Cell = .FindNext(Cell)
                DoEvents
            Loop While Not Cell Is Nothing And _
            Cell.Address <> sFirstAddress
        End If
    End With
 
    Set Cell = Nothing
 
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
 
End Function
 
Sub Test()
 
    MsgBox GetDataLabelFormula(Sheet1, _
    Sheet1.ChartObjects(1).Chart.SeriesCollection(1).Points(1).DataLabel)
 
End Sub

 

[WernerGg]

Hi. If the chart is embeeded in a worksheet and the linked cell is in the same worksheet then the following function should work and is pretty quick.

Yes Jaafar, thanks. This is a working solution albeit a bit strange. It is what I called Rob Barrets "brute search" solution in my post #16. He elaborated it even for searching the hole workbook. It might become problematic if two datalabels refer cells which reference each other, but this seems to be an academic problem.

But restricting it to the sheet containing the diagram will not be sufficient for important cases. If you have a sheet with an embedded diagram with labels referencing cells on that sheet and then copy that sheet into another instance within the same workbook, your code will break down. The formulas in the labels will not be updated by Excel for the new sheet. The copied diagram labels will now reference another sheet - still the old one. Hence you need VBA for adjusting the formulas. This is an important use case for our problem.

For good or bad, until now this "brute search" is the only solution for xl2007 which requires no modification of the sheet.

 

[Norie]

Werner

That's the first time you've mentioned a 'problem'.

Does that mean that you are actually looking for a solution you can apply in a particular situation?

Is finding a suitable method to find out the formula being used for chart data labels and charts needed for whatever it is you are actually doing?

 

[WernerGg]

Werner

That's the first time you've mentioned a 'problem'.

You are funny, Norie. Please see my first post in this thread and practically all others. I don't like to offend you, but maybe you read a bit more and write a bit less.

Does that mean that you are actually looking for a solution you can apply in a particular situation?

Yes

Is finding a suitable method to find out the formula being used for chart data labels and charts needed for whatever it is you are actually doing?

Yes

 

[Norie]

Werner

I am slightly offended and wonder what you mean by 'funny'.

You have not actually said why you want to do this, not in this thread anyway

How exactly, in a 'practical' situation are you going to apply a solution and for what purpose?

You did mention something about XY scatter diagrams somewhere but didn't really expand much on it.

 

[Jaafar Tribak]

Workbook example

A different approach which was drawn to my attention by Rory the other day. It works by brievely selecting the Datalabel and reading the formulabar text. It is ugly and messy but it seems to work fine.

The major limitation of this workaround is that the excel window has to be active when calling the routine. The good thing about this trick is that the linked cell can be in any worksheet or any workbook yet it is quicker because it doesn't need to search or change any cells in the current sheet, workbook or other workbooks.

I have added some code to lock the screen update to make it as smooth as possible.

Code in a Standard module :

Option Explicit
 
Private Declare Function FindWindowEx Lib "user32" _
    Alias "FindWindowExA" ( _
    ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long
 
Private Declare Function FindWindowA Lib "user32" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
 
Private Declare Function GetActiveWindow Lib "user32" () As Long
 
Private Declare Function SendMessage Lib "user32" _
    Alias "SendMessageA" ( _
    ByVal hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    ByVal lParam As Long) As Long
 
Private Declare Function PostMessage Lib "user32" _
    Alias "PostMessageA" _
    (ByVal hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long
 
Private Declare Function SetFocus Lib "user32.dll" _
    (ByVal hwnd As Long) As Long
 
Private Declare Function LockWindowUpdate Lib "user32" _
    (ByVal hwndLock As Long) As Long
 
Private Declare Function SetTimer Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long
 
Private Declare Function KillTimer Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal uIDEvent As Long) As Long
 
Private Const WM_SETFOCUS = &H7
Private Const WM_KILLFOCUS = &H8
 
Private bFBVisible As Boolean
Private oDataObject As Object
Private oActivecell As Range
Private lFBhwnd As Long
Private lXLhwnd As Long
 
Sub test()
 
    GetDtataLabelFormula sh:=Sheet1, chartIndex:=1, serie:=1, point:=4
 
End Sub
 
Sub GetDtataLabelFormula(sh As Worksheet, _
chartIndex As Long, serie As Long, point As Long)
 
    On Error GoTo Oops
 
    'lock the screen update.
    Application.ScreenUpdating = False
    LockWindowUpdate lXLhwnd
 
    'store the FBar visible state.
    bFBVisible = Application.DisplayFormulaBar
 
    'get the FBar hwnd.
    lXLhwnd = FindWindowA("XLMAIN", vbNullString)
    lFBhwnd = FindWindowEx(lXLhwnd, 0, "EXCEL<", vbNullString)
 
    'make sure xl wnd is active.
    If GetActiveWindow <> lXLhwnd Then GoTo Oops
 
    'store the activecell.
    Application.Windows(ThisWorkbook.Name).Activate
    Set oActivecell = ActiveCell
 
    'show the FBar.
    If Not bFBVisible Then Application.DisplayFormulaBar = True
 
    'select the datalabel.
    sh.ChartObjects(chartIndex).Activate
    ActiveChart.SeriesCollection(serie).Points(point).DataLabel.Select
 
    'run timer to read the FBar text.
    SetTimer lXLhwnd, 0, 1, AddressOf ReadFormulaBar
 
    'restore the FBar initial visible state.
    Application.DisplayFormulaBar = bFBVisible
 
    Exit Sub
 
Oops:
 
    LockWindowUpdate 0
    DoEvents
 
    If Not oActivecell Is Nothing Then
        oActivecell.Select
    End If
 
    MsgBox Err.Description
 
End Sub
 
Private Sub ReadFormulaBar()
 
    On Error Resume Next
 
    'done with timer so remove it.
    KillTimer lXLhwnd, 0
 
    'set the keyboard focus to the FBar window.
    SetFocus lFBhwnd
    PostMessage lFBhwnd, WM_SETFOCUS, 0, 0
 
    'copy the FBar text to the clipboard.
    With Application
        .SendKeys "{HOME}"
        .SendKeys "^+{END}"
        .SendKeys "^c"
        .SendKeys "{ESC}"
    End With
 
    'done so focus back to xl.
    SendMessage lFBhwnd, WM_KILLFOCUS, 0, 0
 
    'run a second timer to get the FBar text from the clipboard.
    SetTimer lXLhwnd, 0, 1, AddressOf RetrieveFormula
 
End Sub
 
Private Sub RetrieveFormula()
 
    On Error Resume Next
 
    'done with timer so we remove it here.
    KillTimer lXLhwnd, 0
 
    'get a pointer to the dataobject interface.
    Set oDataObject = _
    GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 
    'retrieve the text from clipboard.
    oDataObject.GetFromClipboard
 
    'select back the activecell.
    DoEvents
    oActivecell.Select
 
    'display the datalabel formula.
    MsgBox oDataObject.GetText()
 
    'unlock window update.
    LockWindowUpdate 0
 
    'cleanup.
    Set oDataObject = Nothing
 
End Sub

I've only tested this on Excel 2003 so I am not sure if it will work in 2007 which is where it is needed.

 

[WernerGg]

Workbook example

A different approach which was drawn to my attention by Rory the other day. It works by brievely selecting the Datalabel and reading the formulabar text. It is ugly and messy but it seems to work fine.

The major limitation of this workaround is that the excel window has to be active when calling the routine. The good thing about this trick is that the linked cell can be in any worksheet or any workbook yet it is quicker because it doesn't need to search or change any cells in the current sheet, workbook or other workbooks.

...

I've only tested this on Excel 2003 so I am not sure if it will work in 2007 which is where it is needed.

Great Jaafar! This is the third solution now. One which is fast and needs no modification of the sheet. Hence the best we have under our three solutions. I was thinking about that solution but did not know the win API details necessary for that.

The price is that it is cruel programming style. But what shall we do when MS leaves us alone.

Your actual code does not work immediately under xl2007, I tried to run and debug it. It leads to an empty result in the last MsgBox. I could not fix it in 10 minutes since I am not familiar with Win APIs. But this is not a problem.

Thanks again.

 

[Jaafar Tribak]

Your actual code does not work immediately under xl2007, I tried to run and debug it. It leads to an empty result in the last MsgBox. I could not fix it in 10 minutes since I am not familiar with Win APIs. But this is not a problem.

Thanks again.

Hi Werner. Did you succed in making the code work for Excel 2007 in the end ?

By the way. There is a little mistake when locking the screen update in the above code . Also, I tested the code on different machine ( always excel 2003 ) and noticed a couple of small problems with the Office Clipboard...

Here is the updated code that should correct the above mentioned problems :

Workbook example.

Option Explicit
 
Private Declare Function FindWindowEx Lib "user32" _
    Alias "FindWindowExA" ( _
    ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long
 
Private Declare Function FindWindowA Lib "user32" ( _
    ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
 
Private Declare Function GetActiveWindow Lib "user32" () As Long
 
Private Declare Function SendMessage Lib "user32" _
    Alias "SendMessageA" ( _
    ByVal hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    ByVal lParam As Long) As Long
 
Private Declare Function PostMessage Lib "user32" _
    Alias "PostMessageA" _
    (ByVal hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long
 
Private Declare Function SetFocus Lib "user32.dll" _
    (ByVal hwnd As Long) As Long
 
Private Declare Function LockWindowUpdate Lib "user32" _
    (ByVal hwndLock As Long) As Long
 
Private Declare Function SetTimer Lib "user32" _
    (ByVal hwnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long
 
Private Declare Function KillTimer Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal uIDEvent As Long) As Long
 
Private Declare Function CloseClipboard Lib "user32" _
() As Long
 
Private Declare Function OpenClipboard Lib "user32" _
(ByVal hwnd As Long) As Long
 
Private Declare Function EmptyClipboard Lib "user32" _
() As Long
 
Private Const WM_SETFOCUS = &H7
Private Const WM_KILLFOCUS = &H8
 
Private bFBVisible As Boolean
Private oDataObject As Object
Private oActivecell As Range
Private lFBhwnd As Long
Private lXLhwnd As Long
 
Sub test()
 
    GetDtataLabelFormula sh:=Sheet1, chartIndex:=1, serie:=1, point:=4
 
End Sub
 
Sub GetDtataLabelFormula(sh As Worksheet, _
chartIndex As Long, serie As Long, point As Long)
 
    On Error GoTo Oops
 
    'store the FBar visible state.
    bFBVisible = Application.DisplayFormulaBar
 
    'get the FBar hwnd.
    lXLhwnd = FindWindowA("XLMAIN", vbNullString)
    lFBhwnd = FindWindowEx(lXLhwnd, 0, "EXCEL<", vbNullString)
 
    'lock the screen update.
     Application.ScreenUpdating = False
     LockWindowUpdate lXLhwnd
 
    'make sure xl wnd is active.
    If GetActiveWindow <> lXLhwnd Then GoTo Oops
 
    'store the activecell.
    Application.Windows(ThisWorkbook.Name).Activate
    Set oActivecell = ActiveCell
 
    'show the FBar.
    If Not bFBVisible Then Application.DisplayFormulaBar = True
 
    'select the datalabel.
    sh.ChartObjects(chartIndex).Activate
    ActiveChart.SeriesCollection(serie).Points(point).DataLabel.Select
 
    'run timer to read the FBar text.
    SetTimer lXLhwnd, 0, 1, AddressOf ReadFormulaBar
 
    'restore the FBar initial visible state.
    Application.DisplayFormulaBar = bFBVisible
 
    Exit Sub
 
Oops:
 
    LockWindowUpdate 0
    DoEvents
 
    If Not oActivecell Is Nothing Then
        oActivecell.Select
    End If
 
    MsgBox Err.Description
 
End Sub
 
Private Sub ReadFormulaBar()
 
    On Error Resume Next
 
    'done with timer so remove it.
    KillTimer lXLhwnd, 0
 
    'set the keyboard focus to the FBar window.
    SetFocus lFBhwnd
    PostMessage lFBhwnd, WM_SETFOCUS, 0, 0
 
    'copy the FBar text to the clipboard.
    With Application
        .SendKeys "{HOME}"
        .SendKeys "^+{END}"
        .SendKeys "^c"
        .SendKeys "{ESC}"
    End With
 
    'done so focus back to xl.
    SendMessage lFBhwnd, WM_KILLFOCUS, 0, 0
 
    'run a second timer to get the FBar text from the clipboard.
    SetTimer lXLhwnd, 0, 1, AddressOf RetrieveFormula
 
End Sub
 
Private Sub RetrieveFormula()
 
    On Error Resume Next
 
    'done with timer so we remove it here.
    KillTimer lXLhwnd, 0
 
    'get a pointer to the dataobject interface.
    Set oDataObject = _
    GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
 
    'retrieve the text from clipboard.
    oDataObject.GetFromClipboard
 
    'select back the activecell.
    DoEvents
    oActivecell.Select
 
    'display the datalabel formula.
    MsgBox oDataObject.GetText()
 
    'clear the clipboard.
    OpenClipboard 0
    EmptyClipboard
    CloseClipboard
 
    'unlock window update.
    LockWindowUpdate 0
 
    'cleanup.
    Set oDataObject = Nothing
 
End Sub

I'll try the code on Excel 2007 later on and see if I can edit/debug it so it works as it does for excel 2003.

 

[WernerGg]

Hi Werner. Did you succed in making the code work for Excel 2007 in the end ?

No, unfortunately it does not work. It gets an empty text out of the clipboard.
I am pretty shure that window handling is correct and if I break just before the second settimer (after pasting the formula) I can see the formula and the cursor in the formula bar. Looks good. But this oDataObject seems to get nothing from the clipboard.

Some other questions:

• The first FindWindow could be replaced by "lXLhwnd = Application.hwnd"?
• Do we really need that three step approach with timers? Why? If Windows really acts asynchrounosly somewhere these timer would not be reliable anyway
• In the API docs I cannot find "LockWindowUpdate 0" for unlocking. Should you not use "UnLockWindowUpdata aLhwnd" instead?

 

[RoryA]

Per MSDN, the parameter to LockWindowUpdate is:
"The window in which drawing will be disabled. If this parameter is NULL, drawing in the locked window is enabled."
0 or 0& are effectively Null when calling from VB(A).