locked
Convertion of mouse pointer coordinates into graphical object coordinates RRS feed

  • Question

  • Googling around, I have found some old posts regarding the possibility to accomplish the task by using the Client Coordinates of an embedded chart. The Api Function

    Private Declare Function GetClientRect Lib "user32" (ByVal hwnd As Long, lpRect As RECT) As Long

    is exactly what I need --> It returns the coordinates of a Window object in pixels. 

    To do that I need of finding the embedded Chart's window handle.

    The result will be then used to calculate a PixelToPoint value, through a conversion formula based on the follwing bit of code

    'Conversion factors. A 4pnt padding around the Chart area is added to the Chart area width/height. 'This padding appears to be constant regardless of the size of the chart pxlPerPtX = chRECT.Right / (Ca.Width + 2 * MOUSE_XREF_OFFSET)

    pxlPerPtY = chRECT.Bottom / (Ca.Height + 2 * MOUSE_YREF_OFFSET)

    Before Excel 2007 you could accomplish the task by using the `"EXCELE"` class name, passed as argument to the api Function 

    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

    as follow:

    hWndXL = FindWindow("XLMAIN", Application.Caption)
    hWndDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
    hWndXLE = FindWindowEx(hWndDesk, 0&, "EXCELE", vbNullString)

    Actually the `"EXCELE"` class name seems not to be available anymore, so no handle reference is stored into the `hWndXLE` variable!!! 

    How can I find an embedded Chart's window handle or a different way to find the pixel coordinates of an embedded Chart's window, tacking into account the effect of different page zoom values?



    • Edited by Jumpy73 Wednesday, September 18, 2019 11:09 AM added details for a better understanding
    Wednesday, September 18, 2019 10:58 AM

