Chart Coordinates

There are a number of different coordinate systems which you may have to convert between when working with charts.

  • An XY Scatter Chart xy coordinates

  • The mouse pointer coordinates from the MouseMove event (measured in pixels). These coordinates are affected by the worksheet zoom factor

  • Drawing objects (pictures and graphics) have a top left reference

  • The XLM function GET.CHART.ITEM locates chart objects in points

The following code converts mouse pointer coordinates into data coordinates and graphical object coordinates


Class Module - EventClass

Create a class module called EventClass


Public WithEvents ExcelChartEvents As Excel.Chart 

Private Sub ExcelChartEvents_MouseMove( _
    ByVal Button As Long, _
    ByVal Shift As Long, _
    ByVal x As Long, _
    ByVal y As Long)

Dim oAxis As Excel.Axis
Dim dzoom As Double
Dim dxval As Double
Dim dyval As Double
Dim dpixelsize As Double

    dzoom = ActiveWindow.Zoom / 100
    
    dpixelsize = PointsPerPixel()
    
    Set oAxis = ActiveChart.Axes(xlCategory)
    dxval = oAxis.MinimumScale + (oAxis.MaximumScale - oAxis.MinimumScale) * _
            (x * dpixelsize / dzoom - _
                (ActiveChart.PlotArea.InsideLeft + ActiveChart.ChartArea.Left)) _
            / ActiveChart.PlotArea.InsideWidth
        
    Set oAxis = ActiveChart.Axes(xlValue)
    dyval = oAxis.MinimumScale + (oAxis.MaximumScale - oAxis.MinimumScale) * _
            (1 - (y * dpixelsize / dzoom - _
                (ActiveChart.PlotArea.InsideTop + ActiveChart.ChartArea.Top)) _
            / ActiveChart.PlotArea.InsideHeight)
           
    Application.StatusBar = "(" & Application.Round(dxval, 2) & " , " & _
                                  Application.Round(dyval, 2) & ")"

    If (Shift = 1) Then
        dxval = (x * dpixelsize / dzoom - ActiveChart.ChartArea.Left)
        dyval = (y * dpixelsize / dzoom - ActiveChart.ChartArea.Top)
        
        ActiveChart.Shapes(1).Left = dxval - ActiveChart.Shapes(1).Width / 2
        ActiveChart.Shapes(1).Top = dyval - ActiveChart.Shapes(1).Height / 2
    End If
End Sub


Standard Module

Add a code module

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long 
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Const LOGPIXELSX = 88 'Pixels/inch in X

'A point is defined as 1/72 inches
Private Const POINTS_PER_INCH As Long = 72

Public Chart1 As EventClass

'The size of a pixel, in points
Public Function PointsPerPixel() As Double
Dim hDC As Long
Dim lDotsPerInch As Long

   hDC = GetDC(0)
   lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
   PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
   ReleaseDC 0, hDC
End Function

Public Sub ChartCoordinates()
   Set oChart1 = New EventClass
   Set oChart1.ExcelChartEvents = ActiveChart
End Sub


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext