How to determine x/y coordinates of data points on Excel chart?

[jlgenualdi]

I spent a good portion of my work day today trying to figure this out and am still stumped -

I need to write a VBA macro to overlay multiple transparent rectangles on a chart to highlight different sections of data based on date ranges that span across the x-axis.

For instance, a chart may span from April 2011 thru April 2012. The users of the macro can specify a few sets of date ranges, such as:

01-Jun-2011 thru 15-Jul-2011;
15-Jul-2011 thru 15-Aug-2011;
01-Sep-2011 thru 30-Sep-2011

When they click the macro button, three rectangles should be drawn to capture the data within these 3 ranges. The top and bottom coordinates will always be the same and can be hard-coded. The part I am struggling with is how to determine the horizontal (x) coordinates to plot for the start and end of the rectangles. When hardcoded, one of the rectangles is plotted with the line of code below -

ActiveChart.Shapes.AddShape(msoShapeRectangle, 10, 27, 97, 200).Select

I need to determine what values should be plugged in where I have 10 and 97 above dynamically based on the data source values that match up with the selected date range.

I was looking at the Chart.GetChartElement method, but that seems to perform sort of the reverse of what I need to do here.

Any help would be greatly appreciated!

 

[shg]

Why not instead use additional data series?

 

[Steve=True]

Hello and welcome to the forums.

Jon posted something that may help you.

http://peltiertech.com/WordPress/excel-interpolation-formulas/

Good luck!

 

[jlgenualdi]

I don't know of a way to do this with an additional data series. It's a line chart, and don't see any way to overlay custom-defined rectangles on that.

I also looked at Jon Peltier's post as suggested, but wasn't quite what I'm looking for. BTW, he seems to be quite the guru! Does anybody know if he accepts questions directly?

 

[gardnertoo]

I think what's being suggested (and if not, then I'M suggesting it) is that instead of overlaying a rectangle shape, you create a series to locate the four corners of the rectangle and plot that as another series in the chart, formatting the series with either automatic line or custom line to connect the corners and form the rectangle. This series will be a FIVE point series, with the fifth point being a repeat of the first point, allowing you to draw the final line to connect the fourth dot back to the first dot. This technique works because you can mix line chart series and XY Scatter series in the same chart, and you'll be making the new series an XY Scatter in the chart already containing the line series.

 

[Jon Peltier]

You can determine the coordinates of the plot area using .PlotArea.InsideLeft and .PlotArea.InsideWidth. You can determine the endpoints of the horizontal axis using .Axes(xlCategory).MinimumScale and .MaximumScale. A little algebra tells you where the left and right edges of your rectangle should be (the starting and ending dates of the region to highlight).

 

[jlgenualdi]

Hi Jon,

It looks like the .PlotArea.InsideLeft and .PlotArea.InsideWidth and some algebra are going to work for me. It took a little experimenting, but I think I can develop what my users requested thanks to your tips.

Thank you for your assistance!


Best regards,
Jeff Genualdi