Adding Horizontal and Vertical Lines (cross-hairs) to scatterplots/bubble charts

Posted by John Fava on June 27, 2001 12:29 PM

I received some terrific help recently from Scott regarding how to label xy scatterplots (thanks again, Scott!). I have now been looking for something to replace a very old (DOS) program that I use for placing horizontal and vertical reference lines on scatterplots. These lines would be placed in the "median" position, so that one-half of data points would be above/below and left/right of the lines. I can easily calculate the median that would be necessary for each line, but can't figure out how to "automate" the adding of the lines to the chart. As far as I can figure, the only way I can do this is to manually add line objects to the chart - now, try this with 100 charts! Does anyone have any suggestions on how to automate the addition of horizontal and vertical lines to a chart (based on the median for the specific data points present). Thanks!

John

Posted by Damon Ostrander on June 27, 2001 2:27 PM

Hi John,

It is relatively easy to add lines to charts under VBA control the same way you would manually. Try recording a macro where you draw a horizontal and vertical line, and you will see what I mean. You will have to calculate the Top, Left, Height, and Width properties of the lines, and do some scaling based on the size of the chart. All graphic objects have Top, Left, Height and Width properties, so it is easy to determine where a chart is that is embedded on a worksheet, and its size. Of course, you will have to calculate the chart coordinates where the lines should be drawn based on x and y median value calculations.

The macro could either operate on just the selected chart, in which case you would have to run it 100 times, or, if you really want to add the lines to all charts, simply loop through all the chart objects in your workbook and run the code that generates the lines for each one. If you only want to add the lines to certain types of charts, you would want to add some code to check the chart type before adding them.

I realize that this is just a crude outline, but at least you know that it is not difficult--just a bit of a learning curve if you haven't done it before.

Happy charting.

Damon

Posted by John on June 28, 2001 5:52 AM

Thanks, Damon. I have no experience with macros, so the learning curve will be a steep one! However, I can see the benefits of macros in this situation - heck, some of the best stuff in here was developed using macros! Could you suggest some resources and hints to help me along (articles, other examples, etc.)? Thank you very much for your response.

John

Posted by Damon Ostrander on June 28, 2001 8:41 AM

John,

I wish I knew what book to recommend. There are so many available now, a little time spent at your local bookstore looking over the options might be helpful. When I learned this stuff, I dug it out for myself because not so many books were available, and because I had a lot of previous experience writing my own graphics routines and with researching the object models of objects within Excel.

If I can get the time I will send you an example of some code to draw a line across each chart in a workbook.

Damon