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 0 down vote favorite

I have a VBA code that generates two colums of data. This analysis repeats to generate more data in the same columns but for different conditions and places this data below the first iteration's output. How do I get the code to generate a XY Scatter with smooth lines next to each block of data generated? My Attempt is below however I run into three problems: 1) It only generates one graph before the debug stops the macro, 2) It generates a barchart and not a scatter 3) How do I get each graph that is generated to be scaled placed next to the data it is drawing from such that I get a column of graphs?

    Sub TestExample()

   Dim NoIteration As Integer
   Dim Iteration As Integer
    NoIteration = Range("N26").Value

    Dim YieldIteration As Integer
    Dim NoOfJumps As Long
    Dim minyield As Long
    Dim maxyield As Long
    Dim jump As Long
        NoOfJumps = Range("Q24").Value
        minyield = Range("Q25").Value
        maxyield = Range("Q26").Value
        jump = Range("Q27").Value

    Dim xaxis As Range
    Dim yaxis As Range
        Dim c As Chart
       Dim Sh As String
        Sh = ActiveSheet.Name


    Range("M29:T1000").Select
    Selection.Clear

    For jump = 0 To NoOfJumps


        For Iteration = 0 To NoIteration
                    'Print Intervals
                    Range("M30").Offset(NoIteration + Iteration + 4, 0).Value = Range("V19").Value * Iteration

                    'Solve weights for minimum Spot SD for each given interval
                    SolverReset
                    SolverOk SetCell:="$T$18", MaxMinVal:=2, ValueOf:="0", ByChange:="$O$20:$R$20"
                    SolverAdd CellRef:="$T$17", Relation:=2, FormulaText:=Range("M30").Offset(NoIteration + Iteration + 4, 0).Value
                    SolverAdd CellRef:="$T$20", Relation:=2, FormulaText:=1
                    SolverAdd CellRef:="$T$7", Relation:=1, FormulaText:=minyield + jump 'State min required yield
                    SolverAdd CellRef:="$T$7", Relation:=3, FormulaText:=maxyield + jump 'State max required yield
                    SolverAdd CellRef:="$O$20:$R$20", Relation:=3, FormulaText:="0"
                    SolverSolve UserFinish:=True
                    SolverFinish KeepFinal:=1

                    'Print Income Return, SD
                    Range("N30").Offset(Iteration + jump * 50, 0).Value = Range("T7").Value
                    Range("O30").Offset(Iteration + jump * 50, 0).Value = Range("T8").Value

                    'Print Spot Return, SD
                    Range("N30").Offset(NoIteration + Iteration + 4 + jump * 50, 0).Value = Range("T17").Value
                    Range("O30").Offset(NoIteration + Iteration + 4 + jump * 50, 0).Value = Range("T18").Value


                    'Print Total Return, SD
                    Range("N30").Offset(NoIteration * 2 + Iteration + 8 + jump * 50, 0).Value = Range("AC17").Value
                    Range("O30").Offset(NoIteration * 2 + Iteration + 8 + jump * 50, 0).Value = Range("AC18").Value


        Next Iteration

        Set yaxis = Range(Range("N30").Offset(Iteration + jump * 50, 0), Range("N30").End(xlDown))
        Set xaxis = Range(Range("O30").Offset(Iteration + jump * 50, 0), Range("$O30").End(xlDown))


Set c = ActiveWorkbook.Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=Sh
'Set c = c.Location(Where:=xlLocationAsObject, Name:="Sheet1")
With c
    .ChartType = xlXYScatterLines
    ' set other chart properties
End With

Dim s As Series
Set s = c.SeriesCollection.NewSeries
With s
    .Values = yaxis
    .XValues = xaxis

End With


    Next jump
End Sub
share|improve this question

2 Answers

up vote 0 down vote

  1. There could be lot's of problems which are difficult to analyse when we don't have data you are working with. However, see next points which could be helpful and solve the 1st issue.

  2. If you finally place your chart as ChartObject on some sheet you could do it a bit different way. Moreover, by default new chart is bar style. To avoid changing it into ScatterLines then (which I guess causes some exceptions) try to use the following code (instead of your in some areas):

    Dim c As Chart ==>change into Dim c as Shape

The whole section starting with: Set c=Activeworkbook.Charts.Add until End With replace with the following code:

Set c = Sheets(Sh).Shapes.AddChart(xlXYScatterLines, 10, 10, 200, 200)

Dim s As Series
Set s = c.Chart.SeriesCollection.NewSeries
With s
    .Values = yaxis
    .XValues = xaxis

End With

  1. To set position of your chart try to use similar solution before end of the loop:

    'setting position
With c
    .Left = Range("M10").Offset(0,1).Left
    .Top = Range("M10").Offset(0,1).Top
End With

but instead of Range("M10") put the reference of your data Range.

share|improve this answer
That is what I needed but the essential problem I was having was looking up the data to plot the graph. As the original code prints blocks of data seperated by space it was to get a code that looks up each block and plots a graph next to it as the first code goes through a loop. One problem that I do have is that if I insert the below code in the above code it works fine. However if I "Call" it, it doesn't. No Idea why. As the below code is only part of a bigger code (which plots several graphs) calling it would be useful. – Mary Apr 3 at 9:22
So, does it mean that problem is solved?? – KazJaw Apr 3 at 9:26
Yes, thank you- I have put the solution below please see srs.Xvalues/ srs.Values part. However it not clear why it is not working under a Call. – Mary Apr 3 at 9:58
How are you "calling" the code? – Jon Peltier Apr 3 at 12:16
up vote 0 down vote

This code looks up the code in the columns and plots the graph in the specified range. It offsets the lookup and plotting as the first code goes through the loop.

Dim srs As Series

Set cht = ActiveChart
'GRAPH 1
    Set rng1 = ActiveSheet.Range(Range("W30").Offset(jump * 50, 0), Range("AA40").Offset(jump * 50, 0))


With ActiveSheet.ChartObjects.Add(Left:=rng1.Left, Width:=rng1.Width, Top:=rng1.Top, Height:=rng1.Height)
 '(Left:=100, Width:=375, Top:=75, Height:=225)
    .Chart.ChartType = xlXYScatterLines

     Set srs = .Chart.SeriesCollection.NewSeries
    srs.Name = "Graph1" '
    srs.XValues = Range(Range("O30").Offset(jump * 50, 0), Range("O30").Offset(jump * 50, 0).End(xlDown))
    srs.Values = Range(Range("N30").Offset(jump * 50, 0), Range("N30").Offset(jump * 50, 0).End(xlDown))

End With
    End With
share|improve this answer

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.