Multiple graphs in excel using VBA

Question

I am extremely new to excel vba and am using this first attempt as a learning experience. I am hoping to make a matrix of scatterplots in a separate sheet from the sheet they are taking the data from.

So heres a kind of schematic of the graphs I would like to be generated in an excel sheet. This represents a single satterplot [x-axis(ColumnletterRownumber), y-axis(ColumnletterRownumber)]

[(S2:S372),(AW2:AW372)] [(T2:T372),(AW2:AW372)] [(U2:U372),(AW2:AW372)]

[(S2:S372),(AX2:AX372)] [(T2:T372),(AX2:AX372)] [(U2:U372),(AX2:AX372)]

[(S2:S372),(AY2:AY372)] [(T2:T372),(AY2:AY372)] [(U2:U372),(AY2:AY372)]

[(S2:S372),(AZ2:AZ372)] [(T2:T372),(AZ2:AZ372)] [(U2:U372),(AZ2:AZ372)]

So those would be the scatterplots on the next sheet. Obviously I need a lot more graphs than that but that should give you an idea.

Here's what I got so far: Sorry in advance for the large amount of commented out things... those are ideas I think might help but I haven't gotten them to work.

Sub SPlotMatrix1()


Application.ScreenUpdating = False


'SPlotMatrix1 Macro


'Define the Variables

'---------------------

Dim Xaxis As range

Dim Yaxis As range


''Initialize the Variables

''-------------------------


Set Xaxis = range("S2:S372")

Set Yaxis = range("AW2:AW372")




'Tell macro when to stop

'-----------------------

Dim spot As Long


spot = 0


Do Until spot > 50


Sheets("2ndFDAInterimData").Select

''MAIN LOOP


'Graph1

'-------

'Selection Range


   range("S2:S372,AW2:AW372").Select

   'range("Xaxis,Yaxis").Select

   'range("AW1:AW372",S1:S372").Offset(0, rng).Select


    'range("AW1:AW372", 0).Select

    'range("0,S1:S372").Offset(0, rng).Select


    range("S372").Activate

'Select Graph Range

    ActiveSheet.Shapes.AddChart2(240, xlXYScatter).Select

'    ActiveChart.SetSourceData Source:=range( _

        "'2ndFDAInterimData'!$AW$1:$AW$372,'2ndFDAInterimData'!$S$1:$S$372")

'Graph Title

    ActiveChart.SetElement (msoElementChartTitleAboveChart)

    ActiveChart.FullSeriesCollection(1).Select

    ActiveChart.FullSeriesCollection(1).name = "='2ndFDAInterimData'!$DL$1"

'Add Trendline

    ActiveChart.Axes(xlValue).MajorGridlines.Select

    ActiveChart.FullSeriesCollection(1).Trendlines.Add Type:=xlLinear, Forward _

        :=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, name:= _

        "Linear (Ave.Score)"

    ActiveChart.FullSeriesCollection(1).Trendlines.Add Type:=xlLinear, Forward _

        :=0, Backward:=0, DisplayEquation:=0, DisplayRSquared:=0, name:= _

        "Linear (Ave.Score)"

    ActiveChart.FullSeriesCollection(1).Trendlines(2).Select

    Selection.DisplayRSquared = True

'Move Rsquare Label to Corner

    ActiveChart.FullSeriesCollection(1).Trendlines(2).DataLabel.Select

    Selection.Left = 30.114

    Selection.Top = 13.546

'Format Trendline

    ActiveChart.FullSeriesCollection(1).Trendlines(2).Select

    With Selection.Format.Line

        .Visible = msoTrue

        .ForeColor.ObjectThemeColor = msoThemeColorText1

        .ForeColor.TintAndShade = 0

        .ForeColor.Brightness = 0

        .Transparency = 0

    End With

    With Selection.Format.Line

        .Visible = msoTrue

        .DashStyle = msoLineSolid

    End With

    ActiveChart.ChartArea.Select

    With Selection.Format.Line

        .Visible = msoTrue

        .Weight = 1.75

    End With

