:: infotechGuyz.com ::
> For the IT community by the IT community

Excel 2010 Macro Specify Size and Location of a Chart

 

The AddChart method has additional parameters you can use to specify the type of chart, the size of the chart, and the chart’s location on the worksheet.

The location and size of a chart are specified in points (72 points = 1 inch). For instance, the Top parameter requires the number of points from the top of Row 1 to the top edge of the worksheet.

To create a chart that roughly covers the Range C11:J30:

Sub SpecifyLocation()

    Dim WS As Worksheet

    Set WS = Worksheets(“Sheet1”)

WS.Shapes.AddChart(xlColumnClustered, _

        Left:=100, Top:=150, _

        Width:=400, Height:=300).Select

    ActiveChart.SetSourceData Source:=WS.Range(“A1:E4”)

End Sub

 

It requires a lot of trial and error to randomly figure out the exact distance in points to cause a chart to line up with a certain cell. Luckily, just ask VBA to tell you the distance in points to a certain cell. If you ask for the Left property of any cell, you find the distance to the top-left corner of that cell. You can also ask for the width of a range or the height of a range.

For example, the following code creates a chart in C11:J30 exactly:

Sub SpecifyExactLocation()

    Dim WS As Worksheet

    Set WS = Worksheets(“Sheet1”)

WS.Shapes.AddChart(xlColumnClustered, _

        Left:=WS.Range(“C11”).Left, _

        Top:=WS.Range(“C11”).Top, _

        Width:=WS.Range(“C11:J11”).Width, _

        Height:=WS.Range(“C11:C30”).Height).Select

    ActiveChart.SetSourceData Source:=WS.Range(“A1:E4”)

End Sub

 

For the above code, the location of the Chart object is not being moved. Instead, you are moving the location of the container that contains the chart. In Excel 2010, it is either the ChartObject or the Shape object. If you change the actual location of the chart, you move it within the container. Because you can actually move the chart area a few points in either direction inside the container, the code will run, but you will not get the desired results.

To move a chart that has already been created, you can reference either the ChartObject or the Shape and change the Top, Left, Width, and Height properties:

Sub MoveAfterTheFact()

    Dim WS As Worksheet

    Set WS = Worksheets(“Sheet1”)

    With WS.ChartObjects(“Chart 9”)

        .Left = WS.Range(“C21”).Left

        .Top = WS.Range(“C21”).Top

        .Width = WS.Range(“C1:H1”).Width

        .Height = WS.Range(“C21:C25”).Height

    End With

   End Sub