Resizing Plots VBA

Question

I am having issues with resizing images in VBA. I have tried a bunch of methods and here is the code that I am using:

    Sub Macro17()
    '
    ' Macro17 Macro
    '
    '
    Call Macro1
    Call Macro2
    End Sub

    Sub Macro1()
    Sheets("Data").Select
    Range("A77:N79").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("A58:N58,A77:N79")


     With ActiveChart
     'chart name
    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = " Plot 1"
     'X axis name
    '.Axes(xlCategory, xlPrimary).HasTitle = True
    '.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X-Axis"
     'y-axis name
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y - label"
End With
    ActiveChart.Parent.Cut
    Sheets("Plots").Select
    ActiveSheet.Paste Destination:=Worksheets("Plots").Range("B2:B5")
    With ActiveChart.Parent
    .Height = 369
    .Width = 520
  End With

End Sub

Sub Macro2()
Sheets("Data").Select
    Range("A83:N85").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SetSourceData Source:=Range("A58:N58,A83:N85")


     With ActiveChart
     'chart name
    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = " Plot Two"
     'X axis name
    '.Axes(xlCategory, xlPrimary).HasTitle = True
    '.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "X-Axis"
     'y-axis name
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y - label"
End With
    ActiveChart.Parent.Cut
    Sheets("Plots").Select
    ActiveSheet.Paste Destination:=Worksheets("Plots").Range("B30:B35")
    With ActiveChart.Parent
    .Height = 369
    .Width = 520
  End With
  ActiveChart.Axes(xlCategory).TickLabelPosition = xlTickLabelPositionLow

End Sub

So I use Macro17, to call both Macro1 and Macro2, and then plot the appropriate data, my issue is that when I call Macro17, only the first plot is made bigger. When I run through both macros with the debugger using F8 (in windows) I have no issues and the code runs as expeceted, but this is extremely in efficient. It probably has to do with how ActiveChart.Parent is used. Thank you for all your help it is much appreciated.

At a quick look, it could have something to do with your using `.Select`. I highly suggest reading through [how to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), which can relieve some issues in VBA. — BruceWayne, Jun 14 '16 at 14:13

score 0 · Accepted Answer · answered Jun 14 '16 at 17:25

0

The issue is created when ActiveChart is called again, the reason for this issue is that it isn't sure which chart is active. The solution is to use ActiveChart.Parent.Name = "Test" and then before ActiveChart is called again you have to use: ActiveSheet.Shapes("Test").Select. The key here is that when using ActiveSheets.Shapes() duplicates won't be handled well.

answered Jun 14 '16 at 17:25

Adam Warner

1,334
2
14
30

Once you have the chart created you should be able to use `ActiveSheet.ChartObjects()` to reference the chart instead of `ActiveSheet.Shapes()` – JerryT Jun 14 '16 at 19:10
@JerryT using ChartObjects() didn't work for me, I'm not exactly sure why. – Adam Warner Jun 15 '16 at 15:36
` With ActiveSheet.ChartObjects("Test") .Height = 369 .Width = 520 End With` – JerryT Jun 16 '16 at 11:24

Resizing Plots VBA

1 Answers1