Resizing and Moving Excel Charts with VBA

How do I change the size or position of my chart with VBA?

Recording a Macro

You can always record a macro to find out how to do something. Select a chart, then record a macro while you move it and resize it. The result is something like this:

     Sub Macro1()
     '
     ' Macro1 Macro
     ' Macro recorded by Jon Peltier
     '
         ActiveSheet.Shapes("Chart 1").IncrementLeft -58.5
         ActiveSheet.Shapes("Chart 1").IncrementTop -57#
         ActiveSheet.Shapes("Chart 1").ScaleWidth 0.8, msoFalse, msoScaleFromTopLeft
         ActiveSheet.Shapes("Chart 1").ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft
     End Sub

Notice a few things about this recorded code. First, it doesn't act on the chart, ActiveChart, or on the chart object, ActiveSheet.ChartObjects(1). It acts on the shape that represents the chart object, ActiveSheet.Shapes("Chart 1"). Fine, we can live with that.

More important, the recorded code actually moves the chart, using IncrementLeft and IncrementTop. It also resizes the chart to a factor times its original size, using ScaleWidth and ScaleHeight. Since I usually know where and how large I want the chart to be, I would rather work on the actual position and size, not on incremental positions and scaled sizes.

Punching Up the Code

What you need to do is change the position and size of a chart object. It is actually the chart object that is embedded in the worksheet, and the chart object contains the chart. (The object model is at first rather difficult to comprehend.)

You refer to the chart object something like this:

     ActiveSheet.ChartObjects(1) ' the first chart object in the active sheet

     ActiveSheet.ChartObjects("MyChart") ' a chart that has been named "MyChart"

     ActiveChart.Parent ' the chart object containing the selected chart

Given that you know your standard sizes, in points (1/72 inch, plus or minus a printer fudge factor of a few percent, which is why God invented trial-and-error), you use this kind of approach:

     With ActiveChart.Parent
         .Height = 325 ' resize
         .Width = 500  ' resize
         .Top = 100    ' reposition
         .Left = 100   ' reposition
     End With

Suppose I want to line up a chart to cover a range in the worksheet. I can do this very easily using a range object variable and chartobject variable:

     Sub CoverRangeWithAChart()
         Dim RngToCover As Range
         Dim ChtOb As ChartObject
         Set RngToCover = ActiveSheet.Range("D5:J19")
         Set ChtOb = ActiveChart.Parent
         ChtOb.Height = RngToCover.Height ' resize
         ChtOb.Width = RngToCover.Width   ' resize
         ChtOb.Top = RngToCover.Top       ' reposition
         ChtOb.Left = RngToCover.Left     ' reposition
     End Sub

You can carry out this procedure a little further, using some of your best 6th grade algebra, to line up the charts on your worksheet:

     Sub LineUpMyCharts()
         Dim MyWidth As Single, MyHeight As Single
         Dim NumWide As Long
         Dim iChtIx As Long, iChtCt As Long

         MyWidth = 200
         MyHeight = 150
         NumWide = 3

         iChtCt = ActiveSheet.ChartObjects.Count
         For iChtIx = 1 To iChtCt
             With ActiveSheet.ChartObjects(iChtIx)
                 .Width = MyWidth
                 .Height = MyHeight
                 .Left = ((iChtIx - 1) Mod NumWide) * MyWidth
                 .Top = Int((iChtIx - 1) / NumWide) * MyHeight
             End With
         Next
     End Sub

The Interactive Chart Creation demonstrates code that asks the user to select a range, then resizes the chart to cover that range.

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile