ScaleType error

Discussion in 'Excel Programming' started by jcdecker, May 15, 2009.

  1. jcdecker

    jcdecker Guest

    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...
     
    jcdecker, May 15, 2009
    #1
    1. Advertisements

  2. jcdecker

    Jon Peltier Guest

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

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    http://PeltierTech.com/WordPress/
    _______


    "jcdecker" <> wrote in message
    news:...
    >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, May 15, 2009
    #2
    1. Advertisements

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

    "jcdecker" <> wrote in message
    news:...
    > 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...
     
    Patrick Molloy, May 15, 2009
    #3
  4. jcdecker

    jcdecker Guest

    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

    "Jon Peltier" wrote:

    > I've run across this in 2007 and reported it, but nobody seemed impressed
    > enough to want to fix it.
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services, Inc.
    > http://PeltierTech.com/WordPress/
    > _______
    >
    >
    > "jcdecker" <> wrote in message
    > news:...
    > >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...

    >
    >
    >
     
    jcdecker, Jun 4, 2009
    #4
  5. jcdecker

    jcdecker Guest

    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" wrote:

    > I've run across this in 2007 and reported it, but nobody seemed impressed
    > enough to want to fix it.
    >
    > - Jon
    > -------
    > Jon Peltier, Microsoft Excel MVP
    > Peltier Technical Services, Inc.
    > http://PeltierTech.com/WordPress/
    > _______
    >
    >
    > "jcdecker" <> wrote in message
    > news:...
    > >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...

    >
    >
    >
     
    jcdecker, Jun 4, 2009
    #5
  6. jcdecker

    Jon Peltier Guest

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


    "jcdecker" <> wrote in message
    news:...
    >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" wrote:
    >
    >> I've run across this in 2007 and reported it, but nobody seemed impressed
    >> enough to want to fix it.
    >>
    >> - Jon
    >> -------
    >> Jon Peltier, Microsoft Excel MVP
    >> Peltier Technical Services, Inc.
    >> http://PeltierTech.com/WordPress/
    >> _______
    >>
    >>
    >> "jcdecker" <> wrote in message
    >> news:...
    >> >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, Jun 4, 2009
    #6
  7. jcdecker

    Michael Virostko

    Joined:
    Nov 22, 2016
    Messages:
    2
    Likes Received:
    1
    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, Nov 22, 2016
    #7
  8. jcdecker

    Michael Virostko

    Joined:
    Nov 22, 2016
    Messages:
    2
    Likes Received:
    1
    Hello All,

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

    Code:
    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
     
    Michael Virostko, Nov 23, 2016
    #8
    Becky likes this.
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. wellie
    Replies:
    1
    Views:
    131
    Patrick Molloy
    Jul 10, 2003
  2. Daniel Bonallack

    Error Handler not handling error...

    Daniel Bonallack, Jul 21, 2003, in forum: Excel Programming
    Replies:
    2
    Views:
    98
    BrianB
    Jul 22, 2003
  3. John
    Replies:
    3
    Views:
    181
    John.
    Sep 4, 2003
  4. ibeetb

    Device I/O Error (Error 57)

    ibeetb, Sep 23, 2003, in forum: Excel Programming
    Replies:
    0
    Views:
    106
    ibeetb
    Sep 23, 2003
  5. Neo
    Replies:
    0
    Views:
    153
  6. David
    Replies:
    9
    Views:
    407
    Steve D
    Feb 16, 2006
  7. Replies:
    3
    Views:
    275
  8. SixSigmaGuy

    Problem with ScaleType propert for Axis object

    SixSigmaGuy, Aug 29, 2008, in forum: Excel Programming
    Replies:
    1
    Views:
    123
    AD@Alog
    Sep 3, 2008
Loading...