different value for setting with breakpoint vs regular running code

daddy-o

unread,

Mar 2, 2011, 6:35:37 AM3/2/11

to

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!

Vacuum Sealed

unread,

Mar 2, 2011, 7:35:43 AM3/2/11

to

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

daddy-o

unread,

Mar 2, 2011, 11:01:43 AM3/2/11

to

Thanks Mick. The situation is confusing at best - thanks for taking a stab at it.

I think I understand what you're getting at. Essentially, if conditions are one way, change the secondary axis, if they are another way, change the primary axis. Not a bad idea. The issue is that I have no way of testing or recognizing what condition I am in.

When it comes down to it, I'm grabbing the min of the primary axis, and setting the min of the secondary axis to that same value. Somehow, however, on certain users' machines, when the code is finished running, they get a chart with axes with different min's.

So, I stepped into the code to investigate why. I came upon the line of code which "should" actually set the min's equal to each other. I discovered that if I put a breakpoint anywhere before this line of code, and then press F5 (having done nothing else), I get axes with the same min value. If there is no breakpoint, or it comes after the said line of code, then I get axes with different min's.

I went even further, and verified that the said line of code actually picks up a different value for the min of the primary axis depending on where/if there is a breakpoint.

I don't know if that was any clearer. Any ideas anyone?

Tyler

Clif McIrvin

unread,

Mar 2, 2011, 2:25:00 PM3/2/11

to

"daddy-o" <jtyler...@gmail.com> wrote in message
news:7d82464f-217b-451d...@glegroupsg2000goo.googlegroups.com...

Tyler

------

Nice job of describing your situation. A guess:

Is the axis min stipulated, or set to auto?

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Vacuum Sealed

unread,

Mar 2, 2011, 4:59:52 PM3/2/11

to

Tyler

you could have a user response set the path as to what the user will see and
how the code reacts....

ie
Dim MyParameter as Integer

Your Parameter message could =("Select Axis to Set", [Min] or [Max])

If MyParameter = [Min] Then
Path_1
Else
Path_2
End IF

Something to consider
Cheers
Mick

Martin Brown

unread,

Mar 7, 2011, 8:40:11 AM3/7/11

to

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