Consulting

Results 1 to 3 of 3

Thread: vba chart series formula property

  1. #1

    vba chart series formula property

    Sub Ohh(chtname As String, chtsheet As String, chttitle As String, chtrangex As Range, chtrangey As Range, chtrangey1 As Range, chtrangey2 As Range, chtxaxistitle As String, chtyaxistitle As String)
    Dim OlaCab1 As Series
    Dim olacab As SeriesCollection
    On Error GoTo ErrorHandling
    
    
        With Charts(chtname)
            
            .ChartType = xlLine
            .HasTitle = True
            
            .ChartTitle.Text = chttitle                 
            Set OlaCab1 = olacab.NewSeries
            ActiveChart.SeriesCollection.NewSeries                           
            .SeriesCollection(1).XValues = chtrangex
            Activechart.seriescollection.formula= "=series(""jan"",chtrangex,chtrangey,1)"     
            
    
           'ActiveChart.SeriesCollection.NewSeries
          '.SeriesCollection(3).XValues = chtrangex 
    
           'ActiveChart.SeriesCollection.NewSeries
          '.SeriesCollection(3).XValues = chtrangex
         
    
          Set olacab = ActiveChart.SeriesCollection
    '      For Each OlaCab1 In olacab
    '        OlaCab1.Name = Sheets("Sheet1").Cells(1, i + 2)
    '        i = i + 1
    '      Next OlaCab1
          
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = chtxaxistitle
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = chtyaxistitle
        End With
    Exit Sub
    
    
    ErrorHandling:
    MsgBox "No Chart found create one?"
    Charts.Add
    ActiveChart.Name = chtname
    Resume
    End Sub
    
    
    Sub CallOhh()
    Dim chtrangex As Range, chtrangey As Range, chtrangey1 As Range, chtrangey2 As Range
    Set chtrangex = Sheets("Sheet1").Range(Sheets("Sheet1").Range("A2"), Sheets("Sheet1").Range("A2").End(xlDown))
    Set chtrangey = Sheets("Sheet1").Range(Sheets("Sheet1").Range("B2"), Sheets("Sheet1").Range("B2").End(xlDown))
    Set chtrangey1 = Sheets("Sheet1").Range(Sheets("Sheet1").Range("C2"), Sheets("Sheet1").Range("C2").End(xlDown))
    Set chtrangey2 = Sheets("Sheet1").Range(Sheets("Sheet1").Range("D2"), Sheets("Sheet1").Range("D2").End(xlDown))
    Call Ohh("Chart1", "Sheet1", "Fruits sales", chtrangex, chtrangey, chtrangey1, chtrangey2, "Fruits", "Sales")
    End Sub



    Here in this code i have declared set set ranges chtrangex and chtrangey for 1st serie(i have shown only ist serie here).and then i used activechart.seriescollection(1).formula = ........so many ways but the declared ranges seems to not working in the code above. It is even not working in the line seriescollection(1).xvalues = chtrangex. please help. But i know how to successfully create chart like using seriescollection.add property.My data is in sheet1.

    jan feb mar
    apple 208 215 239
    bannana 272 156 102
    grapes 124 82 112
    orange 214 69 139
    cherry 278 91 285
    strawberry 235 291 257
    jackfruit 221 287 84
    peers 292 175 90

  2. #2
    I found a solution for this code i.e use address function with range variables i.e.
    chtrangex.address(,,,true)......in the formula string.Any way if others have more thoughts please reply.

  3. #3
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    SetSourceData method

    Option Explicit
    
    
    Sub test()
        Dim cht As Chart
        Const chtname = "Chart1"
        Const chtsheet = "Sheet1"
        Const chttitle = "Fruits sales"
        Const chtxaxistitle = "Fruits"
        Const chtyaxistitle = "Sales"
        Dim chtrange As Range
        
        Set chtrange = Sheets(chtsheet).Cells(1).CurrentRegion
        
        On Error Resume Next
        Set cht = Charts(chtname)
        On Error GoTo 0
        If cht Is Nothing Then
            Set cht = Charts.Add
            cht.Name = chtname
        End If
        
        With cht
            .ChartType = xlLine
            .HasTitle = True
            .ChartTitle.Text = chttitle
            
            .SetSourceData chtrange
                    
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = chtxaxistitle
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = chtyaxistitle
        End With
            
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •