[Excel VBA] How to Freely Change the Position, Size, and Rotation of Existing Shapes

Introduction

When generating reports automatically with Excel VBA, there are many situations where layout automation is necessary, such as “moving this graph to a specific location” or “fitting a shape exactly to the size of certain cells.”

By using VBA, it is possible to accurately control the position, size, and even the rotation angle of existing shapes (shapes, charts, text boxes, etc.) on a sheet through code.

In this article, I will explain the basic properties for manipulating existing shapes and provide specific VBA code for two practical patterns.

---

Basic Properties for Manipulating Shapes

To change the appearance of a shape via VBA, you primarily work with the following five properties:

• Top: The distance from the top edge of the worksheet to the top edge of the shape.
• Left: The distance from the left edge of the worksheet to the left edge of the shape.
• Width: The width of the shape.
• Height: The height of the shape.
• Rotation: The rotation angle of the shape (clockwise).

All of these properties are specified in points (1 point ≈ 0.35 mm).

---

Preparation: Naming the Shape

To ensure you are manipulating the correct shape in VBA, it is highly recommended to give the shape a unique name beforehand.

1. Select the shape you want to operate on.
2. Click the “Shape Format” tab displayed in the ribbon.
3. Click “Selection Pane” in the “Selection” group to open the Selection window on the right side of the screen.
4. Select the target shape from the list, double-click the name, and edit it (e.g., “SampleShape”).

---

Practice 1: Specifying Position and Size Directly by Value

The most fundamental method is to directly assign numeric values to properties such as Top and Left. This allows you to place shapes accurately at the millimeter level.

VBA Code Example

The following code moves a shape named “SampleShape” to a specific location, changes its size, and rotates it slightly.

' Move and resize a shape by direct values
Sub MoveShapeByValue()

    ' Manipulate the shape named "SampleShape"
    With ActiveSheet.Shapes("SampleShape")
        ' Set the position from the top to 100 points
        .Top = 100
        
        ' Set the position from the left to 50 points
        .Left = 50
        
        ' Set the width to 250 points
        .Width = 250
        
        ' Set the height to 150 points
        .Height = 150
        
        ' Rotate by 15 degrees
        .Rotation = 15
    End With

End Sub

Code Explanation

By using the With ActiveSheet.Shapes("SampleShape") ... End With syntax, you save the effort of writing the long reference multiple times, making the code cleaner. The .Top and .Left inside the block all refer to the properties of “SampleShape.”

---

Practice 2: Specifying Position and Size to Match a Cell Range

If you want to perfectly fit a shape to a specific cell range, using the properties of a Range object is extremely convenient.

VBA Code Example

The following code moves and resizes “SampleShape” so that it fits perfectly within the range of cells C3 to F8.

' Align a shape to a specific cell range
Sub AlignShapeToRange()

    ' Declare variable
    Dim targetArea As Range
    
    ' Set the reference cell range
    Set targetArea = ActiveSheet.Range("C3:F8")
    
    ' Manipulate the shape named "SampleShape"
    With ActiveSheet.Shapes("SampleShape")
        ' Align the top of the shape to the top of the cell range
        .Top = targetArea.Top
        
        ' Align the left of the shape to the left of the cell range
        .Left = targetArea.Left
        
        ' Align the width of the shape to the width of the cell range
        .Width = targetArea.Width
        
        ' Align the height of the shape to the height of the cell range
        .Height = targetArea.Height
        
        ' Reset rotation (return to 0 degrees)
        .Rotation = 0
    End With
    
    ' Release the object variable
    Set targetArea = Nothing

End Sub

Code Explanation

The reference cell range is set in a Range type variable called targetArea. Since Range objects also have properties like .Top, .Left, .Width, and .Height just like shapes, you can easily synchronize the size and position by simply assigning those values to the shape’s properties.

---

Summary

In this article, I explained how to control the position, size, and rotation of existing shapes using VBA.

• Basic Properties: Use Top, Left, Width, Height, and Rotation.
• Reliable Operation: Give shapes unique names and specify them with Shapes("ShapeName").
• Value Specification: Ideal for fixed layouts, allowing precise placement in points.
• Range Specification: Use Range object properties to easily perform dynamic layout adjustments.

Mastering these techniques allows you to automate the generation of visually appealing reports and create advanced tools where the layout changes according to user operations.