Finding suitable graph sizes

[Aelyn]

Hi all,

I'm working on a program that sorts data then produces graphs of the data.

I want the graphs to have "nice" upper and lower bounds (multiples of 100) and for their to be somewhere between forty and seventy-five values on each graph.

I've been wracking my brain for a couple days now on the most efficient way to write a macro to find good upper and lower bounds, and while I've had a couple of ideas, they were all very buggy and basically useless.

Can anyone suggest a process I could use to find good values?

Let me know if you'd like an example, I can post a current sheet of data with an explanation of how I want it to work.

Thanks for your time.

[mangesh_yadav]

right-click on the tab displaying the chart-name and click on view code. In the VBE that opens, enter the following code:

Private Sub Chart_Activate()

ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue) ' y-axis
.MinimumScale = 0
.MaximumScale = 8
.MajorUnit = 1
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory) ' x-axis
.MinimumScale = 0
.MaximumScale = 6
.MajorUnit = 1
End With

End Sub

Replace the numbers above 0, 8, 1 through some formula to determine your min, max range, and the intermediate levels for y-axis. Same for x-axis.


Mangesh

[Aelyn]

*snip*

Thanks for your help, but I'm afraid I may have misexplained my problem.

Thing is, I have roughly 250-300 data to display on the graphs. What I want to do is figure out a program to find out what upper and lower bounds would be good for the graphs such that both bounds are multiples of 100 and that there are at least 40 and at most 75 data represented on every graph.

Until now, this process has always been done entirely by hand, and I can't help but feel I could speed the process up a lot if I could write a macro to create all the graphs for us, with suitable bounds.

I appreciate your help, and apologise for wasting your time by misexplaining my problem.

[mangesh_yadav]

maybe you could give an example of your data and how you would want the graph. Basically what I suggested was by automatically fix the graphs upper and lower bound and the interval through the code I gave earlier. You just have to replace the numbers in that with some formula which would fix the bounds. But if you need the formulas for the bounds, then maybe you could give the sample data.

Mangesh

[Aelyn]

OK, here's an example of the data I'm using.

The values chosen for this sheet were to produce four graphs, one with bounds 1400+, one with bounds 1100-1400, one with bounds 800-1100 and the last with bounds 800-.

Thing is, every sheet could have anywhere from three to six graphs, and the values can change quite radically.

Thanks again for your attention.

[Aelyn]

OK, I have the macro working now. For all those interested:

Please Login or Register  to view this content.

It took me a while, but I finally got it working.