How to zoom on charts using VBA

[Rasm]

Just wondering if Excel has a zoom function where I can simply highlight a area on a chart - by click and drag kinda deal - then that area is zoomed in on.

I want to do this using VBA

 

[Rasm]

AlphaFrog - I will definitely take a look - thanks - may take me a bit - very busy.

 

[Jaafar Tribak]

Does anybody know if the Point object of a chart Series collection has a Left/Top Properties ? In other words, can we know the position of a Point ? I am writing some code where I'll need this info. Thanks.

 

[jolivanes]

Jaafar.
This is all way over my head but if I remember correctly, Andy Pope had a chart with axis in the middle of the chart and when you moved the mouse pointer in the chart it gave the angle relative to the centre of the chart. I don't know how it was programmed but if I happen to find that workbook, I'll post it.
John

 

[Rasm]

Jaafar - If you find the .Left & .Top property for a point - let me know - I have searched and searched and searched.

if you add a textbox on your chart the put the box to intercept the X axis - lets say at numerice value 12 - if you then resize the chart - the Textbox dont follow. Same if you copy & paste a chart into Word (my Charts are on a separate Chart tab - textboxes and other shapes move their location on the chart in the Word document. These charts are a nightmare.

 

[RoryA]

I suspect the old GET.CHART.ITEM XLM function is what is needed.

 

[Jaafar Tribak]

Thanks everyone for the feedback.I'll investigate all suggestions.

Rorya,. would that work for XL 2007 as well ? I can't try it now as I am away from home .

 

[Rasm]

AlphaFrog
I did try you code - I like the concept. So I need two scrollbars per axis - one for minimum and one for maximum. That is no problem - I have posted a separate thread as I want to add these double scrollbars using code (so no userform) & next I dont want the Scrollbar_click even to reside outside the sheet code - I assume a class module. Opps gotta go - dinner ready

 

[RoryA]

Yes, it works in 2007 too - just bear in mind that it returns co-ordinates from the bottom left of the chart area.

 

[Jaafar Tribak]

Yes, it works in 2007 too - just bear in mind that it returns co-ordinates from the bottom left of the chart area.

Hi Rory.

I've got this done or almost for XL2007 but I am having a problem in XL2003.

I am converting the chart data point coordinates (which I obtained with the use of the ExecuteExcel4Macro you suggested ) from points to screen pixels (see code below) but the conversion only works in XL2007. I get inaccurate pixels when using the same code for the same chart in XL2003.

<TABLE style="WIDTH: 258pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=343 x:str><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2157" width=59><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=80 align=right x:num>2003</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 44pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=59></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 154pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" width=204 colSpan=3>343,75 X_coordinates in Points.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" colSpan=3>35 Y_coordinates in Points.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" colSpan=3>785 X_coordinates in Pixels.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" colSpan=3>178 Y_coordinates in Pixels.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 align=right x:num>2007</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" colSpan=3>342.75 X_coordinates in Points.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" colSpan=3>33.75 Y_coordinates in Points.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" colSpan=3>483 X_coordinates in Pixels.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" colSpan=3>231 Y_coordinates in Pixels.</TD></TR></TBODY></TABLE>


Any thoughts on why this happens ? do this XLM macro behave differently depending on the versions of Excel ?

Test code:

Option Explicit
 
Private Type POINTAPI
  x As Long
  y As Long
End Type

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

Sub Test()
 
    Dim PALeft As Double
    Dim PATop As Double
    
    Dim PixLeft As Long
    Dim PixTop As Long
    
    
    ActiveSheet.ChartObjects(1).Activate
    
[COLOR=seagreen][B]   [COLOR=green]'\\retrieve data point (S1P6) coordinates in Points.[/COLOR][/B][/COLOR]
    
    PALeft = ActiveSheet.ChartObjects(1).Left + _
    ExecuteExcel4Macro("get.chart.item(1,1,""S1P6"")") _
    
    PATop = ActiveSheet.ChartObjects(1).Top + ActiveSheet.ChartObjects(1).Height _
    - ExecuteExcel4Macro("get.chart.item(2,1,""S1P6"")")
    
    
    Debug.Print PALeft & vbTab & "X_coordinates in Points."
    
    Debug.Print PATop & vbTab & "Y_coordinates in Points."
    
    
    [COLOR=green][B]'\\convert data point coordinates to screen pixels.[/B][/COLOR]
[COLOR=green][/COLOR]    PixLeft = PTtoPX(PALeft, False) * (ActiveWindow.Zoom / 100) + _
    ActiveWindow.PointsToScreenPixelsX(0)
    
    PixTop = PTtoPX(PATop, True) * (ActiveWindow.Zoom / 100) + _
             ActiveWindow.PointsToScreenPixelsY(0)
    
    Debug.Print PixLeft & vbTab & "X_coordinates in Pixels."
    
    Debug.Print PixTop & vbTab & "Y_coordinates in Pixels."
 
   [COLOR=green] [B]'\\ move the mouse pointer to the chart data point (S1P6)
[/B][/COLOR]    SetCursorPos PixLeft, PixTop
    
End Sub

Private Function ScreenDPI(bVert As Boolean) As Long
 
    Static lDPI(1), lDC
 
   If lDPI(0) = 0 Then
        lDC = GetDC(0)
        lDPI(0) = GetDeviceCaps(lDC, LOGPIXELSX)
        lDPI(1) = GetDeviceCaps(lDC, LOGPIXELSY)
        lDC = ReleaseDC(0, lDC)
    End If
 
    ScreenDPI = lDPI(Abs(bVert))
 
End Function
 
Private Function PTtoPX _
(Points As Double, bVert As Boolean) As Long
 
    PTtoPX = Points * ScreenDPI(bVert) / PointsPerInch
 
End Function

 

[Rasm]

Jaafar

This will be cool when it is done.

Thanks for sticking with it.