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
 From the File menu, choose New to start a new workbook. From the Insert menu, choose Macro, and then choose Module. Type the four functions (PALeft, PATop, PAWidth, PAHeight) as shown above on the module sheet.  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   Click the sheet tab for Sheet1, and enter the following in cells A1:A5: A1:    5 A2:    8 A3:    9 A4:    1 A5:    2  Select cells A1:A5, and press the F11 key to create a chart as a new sheet. From the Tools menu, choose Macro. Select the macro DrawHorizLine and choose the Run button. 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.</li></ol>

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