'Resize Graph

    ActiveChart.Parent.Height = 180

    ActiveChart.Parent.Width = 239.76

'Y axis scale

    ActiveChart.FullSeriesCollection(1).Select

    ActiveChart.Axes(xlValue).Select

    ActiveChart.Axes(xlValue).MaximumScale = 100

'Move graph to center (for the purposes of design and debugging)

    ActiveChart.Parent.Cut

    range("V4").Offset(spot, 0).Select

    ActiveSheet.Paste



' 'Move Graph to other sheet

'    ActiveChart.Parent.Cut

'    Sheets("graphs").Select

'    range("A1").Offset(spot, 0).Select

'    ActiveSheet.Paste




spot = spot + 14


Loop





Application.ScreenUpdating = True



End Sub

I've gotten to the point where I am creating a number of the same graphs in a row or column if I want. But I can't successfully get the graphs ranges to change so that they are plotting different data.

Please help, let me know if I can further clarify. Thank you!

Jon Peltier · Accepted Answer · 2015-04-10 14:56:59Z

You can define the data with a couple simple loops. Create the chart and embellish it within the inner loop.

Sub InsertMultipleCharts()
  ' data particulars
  Dim wksData As Worksheet
  Const Xcol1 As Long = 19 ' column S
  Const Xcol2 As Long = 21 ' column U
  Const Ycol1 As Long = 49 ' column AW
  Const Ycol2 As Long = 52 ' column AZ
  Const Row1 As Long = 2
  Const Row2 As Long = 372

  ' chart dimensions
  Const FirstChartLeft As Long = 50
  Const FirstChartTop As Long = 50
  Const ChartHeight As Long = 180
  Const ChartWidth As Long = 240

  ' working variables
  Dim wksChart As Worksheet
  Dim cht As Chart
  Dim Xrange As Range
  Dim Yrange As Range
  Dim Xcol As Long
  Dim Ycol As Long

  ' define sheets
  Set wksData = ActiveSheet
  Set wksChart = Worksheets.Add

  ' loop X
  For Xcol = Xcol1 To Xcol2
    ' define x values
    Set Xrange = Range(wksData.Cells(Row1, Xcol), wksData.Cells(Row2, Xcol))

    ' loop Y
    For Ycol = Ycol1 To Ycol2
      ' define y values
      Set Yrange = Range(wksData.Cells(Row1, Ycol), wksData.Cells(Row2, Ycol))

      ' insert chart
      Set cht = wksChart.Shapes.AddChart2(Style:=240, XlChartType:=xlXYScatter, _
                  Left:=FirstChartLeft + (Xcol - Xcol1) * ChartWidth, _
                  Top:=FirstChartTop + (Ycol - Ycol1) * ChartHeight, _
                  Width:=ChartWidth, Height:=ChartHeight).Chart

      ' assign data to chart
      cht.SetSourceData Source:=Union(Xrange, Yrange)

      ' chart title
      cht.HasTitle = True
      With cht.ChartTitle.Font
        .Size = 12
        .Bold = False
      End With

      ' axis scale
      cht.Axes(xlValue).MaximumScale = 100

      ' legend
      cht.HasLegend = False

      ' series: name, trendline, and Rsquared
      With cht.SeriesCollection(1)
        .Name = "Series Name" '''' don't know where these are coming from
        With .Trendlines.Add(Type:=xlLinear, DisplayRSquared:=True).DataLabel
          .Format.Line.DashStyle = msoLineSolid
          .Top = cht.PlotArea.InsideTop
          .Left = cht.PlotArea.InsideLeft
        End With
      End With
    Next
  Next
End Sub

Macro recorder code is messy, but it gives you syntax.

rgo · Accepted Answer · 2015-04-09 01:26:22Z

0

Try using the macro recorder to edit an existing range so you get the code for setting the ranges for X, Y and the range name and size. Once recorded you can swap out the new ranges as variables to get the new charts.

answered Apr 9, 2015 at 1:26

rgo

4891 gold badge4 silver badges12 bronze badges

Collectives™ on Stack Overflow

Multiple graphs in excel using VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related