Plotting points using VBA

Hi everybody,

I have a series of coordinates in a range. I would like to plot them as points using VBA.

Is there a web link which gives tips in how to plot and edit a chart using VBA???

thanks

I don't know of any one specific web site, but you can look in the VBA object browser for the Chart and Series objects to get started.

You might also try to Google "Excel VBA chart".

What do you need to do that requires VBA? You certainly don't need VBA to plot a series of coordinates in a range.

I would suggest creating a chart using the desired ranges allowing axis options to be automatic. Then the chart will be automatically adjusted to reflect the data your VBA generates, without having to manipulate the chart with VBA.

I can illustrate better if you could post an example of the workbook you're building.

I suggest you change your data format to have two columns dedicated to the bolt coordinates, and two more dedicated to the squares of the bolt coordinates, instead of including them in columns that also contain other data. That greatly simplifies the plotting problem. The chart series can refer to the entire column and manage everything else automatically.

I will see if I can provide a revision, including an update to your VBA. However, your code has range references hard coded so it would need a lot of revisions. I have two strategies for managing this. One is to create named ranges in the spreadsheet and use those as anchor points for tables of data. For example, you could have X1 name the cell where the first X value will be placed. Then make all references to cells in the table relative to that anchor point.

Please Login or Register  to view this content.

Another strategy is to create a variable in the code to achieve the same thing

Please Login or Register  to view this content.

This allows you to change the table location and change at most one line of code. In the existing code there are maybe dozens of places that need to be changed.

I would also suggest turning off screen updating during calculation, which should speed things up a bit.

I tried running it, but I do not understand the engineering involved, so my input was probably nonsense. But I noted that your code is subject to divide by zero errors. You will want to identify where that could happen and whether there is legitimate input that can cause that result, or prevent bad input from causing it.

Here is an example of a suggested data layout. I discovered you can't plot more than 32K points so I can't select the entire column for a plot. For purposes of this example I specified a range of 100 rows, although there are only 18 data points. The updated layout is in "ICR - Results (2)" and the chart is in "Plot".

The update to the VBA is incomplete so this is just for looking at, not executing.

Glad to see I was able to help. I didn't think we were quite there yet--let me know if I can do something else.