NeuroAgent

Excel VBA: Get Shape Dimensions for Formulas

Master Excel VBA to retrieve shape width and height for dynamic formulas. Create custom UDF functions that access shape properties and integrate them into your spreadsheets. Complete guide with practical examples.

#excel-automation#excel-formulas#excel-vba#shape-dimensions#spreadsheet-programming#udf-functions

How can I retrieve the width and height of a shape in Excel to use in formulas?

NeuroAgent

You can retrieve the width and height of a shape in Excel primarily through VBA programming, as Excel doesn’t provide built-in worksheet functions to access shape dimensions directly. The most common approach involves creating User Defined Functions (UDFs) in VBA that can then be used in worksheet formulas.

Contents

VBA Methods for Shape Dimensions

Excel provides several VBA properties and methods to access shape dimensions. The primary properties are:

Basic Shape Properties:

  • Shape.Width - Returns the width of the shape in points
  • Shape.Height - Returns the height of the shape in points

Example VBA code to get shape dimensions:

vba
Sub GetShapeDimensions()
    Dim sh As Shape
    Set sh = ActiveSheet.Shapes("Rectangle 1")
    
    Debug.Print "Width: " & sh.Width & " points"
    Debug.Print "Height: " & sh.Height & " points"
End Sub

Advanced Methods:

  • GetWidth(Unit As MsoMeasurementUnit) - Returns width in specified units
  • GetHeight(Unit As MsoMeasurementUnit) - Returns height in specified units

These methods allow you to specify units like points, centimeters, inches, or pixels for the measurement source.

Creating UDF Functions for Formula Use

To use shape dimensions in worksheet formulas, you need to create User Defined Functions in VBA. Here are the most common approaches:

Basic UDF Functions

vba
Function GetShapeWidth(shapeName As String) As Double
    On Error Resume Next
    GetShapeWidth = ActiveSheet.Shapes(shapeName).Width
    On Error GoTo 0
End Function

Function GetShapeHeight(shapeName As String) As Double
    On Error Resume Next
    GetShapeHeight = ActiveSheet.Shapes(shapeName).Height
    On Error GoTo 0
End Function

Usage in formulas:

  • =GetShapeWidth("Rectangle 1") - Returns width in points
  • =GetShapeHeight("Circle 1") - Returns height in points

Advanced UDF with Unit Conversion

vba
Function GetShapeSize(shapeName As String, dimension As String, Optional unit As String = "pt") As Double
    Dim sh As Shape
    On Error Resume Next
    Set sh = ActiveSheet.Shapes(shapeName)
    
    If sh Is Nothing Then
        GetShapeSize = 0
        Exit Function
    End If
    
    Select Case LCase(unit)
        Case "cm"
            If LCase(dimension) = "width" Then
                GetShapeSize = sh.Width / 28.3464567 ' Convert points to cm
            ElseIf LCase(dimension) = "height" Then
                GetShapeSize = sh.Height / 28.3464567 ' Convert points to cm
            End If
        Case "in"
            If LCase(dimension) = "width" Then
                GetShapeSize = sh.Width / 72 ' Convert points to inches
            ElseIf LCase(dimension) = "height" Then
                GetShapeSize = sh.Height / 72 ' Convert points to inches
            End If
        Case Else ' Default to points
            If LCase(dimension) = "width" Then
                GetShapeSize = sh.Width
            ElseIf LCase(dimension) = "height" Then
                GetShapeSize = sh.Height
            End If
    End Select
    On Error GoTo 0
End Function

Usage examples:

  • =GetShapeSize("Rectangle 1", "width") - Width in points
  • =GetShapeSize("Rectangle 1", "width", "cm") - Width in centimeters
  • =GetShapeSize("Rectangle 1", "height", "in") - Height in inches

Alternative Approaches

Using Cell References for Shape Position and Size

Some users have created workarounds by relating shape positions to cell positions:

vba
Function GetShapePositionInfo(shapeName As String) As String
    Dim sh As Shape
    On Error Resume Next
    Set sh = ActiveSheet.Shapes(shapeName)
    
    If sh Is Nothing Then
        GetShapePositionInfo = "Shape not found"
        Exit Function
    End If
    
    GetShapePositionInfo = "Width: " & sh.Width & " pt, Height: " & sh.Height & " pt" & _
                          " | Left: " & sh.Left & " pt, Top: " & sh.Top & " pt"
    On Error GoTo 0
End Function

Dynamic Shape Sizing Based on Cell Values

You can create shapes that dynamically size based on cell values:

vba
Sub SizeCircle(Name As String, Diameter)
    Dim xCenterX As Single
    Dim xCenterY As Single
    Dim xCircle As Shape
    Dim xDiameter As Single
    
    On Error GoTo ExitSub
    xDiameter = Diameter
    
    If xDiameter > 10 Then xDiameter = 10
    If xDiameter < 1 Then xDiameter = 1
    
    Set xCircle = ActiveSheet.Shapes(Name)
    With xCircle
        xCenterX = .Left + (.Width / 2)
        xCenterY = .Top + (.Height / 2)
        .Width = Application.CentimetersToPoints(xDiameter)
        .Height = Application.CentimetersToPoints(xDiameter)
        .Left = xCenterX - (.Width / 2)
        .Top = xCenterY - (.Height / 2)
    End With
    
