I'm creating a chart with two data series using vba. One data series is on the primary axis and one is on the secondary axis. In order for the chart to look correct, the two axis need to have the same max/mins. I have written code that does just this. Below is a portion of code from a much larger routine.

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = ActiveChart.Axes(xlValue, xlPrimary).MinimumScale
.MaximumScale = ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
End With

In my testing I'm using a chart with primary axis max=60, min=-30. The code always works for the maximum value. It also works perfectly for the min if I put a breakpoint in the code before "MinimumScale = ActiveChart....". However, if the code is called from Excel and runs freely (no breakpoints), or if the first breakpoint is after "MinimumScale = ActiveChart...", then VBA somehow concludes that the min=-40.

I'm finding this absolutely maddening. Does anyone know how to get Excel/VBA to work correctly?

Thanks!

Just a thought, and of course I maybe way off the mark if comprehending your
request.

Would a "Select Case" be of help, something along the lines like:

If Not ("MyCondition") Is Nothing Then

Select Case True

Case (MyCondition_1)

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = ActiveChart.Axes(xlValue,
xlPrimary).MinimumScale
.MaximumScale = ActiveChart.Axes(xlValue,
xlPrimary).MaximumScale
End With

Case (MyCondition_2)

With ActiveChart.Axes(xlValue, xlPrimary)
.MinimumScale = ActiveChart.Axes(xlValue,
xlSecondary).MinimumScale
.MaximumScale = ActiveChart.Axes(xlValue,
xlSecondary).MaximumScale
End With

End Select
End IF

HTH
Mick

On Mar 2, 11:35*am, daddy-o wrote:
I'm creating a chart with two data series using vba. *One data series is on the primary axis and one is on the secondary axis. *In order for the chart to look correct, the two axis need to have the same max/mins. *I have written code that does just this. *Below is a portion of code from a much larger routine.

With ActiveChart.Axes(xlValue, xlSecondary)
* * .MinimumScale = ActiveChart.Axes(xlValue, xlPrimary).MinimumScale
* * .MaximumScale = ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
End With

In my testing I'm using a chart with primary axis max=60, min=-30. *The code always works for the maximum value. *It also works perfectly for the min if I put a breakpoint in the code before "MinimumScale = ActiveChart...". *However, if the code is called from Excel and runs freely (no breakpoints), or if the first breakpoint is after "MinimumScale = ActiveChart...", then VBA somehow concludes that the min=-40.

I'm finding this absolutely maddening. *Does anyone know how to get Excel/VBA to work correctly?

Avoid using XL2007. Failing that add a delay after creating the chart
and before altering the Axes.

It is one of many race conditions in the XL2007 graphics and charting
code and can probably be kludged around by adding a judicious delay in
the right place. It probably explains why it runs with such glacial
slowness too since MS have doen the same fix internally in their own
code. If you try really hard with big datasets you can write VBA code
that attempts to modify the axes even before they have been
instantiated and fully initialised failing object not found. The debug
breakpoint suspends the main thread execution for long enough to allow
the Axis object to become correctly created and initialised. Whoever
wrote the XL2007 charts code should be terminated. Their attempt to
parallelise things to speed it up backfired big time.

You can also get another situation where chart dialogues invoked from
VBA return the original values immediately with a "done" flag but
remain active and allow the user to alter things whilst the program
remains blissfully unaware of it! These race conditions tends to be
worst on fast machines with multiple cores. I am now seeing new
"features" that only cause trouble on quad core and higher.

Regards,
Martin Brown