ScaleType error

[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...

 

[Jon Peltier]

I've run across this in 2007 and reported it, but nobody seemed impressed
enough to want to fix it.

- Jon

 

[Patrick Molloy]

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

 

[jcdecker]

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

 

[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

 

[Jon Peltier]

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/
_______

 

[Michael Virostko]

Hello,

I know this is a old thread, but I am hoping someone could help me. My company will not let me reach Microsoft and I have been looking for weeks for a solution to this problem.

I have Excel 2016 and I do a lot of graphical plotting that I send to customers so I need the graphs to be consistent and look good. The data is plotted using XY scatter graphs (I am an engineer). I have not had any issues with re-formatting the chart if there are just primary axes. The X axis (Microsoft calls category) can be either a linear or logarithmic scale.

I now am adding a second vertical axis (Value, Secondary). When this axis added I can no longer determine what the ScaleType is. The ScaleType is <Application-defined or object-defined error> instead of xlScaleLinear or xlScaleLogarithmic.

As I step through the macro and and I watch the object ActiveChart.Axes(xlCategory, xlPrimary).ScaleType I can watch this object become undefined.

1) I assume Microsoft does care to fix this issue.
2) Any ideas on how to program around this?

Thanks in advance for you help.

Michael J Virostko

 

[Michael Virostko]

Hello All,

Since this bug has been around so long, I wrote this function to solve the issue, since Microsoft never will.

Public Function ScaleType_Category() As Variant
'
'  Michael J. Virostko  2016-11-23
'
'  Microsoft Excel has a internal bug that has been around for a while.
'
'  https://www.office-forums.com/threads/scaletype-error.2022608/#post-7430843
'
'  If there is a secondary vertical (Value) axis, then the  primary (Catergory) axis object
'   ActiveChart.Axes(xlCategory, xlPrimary).ScaleType  is <Application-defined or object-defined error>.
'  The values should be either xlScaleLinear or xlScaleLogarithmic.
'  If there is only one vertical axes, you get the correct value.
'  No solution via the web was found, so I wrote this routine to decontruct and recontrust the graph.
'
Dim NSeries As Integer
Dim Sloop As Long
Dim SeriesGroup() As Variant
Dim Y2Axis(1 To 7) As Variant
Dim TestAxis As Boolean
'
'   Determine the number of series
'
  NSeries = ActiveChart.SeriesCollection.Count
  ReDim SeriesGroup(1 To NSeries)
'
'   Set parameter to determine if there is a secondary axis
  TestAxis = False
'
'   Loop through the series to determine if there is a secondary axis
'   If so, then
'    If first series on secondary axis save key parameters for the axis
'       in the array Y2Axis
'    Reset Series to the primary axis
'
  For Sloop = 1 To NSeries
    SeriesGroup(Sloop) = ActiveChart.SeriesCollection(Sloop).AxisGroup
    If SeriesGroup(Sloop) = xlSecondary Then
      If Not TestAxis Then
        With ActiveChart.Axes(xlValue, xlSecondary)
          Y2Axis(1) = .MinimumScale
          Y2Axis(2) = .MaximumScale
          Y2Axis(3) = .MajorUnit
          Y2Axis(4) = .MinorUnit
          Y2Axis(5) = .CrossesAt
          Y2Axis(6) = .ScaleType
          Y2Axis(7) = .TickLabels.NumberFormat
        End With
      End If
      TestAxis = True
      ActiveChart.SeriesCollection(Sloop).AxisGroup = xlPrimary
    End If
  Next Sloop
'
'  Now the chart only has one category and one value axis
'
  ScaleType_Category = ActiveChart.Axes(xlCategory, xlPrimary).ScaleType
'
'   Now recontruct secondary axis
'
  If TestAxis Then
    For Sloop = 1 To NSeries
      If SeriesGroup(Sloop) = xlSecondary Then ActiveChart.SeriesCollection(Sloop).AxisGroup = SeriesGroup(Sloop)
    Next Sloop
    With ActiveChart.Axes(xlValue, xlSecondary)
      .MinimumScale = Y2Axis(1)
      .MaximumScale = Y2Axis(2)
      .MajorUnit = Y2Axis(3)
      .MinorUnit = Y2Axis(4)
      .CrossesAt = Y2Axis(5)
      .ScaleType = Y2Axis(6)
      .TickLabels.NumberFormat = Y2Axis(7)
    End With
  End If

End Function