ExitSub:
End Sub

Common Challenges and Solutions

Challenge 1: Shape to Cell Width Ratio Issues

Problem: Excel column widths don’t have a fixed ratio to shape widths. The relationship between shape width and cell column width fits a curve rather than a line source.

Solution: Use empirical testing to establish conversion factors for your specific Excel settings:

vba
Function PointsToColumnWidth(points As Double) As Double
    ' This is an approximation - you need to calibrate this for your system
    PointsToColumnWidth = points * 0.13 ' Example conversion factor
End Function

Challenge 2: Getting Precise Shape Dimensions

Problem: Users report issues with getting precise shape dimensions, especially when trying to set exact sizes like 12x12 points source.

Solution: Use the following approach to ensure precise sizing:

vba
Sub SetPreciseShapeSize()
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        sh.LockAspectRatio = msoFalse
        sh.Height = 12
        sh.Width = 12
        sh.Placement = xlMove
        sh.LockAspectRatio = msoTrue
    Next sh
End Sub

Challenge 3: Macro Security and Disabled Functionality

Problem: UDF functions may not work when macros are disabled source.

Solution:

  1. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm)
  2. Enable macros when opening the file
  3. Consider using Excel’s COM add-ins for alternative solutions

Practical Examples

Example 1: Creating a Dynamic Dashboard

vba
Function GetDashboardMetrics() As Variant
    Dim metrics(1 To 4) As Double
    Dim statusShape As Shape
    
    On Error Resume Next
    Set statusShape = ActiveSheet.Shapes("StatusIndicator")
    
    If Not statusShape Is Nothing Then
        metrics(1) = statusShape.Width ' Width in points
        metrics(2) = statusShape.Height ' Height in points
        metrics(3) = statusShape.Left / 72 ' Left position in inches
        metrics(4) = statusShape.Top / 72 ' Top position in inches
    End If
    
    GetDashboardMetrics = metrics
    On Error GoTo 0
End Function

Example 2: Shape Size Calculator

vba
Function CalculateShapeArea(shapeName As String) As Double
    Dim sh As Shape
    On Error Resume Next
    Set sh = ActiveSheet.Shapes(shapeName)
    
    If sh Is Nothing Then
        CalculateShapeArea = 0
        Exit Function
    End If
    
    CalculateShapeArea = sh.Width * sh.Height / 144 ' Convert to square inches
    On Error GoTo 0
End Function

Example 3: Conditional Formatting Based on Shape Size

vba
Sub CheckShapeSizes()
    Dim sh As Shape
    Dim outputRange As Range
    
    Set outputRange = Range("A1:A10")
    outputRange.ClearContents
    
    For Each sh In ActiveSheet.Shapes
        outputRange.Cells(1, 1).Value = sh.Name
        outputRange.Cells(1, 2).Value = sh.Width
        outputRange.Cells(1, 3).Value = sh.Height
        outputRange.Cells(1, 4).Value = sh.Width * sh.Height
        
        Set outputRange = outputRange.Offset(1, 0)
    Next sh
End Sub

Remember that all shape dimensions are returned in points by default, where 1 point = 1/72 inch. You’ll need to convert these units if you need measurements in centimeters, inches, or other units for your formulas.

Sources

  1. How to auto change shape size based on specified cell value in Excel? - ExtendOffice
  2. How to change the size of a shape based on Cell width - Stack Overflow
  3. Show dimensions of Excel shape in column widths and row heights vba - Stack Overflow
  4. Shape.GetHeight method (Publisher) | Microsoft Learn
  5. How can I get the width/height of a shape? - Stack Overflow
  6. Shape.Width property (Excel) | Microsoft Learn
  7. VBA - Shape Height/Width - How to anchor shape - Reddit
  8. Size cell to fit shape - Excel Forum
  9. Retrieving the Actual Size of a Shape - MrExcel Message Board
  10. Trying to adjust size of shapes in VBA script but cannot get to size precisely - Stack Overflow

Conclusion

Retrieving shape width and height values in Excel for use in formulas requires VBA programming, as there are no built-in worksheet functions for this purpose. The key takeaways are:

  1. Use VBA UDF functions to create custom formulas that can access shape properties like .Width and .Height which return values in points.

  2. Handle unit conversions appropriately since Excel measures shapes in points (1/72 inch) while users often need centimeters or inches for calculations.

  3. Consider the macro security implications - your workbook must be saved as .xlsm and macros enabled for UDF functions to work.

  4. Address precision challenges by using proper VBA techniques like LockAspectRatio = msoFalse before setting exact dimensions.

  5. Understand the relationship between shapes and cells - while shapes can be positioned relative to cells, their sizing doesn’t follow simple linear relationships with column widths.

By implementing these VBA solutions, you can effectively integrate shape dimensions into your Excel formulas and create dynamic, responsive spreadsheets that react to shape properties.

How to convert shape dimensions from points to centimeters in Excel VBA?What are the best practices for creating dynamic dashboards with shape-based metrics in Excel?How can I automatically resize Excel shapes based on cell values using VBA?What are the common errors when working with shape dimensions in Excel VBA and how to fix them?How to create conditional formatting rules based on shape size in Excel?What's the relationship between Excel column widths and shape dimensions in points?
Ask NeuroAgent