XL5: Can't Get or Set Properties of X Axis in XY Scatter Chart

- Microsoft Excel for Windows, version 5.0

SYMPTOMS

In Microsoft Excel version 5.0, you may receive one of the following error messages when you attempt to set or return the x axis scale properties for an xy (scatter) chart:

   Run-time error '1005':
   "Unable to set the <property> property of the axis class"

   -or-

   Run-time error '1005':
   "Unable to get the <property> property of the axis class"

CAUSE

The following Visual Basic axis class properties for the x axis of an xy (scatter) chart may cause the above error messages:

   <object>.Axes(xlCategory).MinorUnit
   <object>.Axes(xlCategory).MajorUnit
   <object>.Axes(xlCategory).MinimumScale
   <object>.Axes(xlCategory).MaximumScale
   <object>.Axes(xlCategory).MinimumScaleIsAuto
   <object>.Axes(xlCategory).MaximumScaleIsAuto
   <object>.Axes(xlCategory).MinorUnitIsAuto
   <object>.Axes(xlCategory).MajorUnitIsAuto
   <object>.Axes(xlCategory).ScaleType

WORKAROUND

To set any of the above properties when the axis in question is the x axis in an (xy) scatter chart, use the Application.ExecuteExcel4Macro method with the Microsoft Excel 4.0 SCALE() macro command. Below is the syntax of the Visual Basic command:

   ' Enter the following line of code as a single, continuous line:
   Application.ExecuteExcel4Macro _
      ("SCALE(min_num,max_num,major,minor,cross,logarithmic,reverse,max)")

   Argument      value     equivalent to
   ---------------------------------------------------

   min_num       TRUE      MinimumScaleIsAuto = True
                 <value>   MinimumScale = <value>

   max_num       TRUE      MaximumScaleIsAuto = True
                 <value>   MaximumScale = <value>

   major         TRUE      MajorUnitIsAuto = True
                 <value>   MajorUnit = <value>

   minor         TRUE      MinorUnitIsAuto = True
                           MinorUnit = <value>

   cross         TRUE      Crosses = xlAutomatic
                              (if max = FALSE)
                 <value>   Crosses = xlCustom, CrossesAt = <value>
                              (if max = FALSE)

   logarithmic   TRUE      ScaleType = xlLogarithmic
                 FALSE     ScaleType = xlLinear

   reverse       TRUE      ReversePlotOrder = True
                 FALSE     ReversePlotOrder = False

   max           TRUE      Crosses = xlMaximum
                 FALSE     see "cross", above

'  This line selects the axis.
   ActiveSheet.Axes(xlCategory).Select
'  This line makes the changes.
   Application.ExecuteExcel4Macro ("scale(5,20,,,,,true)")

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows version 5.0c.

REFERENCES

For more information about ExecuteExcel4Macro choose the search button in Visual Basic Help and type:

   ExecuteExcel4Macro