Microsoft Knowledge Base

WINDOWS               | MACINTOSH

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, versions 7.0, 7.0a

SUMMARY

In Microsoft Excel, when you attempt to retrieve dimensions for the plot area of a chart using a Visual Basic macro with the Left, Top, Width, or Height properties for the PlotArea object, the returned dimensions describe a rectangular area that encompasses the plot area AND the axis labels.

This article provides examples of custom functions you can create and then use in place of the Left, Top, Width, and Height properties for the PlotArea. In addition, it provides a macro example that demonstrates how you can use these functions to draw a horizontal line at a specified position on the y axis.

MORE INFORMATION

Rather than use the Left, Top, Width, and Height properties to retrieve the dimensions for the PlotArea, you can use the Microsoft Excel version 4.0 macro command GET.CHART.ITEM. The GET.CHART.ITEM command will return the dimensions of the PlotArea rather than the dimensions of the rectangular area that encompasses it.

When you use the GET.CHART.ITEM commands to return dimensions for chart items to use with other Visual Basic commands, keep the following in mind:

• The vertical coordinates returned by GET.CHART.ITEM DECREASE from the top to the bottom of the chart area; however, the vertical coordinates returned by the Visual Basic properties Left and Top INCREASE from the top to the bottom of the chart area.
• The smallest coordinate that can be returned by GET.CHART.ITEM is 1; however, the smallest coordinate that can be returned by the Visual Basic properties Left and Top is 0.

Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

Sample Custom Functions

'The following function returns a value for the LEFT coordinate
'of the plot area of the ActiveChart:

   PALeft = ExecuteExcel4Macro("GET.CHART.ITEM(1,1,""plot"")") - 1

'The following function returns a value for the TOP coordinate
'of the plot area of the ActiveChart:

    Dim CAHeight As Integer                   'Chart area height
    CAHeight = ExecuteExcel4Macro("GET.CHART.ITEM(2,1,""chart"")")
    PATop = CAHeight - _
          ExecuteExcel4Macro("GET.CHART.ITEM(2,1,""plot"")")

'The following function returns a value for the WIDTH of the plot
'area of the ActiveChart:

    PAWidth = ExecuteExcel4Macro( _
         "GET.CHART.ITEM(1,3,""plot"") - GET.CHART.ITEM(1,1,""plot"")+1")

'The following function returns a value for the HEIGHT coordinate of
'the plot area of the ActiveChart:

   PAHeight = ExecuteExcel4Macro( _
      "GET.CHART.ITEM(2,1,""plot"") - GET.CHART.ITEM(2,7,""plot"")+1")

' ----------------------------------------------------------------------

To draw a horizontal line at a value on the y axis

1. From the File menu, choose New to start a new workbook.

2. From the Insert menu, choose Macro, and then choose Module.

3. Type the four functions (PALeft, PATop, PAWidth, PAHeight) as shown above on the module sheet.

4. Enter the following code on the module sheet:

         Sub DrawHorizLine()
             ynum = Application.InputBox("Enter a value on the y axis:", , _
                 , , , , , 1)
             If ynum = False Then Exit Sub
             With ActiveChart.Axes(xlValue)
                 y = ((.MaximumScale - ynum) * (PAHeight - 1) / _
                     (.MaximumScale - .MinimumScale)) + PATop
             End With
             ActiveChart.Lines.Add PALeft, y, PALeft + PAWidth - 1, y
         End Sub
   
   

5. Click the sheet tab for Sheet1, and enter the following in cells A1:A5:

         A1:     5
         A2:     8
         A3:     9
         A4:     1
         A5:     2
   
   

6. Select cells A1:A5, and press the F11 key to create a chart as a new sheet.

7. From the Tools menu, choose Macro. Select the macro DrawHorizLine and choose the Run button.

8. The macro displays an input box prompting you for the y value at which you wish to draw a horizontal line. Enter the value 5.5 and choose OK.