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., Copyright © 2014. All rights reserved.