Programming Excel with VBA and .NET

Change Size and Position of Objects

The following objects have Left, Top, Height, and Width properties that control their size and position:

Application	Axis	AxisTitle
ChartArea	ChartObject	ChartObjects
ChartTitle	DataLabel	DisplayUnitLabel
Legend	LegendEntry	LegendKey
MS Form controls	OLEObject	OLEObjects
PlotArea	Range	Shape
ShapeRange	Window

Excel measures objects in points . A point is a typographical measure equal to ¹/₇₂nd of an inch, but since the size and resolution of monitors varies, these units aren’t useful as an absolute measure. Instead, they are used to size and position objects relative to one another.

For example, the following code resizes Excel to half of the screen height and width and centers the window onscreen:

    Sub ResizeExcel( )
        Dim maxHeight As Double, maxWidth As Double
        ' Maximize window to get the full height/width
        Application.WindowState = xlMaximized
        maxHeight = Application.Height
        maxWidth = Application.Width
        ' Set the window style back to normal.
        Application.WindowState = xlNormal
        ' Resize the application window.
        Application.Height = maxHeight / 2
        Application.Width = maxWidth / 2
        ' Reposition the application window
        Application.Top = maxHeight / 2 - Application.Height / 2
        Application.Left = maxWidth / 2 - Application.Width / 2
    End Sub

In addition, most of the preceding objects also have a Visible property that you can use to hide or show the object. The Visible property is mainly useful for ...