All replies

  • Since 2007 embedded charts are 'windowless'.

    You can get the pixel coordinates with something like this

    x = objCht.Left * ActiveWindow.Zoom / 100

    x = x + (.PointsToScreenPixelsX(0) * ppp)

    where ppp = points-per-pixel (get it with the GetDeviceCaps API)

    Similar for the y coordinate, though from memory there's a other factor need to include with zoom for y but it's a long time since I've looked at this and don't quite recall.

    Are you sure you need to do all this with charts, can get the chart element under the cursor with 'GetChartElement'


    Friday, September 20, 2019 2:28 PM
  • I can say that the following code works flawlessly when the zoom factor is 100%

    Const LOGPIXELSX = 88               'Pixels/inch in X'
    Const LOGPIXELSY = 90               'Pixels/inch in Y'
    Const POINTS_PER_INCH As Long = 72  'A point is defined as 1/72 inches'
    
    'A 4pnt padding around the Chart area is added to the Chart area width'
    Const CHART_AREA_OFFSETX = 4
    Const CHARTAREA_OFFSETY = 4
    
    Private Type POINTAPI
         X As Double
         Y As Double
    End Type
    
    Private chObjPT As POINTAPI
    
    Private Sub m_oChart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)
    
    '... other code ...'
    
    chObjPT.X = X * PointsPerPixelX - CHART_AREA_OFFSETX
    chObjPT.Y = Y * PointsPerPixelY - CHART_AREA_OFFSETY
    
    '... other code ...'
    
    End Sub
    
    Private Property Get PointsPerPixelX() As Double
    'the size of a pixel, in points'
        Dim hDC As Long
        Dim lDotsPerInch As Long
        
        hDC = GetDC(0)
        lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
            PointsPerPixelX = POINTS_PER_INCH / lDotsPerInch
        ReleaseDC 0, hDC
    End Property
    
    Private Property Get PointsPerPixelY() As Double
    'the size of a pixel, in points'
        Dim hDC As Long
        Dim lDotsPerInch As Long
        
        hDC = GetDC(0)
        lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSY)
            PointsPerPixelY = POINTS_PER_INCH / lDotsPerInch
        ReleaseDC 0, hDC
    End Property

    I Remember that I need of coordinates (in pnts) with origin (0,0) in the upper-left corner of ChartArea,

    1------------------------------------------+
     |ChartObject                               |
     | 2--------------------------------------+ |
     | |ChartArea                             | |
     | |                                      | |
     | |   +--------------------------------+ | |
     | |   |PlotArea                        | | |
     | |   |    20 +-----------------------+| | |
     | |   |       | 'Inside' dimensions   || | |
     | |   |    10 |                       || | |
     | |   |       |                       || | |
     | |   |     0 +-----------------------+| | |
     | |   +--------------------------------+ | |
     | +--------------------------------------+ |
     +------------------------------------------+

    so the formula you are using where I find the objCht.Left property doesn't sound good for me.

    I hope this clarify better my needs

    Sunday, September 22, 2019 3:56 PM
  • In your OP I understood you wanted the chart's screen pixel coordinates from the top-left of the screen to the top left of the chart, which is what the API you quoted would have given you with EXCELE in 2003. As EXCELE does not exist since 2007 I suggested a different approach with PointsToScreenXPixelsX (though forgot better to get from the relevant Pane object), and also how to cater for zoom (probably need a bit more work with Y if zoomed).

    However now you seem to be asking for something different. In context with your OP I don't follow how your example works correctly even with zoom=100% as the chart's mousemove event returns X:Y in points from the chart's origin 0:0, same as the chartarea, and does not relate directly to the screen cursor's XY coordinates. But I guess that's because I don't follow what you're looking for.

    In passing I suspect the padding you refer to is the temporary border that appears when the chart is selected, the mousemove's X:Y will become negative if you move over the left or top border. There is also a border when the chart is not selected, by default 1 pixel.

    Could you explain your actual objective...

     
    Monday, September 23, 2019 4:39 PM
  • Hi Peter,

    thanks a lot for your attention. What I need to do is obtain an interactive chart that dynamically:

    1. displays an horizontal and a vertical line that intersect theirselves at the current mouse cursor position
    2. displays a textbox with the current X,Y axis values (x,y scatter chart), according to the current mouse cursor position
    3. displays a rectangle area, according to the mouse cursor position at mouse-down and mouse-up event respectively, for further zoom-in/zoom-out X,Y axis scales
    4. zoom-in/zoom-out X,Y axis scales by mouse scrolling

    The X,Y coordinates of mouse-down/move/up events are in pixels, the Shape objects and Axis scale values are in points and have different origins (0,0) in respect of mouse X,Y coordinates.

    For this reason, all the above mentioned features need, firstly, the mouse X,Y coordinates to be converted from pixels to points, then, to be offset for managing the different origins (0,0)

    The child objects of chart object are in this reletionship:

     A------------------------------------------+
     |ChartObject                               |
     | B--------------------------------------+ |
     | |ChartArea                             | |
     | |                                      | |
     | |   C--------------------------------+ | |
     | |   |PlotArea                        | | |
     | |   |    20 +-----------------------+| | |
     | |   |       | 'Inside' dimensions   || | |
     | |   |    10 |                       || | |
     | |   |       |                       || | |
     | |   |     0 +-----------------------+| | |
     | |   +--------------------------------+ | |
     | +--------------------------------------+ |
     +------------------------------------------+
     
    1. The X,Y mouse coordinates are given in pixels and have as origin (0,0) the upper-left corner of Chart object (A).
    2. The shape's coordinates have to be specified in points and have as origin (0,0) the upper-left corner of ChartArea object (B)
    3. The Axis coordinates have to be specified in points (then rescaled) and have as origin (0,0) the upper-left corner of PlotArea object (C)

    For the 1. and 3. feautures, that need of adding shapes to the chart object, after pixels to points conversion, a 4pnt offset has to be applyed (probably 1pnt padding when the chart is not selected +3pnt padding when the chart is selected) --> distance in points between (A) and (B) reference origins

    For the 2. and 4. feautures, that need of converting X,Y coordinates in X,Y axis scales, after pixels to points conversion, a different offset (.InsideLeft/.InsideTop property values + 4pnt padding) has to be applied --> distance in points between (A) and (C) reference origins.

    For all I above mentioned, my primary need is converting the X,Y mouse coordinates from pixels to points, taking into account the active window zoom factor too.

    I hope this clarify my needs and explain better what I understood about this topic and which are the conceptual steps I think to be followed.

    Thanks for the help you want to give me



    • Edited by Jumpy73 Tuesday, September 24, 2019 8:37 AM managed typos
    Tuesday, September 24, 2019 7:59 AM
  • Tuesday, September 24, 2019 11:09 AM
  • Hi Dan Elgaard,

    first of all thanks for your interest, but unfortunately, as far as I understood, I don't think your code can be helpfull for me.

    The function you call gets back the cursor position in screen coordinates (in pixels) while I'm interested in client coordinates (in points). What I don't know is how to do the coordinates conversion and not how to retrieve cursor coordinates. This info is already given by mouse events of Chart object

    Thanks again for taking the time to answer my question

    Tuesday, September 24, 2019 3:11 PM
  • First of all apologies, I forgot the chart events return X:Y in pixels as you said, not points as from memory I assumed.

    In a light test the following worked perfectly for me with zoom at 50, 100 & 200% to apply the first of your objectives, cross hair lines that follow the cursor (some code removed for simplicity):

    Option Explicit
    Private WithEvents cht As Chart
    Private mXoffset As Single, mYoffset As Single
    Private mPPP As Single
    Private mLineX As Shape, mLineY As Shape
    
    Public Property Set propChart(xlCht As Chart)
    
        ' get real points/pixel with the GetDeviceCaps API
        mPPP = 0.75
        ' get real offsets looping GetChartElement until ElementID = xlChartArea incrementing x or y
        mXoffset = 3.75: mYoffset = 3.75
    
        Set cht = xlCht
    
        On Error Resume Next
        Set mLineX = cht.Shapes("xLine")
        If mLineX Is Nothing Then
            Set mLineX = cht.Shapes.AddLine(0, 0, 35, 0)
            mLineX.Name = "xLine"
        End If
        Set mLineY = cht.Shapes("yLine")
        If mLineY Is Nothing Then
            Set mLineY = cht.Shapes.AddLine(0, 0, 0, 35)
            mLineY.Name = "yLine"
        End If
        
    End Property
    
    Private Sub cht_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim zm As Single
    
        zm = 100 / ActiveWindow.Zoom
        
        On Error Resume Next ' if cannot apply the new coordinates
        If mXoffset = 0 Then cht_ActivateX
        mLineX.Left = x * zm * mPPP - mXoffset - mLineX.Width / 2
        mLineX.Top = y * zm * mPPP - mYoffset
    
        mLineY.Left = x * zm * mPPP - mXoffset
        mLineY.Top = y * zm * mPPP - mYoffset - mLineY.Height / 2
    End Sub
    

    Can't position the lines off the chart so would need to shorten them when the cursor nears a side. Interestingly with zoom 200% can see the lines won't move into the 'offset' area (about 3.5 points) around the chartarea.

    Not sure I like these lines under the cursor, name can appear in a tooltip and can't select the chart (would need to intercept the mousedown and explicitly select the element under. Why not use a custom cursor(?)

    More work involved for your other objectives but should be possible with element positions & dimensions that can be returned from the chart object, and possibly GetChartElemement. On which point it returns different dimensions when the chart is/not selected, ie active.

    Not sure where you copied the typed chart 'graphic' from but pretty sure from memory Stephen Bullen posted it last century:)

    Tuesday, September 24, 2019 4:02 PM
  • Hi Peter,

    "Not sure I like these lines under the cursor, name can appear in a tooltip and can't select the chart (would need to intercept the mousedown and explicitly select the element under. Why not use a custom cursor(?)"

    I agree with you :) please look at the code below for understanding what I mean with xLine, yLine 

    Set Pa = Ch.PlotArea
    
    with mLineX
      .Left = Pa.InsideLeft
      .Top = y * zm * mPPP - mYoffset
      .Height = 0
      .Width = Pa.InsideWidth
    End With
    
    With mLineY
      .Left = x * zm * mPPP - mXoffset
      .Top = Pa.InsideTop
      .Height = Pa.InsideHeight
      .Width = 0
    End With

    The lines are deleted when then mouse goes outside the PlotArea.

    In any case, it doesn't work when zoom <> 100% yet. The lines are displayed with offset in respect of mouse cursor :(

     ' get real points/pixel with the GetDeviceCaps API
        mPPP = 0.75
        ' get real offsets looping GetChartElement until ElementID = xlChartArea incrementing x or y
        mXoffset = 3.75: mYoffset = 3.75

    ok for mPPP --> in my case I have 0.6, but probably only depends on my different screen resolution (1920x1080)

    nok for mXoffset --> how to calculate it dinamically? Is it necessary for my purpose? I don't think that it can change

    If mXoffset = 0 Then cht_ActivateX

    nok for cht_ActivateX --> what is it?

    "Not sure where you copied the typed chart 'graphic' from but pretty sure from memory Stephen Bullen posted it last century:)"

    ahahah probably yes... I don't know why I found only very old threads about this topic. I think these features are "plain vanilla" in other chart Applications, so it should be quite normal for who are using excel feel the need to have them (really strange that these features are not available by default in excel too... but this is another point). I don't know if I made myself understood but I'm not native English :)


    • Edited by Jumpy73 Wednesday, September 25, 2019 10:17 AM managed typos
    Wednesday, September 25, 2019 10:14 AM
  • OK I see what you mean about positioning the lines in the plotarea. If doing that you only need to position the xLine.top and the yLine.Left in each move event, should speed things up quite a lot. Increment a static counter and in say every 20 mousemove's re-apply the other values in case user has accidentally moved a line out of place.

    I tested in a 1920x1080 monitor with display at 125%, ie 0.6 ppp, still working correctly for me with zoom at 50, 100 & 200%. With trial & error I changed the offsets to 4.2 to make perfect. My routine for calculating them was 3.0 (ie 5x0.6), I had also increased the offset to 3.75 at 0.75 a tad as well. That was getting the offsets with GetChartElement, not sure why the slight difference between calculated and 'ideal' values.

    >cht_ActivateX --> what is it?
    It was a routine for calculating the offsets, I forgot to remove it from the post

    Rather than shape lines try adding a couple of XY lines and updating the values in cells. Or as each XY line will only need 4 data values, rather than cells hard-code the values in the series formula and update. 

    Wednesday, September 25, 2019 1:23 PM
  • I don't know what I'm wrong. I think I did it the same way of you, but the coordinates conversion seems not to be accurate by my side

    I tested in a 1920x1080 monitor with display at 125%, ie 0.6 ppp --> exactly the my same test environment

    zooming factor recalculated at each mouse-move event trap

    ChartArea padding modified as per your value (4 --> 4.2), but I don't consider this essential

    All this said, if a modify the sheet zoom factor and retest the line alignment with the mouse cursor I obtain variable results (above all for yLine)

    Furthermore, in some cases (ex. wZm=150%), I obtain the yLine longer/shorter than expected (even if its Height should be always the same and equal to Ch.PlotArea.InsideHeight property)

    If you want I can share with you my Test worksheet by sending a PM


    • Edited by Jumpy73 Wednesday, September 25, 2019 3:37 PM managed typos
    Wednesday, September 25, 2019 3:33 PM
  • It also works fine for me at 150% and zoomed.

    I using XY Series lines instead of shape type lines but it was slower (changing cell values,  series formula with array values, and direct to 'series.Values'), so not a good suggestion!

    Maybe you could upload your file to Onedrive or any file sharing site. If it's sensitive you can send if to me, see my profile.

    Thursday, September 26, 2019 7:28 AM
  • I wrote you by PM.

    Tks

    Thursday, September 26, 2019 12:46 PM