Microsoft KB Archive/118851

Microsoft Knowledge Base

XL: Visual Basic Macros to Retrieve Dimensions of the Plot Area

Last reviewed: March 27, 1997
Article ID: Q118851

5.00 5.00c 7.00 7.00a | 5.00 5.00a

WINDOWS               | MACINTOSH

The information in this article applies to:

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

You can use the following custom functions to return the dimensions of the actual PlotArea.

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:

Function PALeft() As Integer

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

End Function

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

Function PATop() As Integer

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

End Function

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

Function PAWidth() As Integer

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

End Function

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

Function PAHeight() As Integer

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

End Function

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

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.

A horizontal line will be drawn on the chart and cross at the value 5.5 on the y axis.

KBCategory: KBSubcategory: Additional reference words: 5.00 7.00 Version : 5.00 5.00c 7.00 7.00a | 5.00 5.0 Platform : MACINTOSH WINDOWS THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: March 27, 1997 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.