How to speed up response to scaling axes with code?

[professorplum101]

I am programming a chart building worksheet with vb, and I must scale
the axes based on a value within my spreadsheet. Right now, I am
using the following commands:

With ActiveChart.Axes(xlValue, xlPrimary)

.MinimumScale = Worksheets("Aux").Range("M15")
.MaximumScale = Worksheets("Aux").Range("M14")
.MajorUnitIsAuto = True
.MinorUnitIsAuto = True

End With

and doing this for each of the Primary, Secondary, and Category axes.
However, with 32,000 data points and 8 to 16 series, this takes
forever. Aside from just using a splash screen to help the user pass
the time, does anyone have any suggestions on how to speed this up or
how to do this a better way? Thanks!

 

[Jon Peltier]

This won't help so much with Excel 2007, but in any Excel version, it should
improve matters to construct your code like this:

Application.ScreenUpdating = False
With ActiveChart.Axes(xlValue, xlPrimary)

.MinimumScale = Worksheets("Aux").Range("M15")
.MaximumScale = Worksheets("Aux").Range("M14")
.MajorUnitIsAuto = True
.MinorUnitIsAuto = True

End With

Application.ScreenUpdating = True

- Jon

 

[David G]

Right now, I am
using the following commands:

With ActiveChart.Axes(xlValue, xlPrimary)

.MinimumScale = Worksheets("Aux").Range("M15")
.MaximumScale = Worksheets("Aux").Range("M14")
.MajorUnitIsAuto = True
.MinorUnitIsAuto = True

End With

and doing this for each of the Primary, Secondary, and Category axes.

Are you sure that scaling the axis is indeed the issue here, rather
than the amount of data that excel has to lay-out?
Maybe scaling the axes before setting the data for the chart would
help improve the performance, if scaling is indeed the issue.

David