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

ID: Q111972

The information in this article applies to:

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)")

The following values are acceptable for the arguments for this method


   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

For example, to set the minimum scale of the X axis to 5, the maximum scale to 20, and reverse the plot order of the axis, you would use this command:


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

Note that the above macro will work only if the chart is created as a separate chart sheet (that is, it will not work with a chart embedded on a worksheet).

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.

MORE INFORMATION

REFERENCES

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

ExecuteExcel4Macro

Additional query words:

Keywords : kbprg
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type :