Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite
2

I am trying loop the scatter plot over columns using Excel VBA, but did get idea how to do it.

Here is a dummy data I produced by filling Y series with RAND() function and the figure shows a sample chart I produced.

enter image description here

Here is associated codes:

Sub multichart()
    Range("A1:B21").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B21"), PlotBy _
        :=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Y1"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveSheet.Shapes("Chart 6").ScaleHeight 0.61, msoFalse, msoScaleFromTopLeft
    ActiveChart.PlotArea.Select
    Selection.Top = 1
    Selection.Height = 106
    Selection.Height = 113
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    With Selection.Interior
        .ColorIndex = 2
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
End Sub

I want to loop this process to create N graphs (where N is number of Y columns in any sheet), means vary from sheet to sheet. Also plots will be appended one after another. Y limits and X limit are intended to be same in all plots for comparison. The output look something like (just showing 4 variables for example):

enter image description here

Sorry if this a basic question as I am Excel VBA guest.

share|improve this question

1 Answer

up vote 3 down vote accepted

You don't say what version of Excel. This works in 2010 and I think should work in other version. But I'm not a VBA chart expert. Hopefully the variable names are self-explanatory:

Sub multichart()
Dim ws As Excel.Worksheet
Dim i As Long
Dim cht As Chart
Dim cho As ChartObject
Dim TitleRange As Excel.Range
Dim DataRange As Excel.Range
Dim ChartHeight As Long
Dim LastCol As Long
Dim LastRow As Long
Dim MaxAmountToChart

Set ws = ActiveSheet
'delete the old ones
For Each cho In ws.ChartObjects
    cho.Delete
Next
ChartHeight = 20
With ws
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    MaxAmountToChart = Application.WorksheetFunction.Max(ws.Range(ws.Cells(1, 2), .Cells(LastRow, LastCol)))
    Set TitleRange = .Range("A1:A" & LastRow)
    For i = 2 To LastCol
        Set DataRange = .Range(.Cells(1, i), .Cells(LastRow, i))
        Set cht = Charts.Add
        cht.Location Where:=xlLocationAsObject, Name:=.Name
        Set cho = .ChartObjects(.ChartObjects.Count)
        cho.Top = (i - 1) * ChartHeight * .Rows(1).RowHeight
        cho.Left = 0
        Set cht = cho.Chart
        '.Shapes(cht.Name).ScaleHeight 0.61, msoFalse, msoScaleFromTopLeft
        With cht
            .ChartType = xlXYScatter
            .SetSourceData Source:=Union(TitleRange, DataRange), PlotBy:=xlColumns
            .HasTitle = True
            .ChartTitle.Characters.Text = "Y1"
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).MaximumScale = MaxAmountToChart
            With .PlotArea
                .Top = 1
                .Height = 106
                .Height = 113
                With .Border
                    .ColorIndex = 16
                    .Weight = xlThin
                    .LineStyle = xlContinuous
                End With
                With .Interior
                    .ColorIndex = 2
                    .PatternColorIndex = 1
                    .Pattern = xlSolid
                End With
            End With
        End With
    Next i
End With
End Sub
share|improve this answer
thnak you so much for the answer. I tested in excel 2003, 2007 and 2013, but not in 2010 (which you did). (1) I think alignment is still need to be worked usually plots are not tightly arranged as in figure - but put some constant gap between the figures and arranged in a single column as desired (2) Title of plot is consistently the first variable in the sheet. I would not worry about fixing this issue, if not trival – jon Mar 25 at 15:25
You're welcome. Are you asking me to do something else? My general advice would be to accept this answer, and try to adjust it yourself to do exactly what you want. – Doug Glancy Mar 25 at 16:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.