Add Excel trendline name

  1. 5 weeks ago

    Eugene D

    Sep 11 Pre-Release Testers, Xojo Pro Canada scispec.ca
    Edited 5 weeks ago

    This is a new conversation that started from the post at : A Method is missing... .

    Hi @Mike L ,

    Here is the example code for Xojo that makes a graph in Excel, adds a trendline, and names the trendline:

    Sub Action() Handles Action
      Dim excel as new ExcelApplication
      excel.Workbooks.Add
      excel.Visible = True
      
      //Add data
      Dim i as integer
      For i = 1 to 50 
        excel.Range("A" + CStr(i)).Value = i
        excel.Range("B" + CStr(i)).Value = i*1.1*i
      Next
      
      //Create a chart
      excel.Range("A1:B50").Select_
      excel.ActiveSheet.Shapes.AddChart2(240, Office.xlXYScatterSmoothNoMarkers).Select_
      excel.ActiveChart.SetSourceData(excel.Range("Sheet1!$A$1:$B$50"))
      
      //Add a trendline
      excel.ActiveChart.FullSeriesCollection(1).Select_
      excel.ActiveChart.FullSeriesCollection(1).Trendlines.Add
      excel.ActiveChart.FullSeriesCollection(1).Name = "Upper two-sided"
      
    End Sub

    Does this work on your computer?

    Edit: This renames all of the lines. Is this what you wanted?

  2. Eugene D

    Sep 11 Pre-Release Testers, Xojo Pro Canada scispec.ca

    Here is how to change different options in a polynomial trendline with Xojo for Excel:

    Sub Action() Handles Action
      Dim excel as new ExcelApplication
      excel.Workbooks.Add
      excel.Visible = True
      
      //Add data
      Dim i as integer
      For i = 1 to 50 
        excel.Range("A" + CStr(i)).Value = i
        excel.Range("B" + CStr(i)).Value = i*1.1*i
      Next
      
      //Create a chart
      excel.Range("A1:B50").Select_
      excel.ActiveSheet.Shapes.AddChart2(240, Office.xlXYScatterSmoothNoMarkers).Select_
      excel.ActiveChart.SetSourceData(excel.Range("Sheet1!$A$1:$B$50"))
      
      //Add a trendline
      excel.ActiveChart.FullSeriesCollection(1).Select_
      excel.ActiveChart.FullSeriesCollection(1).Trendlines.Add
      excel.ActiveChart.FullSeriesCollection(1).Trendlines(1).Select_
      excel.Selection.Type = Office.xlPolynomial
      excel.Selection.Forward = 0
      excel.Selection.Backward = 0
      excel.Selection.Order = 2
      excel.Selection.DisplayEquation = False
      excel.Selection.DisplayRSquared = False
      excel.Selection.Name = "My Trendline Name"
    End Sub

or Sign Up to reply!