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