ScaleType error

  • Thread starter Thread starter jcdecker
  • Start date Start date
J

jcdecker

I have some VBA code that works fine in Excel 2003, but throws an error in
Excel 2007. Here is the part of the code that fails:

With ActiveChart.Axes(xlCategory, xlSecondary)
.MinimumScale = X2Min
.MaximumScale = X2Max
.MinorUnitIsAuto = True
.MajorUnit = BinSize
.Crosses = xlMaximum
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

All of these lines work except for .ScaleType = xlLinear. When that line is
reached, the error: "Method ‘ScaleType’ of object ‘Axis’ failed" pops up. I
tried changing it to .ScaleType = xlScaleLinear, but that errors out also.

Does anybody have any ideas? This is pretty frustrating...
 
I've run across this in 2007 and reported it, but nobody seemed impressed
enough to want to fix it.

- Jon
 
i can't replicate this error - if i change the chart type, then the error is
raised in both versions.
 
Maybe I found the problem...

The Excel Help file says that this setting is for the Value Axis only. I
found other references that say the Category Axis can only be set to
Logarithmic on an XY Scatter Plot chart. When I try to manually change the
scale type, it doesn't even show up as an option. This leads me to believe
that, since it's not modifiable, just accessing the property could cause an
error.

Does that make sense???

jcdecker
 
I think I have this figured out...

The ScaleType documentation says that it is only for the Value Axis. The
Category Axis can only be set to Linear or Logarithmic on XY Scatter Plots.
So, since I am not doing the Scatter Plot, the ScaleType for the Category
Axis is not valid, and that is why it throws the error.

Does that sound like a plausible reason for this error occurring?

jcdecker
 
Here's what happens in Excel 2007 SP2.

In any chart type, if the Y axis is linear, the Y axis ScaleType is
xlScaleLinear.
In any chart type, if the Y axis is logarithmic, the Y axis ScaleType is
xlScaleLogarithmic.

In an XY chart, whether the X axis is linear or logarithmic, the X axis
ScaleType is xlScaleLinear.
In a chart with a category X axis, the ScaleType cannot be read, because
Excel throws an error.

In Excel 2003, every linear value axis (X or Y) has ScaleType of
xlScaleLinear. Every logarithmic value axis (X or Y) has ScaleType
xlScaleLogarithmic. Any category X axis throws the error.

So yeah, probably the coder who handled this in the chart redesign that went
into 2007 probably reasoned that only a value can be logarithmic, so he only
coded this property correctly for Y axes, not for an X value axis. It's a
plausible excuse, but not really a good reason, for this behavior.

- Jon
-------
Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009-06-ACNJ/AdvExcelConf200906ACNJ.html

Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______
 
Back
Top