Locking the origin on a scatter plot

I need to make a chart that displays a phasor diagram. I have a column of cells containing the angles of the phasors. I made a scatterplot that has a separate series for each phasor and draws a line from (0,0) to wherever that phasor needs to be depending on the angle.

Here's the catch, the angles are continuously updating and I need the chart to update as well. Right now, every time it updates, the origin moves around within the chart and is difficult to look at. Is there away to keep the origin locked in the center of the chart while the lines rotate around this origin?

Thanks,

Eric

Excel doesn't have a built in algorithm for this. Excel's built in utility only allows you to fix the endpoints of the axes.

How much variability will there be in (x,y)? If there is limited variability, then it might be easiest to fix the endpoints of your axes now, and then the origin won't move around.

If there is enough variability that you need the endpoints to be flexible, your going to have to write your own algorithm (in VBA or other) that will look up the max and min for x and y, then determine suitable endpoints for the axes and then apply them to the axes, with the idea that the origin will remain in a fixed location. When I've done this, I've used worksheet functions in an out of the way part of the spreadsheet to calculate the desired axis limits, then a chart_calculate event to automatically apply those limits each time the chart "calculates"

Sorry I took so long, I thought I would get an email when someone responded.

How can you write an event for a chart? It doesn't seem to show up in VBA.

Did you put "calculate event" into the VBA help search engine? That worked for me. In your case, I don't know if it will be preferable to have the calculate event tied to the chart or the source worksheet. Tying events to embedded charts introduces a couple of complications that you have to sort out to get them to work. I've always used stand alone chart sheets, so I'm not familiar with these issues.

For some reason, I don't see anything relevant when I search for calculate event in VBA. I also figured if I right-clicked on the chart in Design Mode, it would give me the option to Build Code, but it did not.

I know sometimes the VBA help search engine isn't that good at finding pertinent information.

I would start by opening the VBA editor (alt-F11). Then navigate in the project explorer to the worksheet where you can then put your worksheet_calculate event procedure.

That looks like the right place for me to put my code. But now, what code could I use that would set the max and mins for the axes? VB Help and Excel Help give nothing that would accomplish this in VB.

Once you determine what you are going to use as your algorithm for determining max and min values of the axis, you will apply them to the axis using the maximumscale and minimumscale properties of the axis object.

Please Login or Register  to view this content.

Search VBA help for axes collection, maximumscale property, and/or minimumscale property.

I don't get any results when I search maximumscale. Could we be using a different version of VBA and maybe my version does not have that function?

If you have 2002 or newer, I'm 99.9% certain that your version of VBA will support the maximumscale property of the axis object. If you have 5.0 through 2000, I would be less certain, but still surprised if it doesn't.

My VBA help search engine finds the maximumscale property just fine. Sometimes I've found that the easiest way to find something in VBA help is to start with the object or collection that I want to work with. Sometimes that's easier found using the help table of contents rather than the index or the answer wizard. So, in this case, I would go to the table of contents, objects, A, Axis object. Then under the help file title, there will be "see also, properties, methods," etc. You can then click on properties to get a drop down list of the applicable properties to the axis object, which will bring up the help page for that property.