Challenging problem: exact X and Y coordinates at any point

giotto60

New Member
Joined
Mar 2, 2006
Messages
17
Hi there, I am trying to create a macro that will enable me to easily zoom in a graph, you know, like in picture editing programs where you draw a rectangle with the mouse by clicking, holding and releasing and the program will zoom on the selected area.

I can't believe Excel doesn't natively integrate this, by the way. Zooming in by changing the axis scale is a nightmare.

The problem I have encountered in creating such a macro is that I can't determining the EXACT x and y coordinates at any point of a plot. I have seen a similar post that I thought would give me the solution, here:

http://www.mrexcel.com/board2/viewtopic.php?t=195683&highlight=coordinates

But it doesn't help. This is what I have so far:

Option Explicit
Dim ChWide As Single
Dim ChLeft As Single
Dim ChTop As Single
Dim ChTall As Single
Dim xStart As Single
Dim xEnd As Single
Dim yStart As Single
Dim yEnd As Single
Dim Xcoord As Single
Dim Ycoord As Single


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

With ActiveChart
With .PlotArea
ChWide = .InsideWidth
ChLeft = .InsideLeft
ChTop = .InsideTop
ChTall = .InsideHeight
End With

With .Axes(xlCategory)
xStart = .MinimumScale
xEnd = .MaximumScale
End With

With .Axes(xlValue)
yStart = .MinimumScale
yEnd = .MaximumScale
End With
End With

Xcoord = (((x - ChLeft) / ChWide)) * (xEnd - xStart)
Ycoord = (((y - ChLeft) / ChWide)) * (yEnd - yStart)

MsgBox ("X=" & Xcoord & " , Y=" & Ycoord)

End Sub




But it doesn't give me the right X and Y values in the MsgBox. Are the .InsideXXX properties I am using wrong? I can't find any others.

Thanks a lot
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
This is a nontrivial exercise; I've just spent a couple full days programming such a utility for a client. It would have taken me longer, but a couple years ago I'd kicked this around for several weeks in my free time, the results being that I had some algorithms on hand, and I learned so many arcane facts about the Excel chart's object model.

There are a number of things you need to think about.

*There's a different formula if an axis is plotted in reverse order.

*Also, if the chart is not an XY chart, you need some very different formulas for the X axis.

*The behavior is different in a chart sheet if you don't have it set to scale to the window (in fact, I've found it nigh impossible if this isn't set).

*The x,y arguments in the mouse click event procedure are in pixels, and the coordinates of the chart (.InsideLeft, etc.) are in points. This is a major source of error. Closely related to this is having to account for a window zoom that differs from 100%.

*I seem to recall that theChLeft and ChTop also need to include the ChartArea.Left and .Top.

*Once you have defined the new axis ranges, you also need to know how to rescale your chart. For a value (or date scale) axis, it's pretty easy: just change the minimum and maximum scale. Well, maybe not so easy, because you don't want a min of 47.23358412 and a max of 92.1554863; you need an algorithm to provide "nice" scaling parameters.

*Rescaling a category axis is accomplished not by adjusting the axis but by changing the beginning and ending points of the series data range.

Aside from the above items, your Ycoord formula should use ChTop and ChTall.


_______

2006 Excel User Conference, 19-21 April, Atlantic City, NJ
http://peltiertech.com/Excel/ExcelUserConf06.html
_______
 
Upvote 0
Jon,

Thanks a lot for your reply. You raised some problems which I didn't think about, but I forgot to mention the following points:

- I am dealing only with XY plots;
- The axes are not in reverse order;
- The chart sheet is always set to scale to the window;

So that a few of the points you mentioned are sorted. However, I don't understand your sentence

"The x,y arguments in the mouse click event procedure are in pixels, and the coordinates of the chart (.InsideLeft, etc.) are in pixels. This is a major source of error"

If they are both in the same units, how can that be a source of error?

Yes, my Ycoord formula should use ChTop and ChTall, that was a silly copy/paste mistake.

Even including ChartArea.Left and .Top in my ChLeft and ChTop variables, the coordinates aren't correct.

Heeeeeeeeeeelp!

Corrected code below (still not giving the right coordinates)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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

With ActiveChart
With .PlotArea
ChWide = .InsideWidth
ChLeft = .InsideLeft
ChTop = .InsideTop
ChTall = .InsideHeight
End With

With .Axes(xlCategory)
xStart = .MinimumScale
xEnd = .MaximumScale
End With

With .Axes(xlValue)
yStart = .MinimumScale
yEnd = .MaximumScale
End With
End With

Xcoord = (((x - ChLeft) / ChWide)) * (xEnd - xStart)
Ycoord = (1 - ((y - ChTop) / ChTall)) * (yEnd - yStart)

MsgBox ("X=" & Xcoord & " , Y=" & Ycoord)

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
Upvote 0
DOH!! The chart dimensions are in POINTS! not PIXELS! I've corrected my original post, but not in time to prevent this misunderstanding. You need to convert the pixels to points. On my machine with my Windows settings, the conversion factor is 3/4, but you may need to use a more rigorous approach (API calls) to get the proper conversion.

More refinements:

ChLeft = PlotArea.InsideLeft + ChartArea.Left
ChTop = PlotArea.InsideTop + ChartArea.Top

Off to the dentist....
 
Upvote 0
Jon,

Thanks a lot, yes, it works now, I have the right coordinates. I used 0.75 as well and it seems to match, not sure how to exactly determine that coefficient but I am happy with that for now.

The problem is that even having the right coordinates it's turning out to be more difficult than I thought the process of actually zooming in. I want to draw a zooming rectangle, but when I click I move the plot area. I tried with application.interactive=false but it doesn't give me good vibes. Any ideas?
 
Upvote 0
Yes, there are still tons of details. You have to prevent selection of chart elements. I used something like:

Code:
Private Sub Chart_Select(ByVal ElementID As Long, _
        ByVal Arg1 As Long, ByVal Arg2 As Long)

    ActiveChart.Deselect

End Sub

I tried this with an AutoShape rectangle. I could make the rectangle start and end where I wanted it, but I was dissatisfied by the excessive flashing which occurred. Instead I used an XY series in the chart which had coordinates starting at the mouse_down and ending at the mouse_up.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top