I am trying to program a vba code to draw a smooth curve among control points as showing in the figure below:

This is part of the code, is there any suggest how to curve fitting among control points (polynomial regression). I just want guide or advice how to make this code to work.

Public Sub TestLinest()

Dim x
Dim i As Long
Dim evalString As String
Dim sheetDisplayName As String
Dim polyOrder As String

' this is whatever the name of your sheet containing data:
sheetDisplayName = "Sheet1"
' this gets the index of the last cell in the data range
i = Range(sheetDisplayName & "!A17").End(xlDown).Row
' Obviously change this depending on how many polynomial terms you need
polyOrder = "{1,2,3}"

evalString = "=linest(" & sheetDisplayName & "!E6:E" & i & ", " & sheetDisplayName & "!D6:D" & i & "^" & polyOrder & ")"
x = Application.Evaluate(evalString)

Cells(3, 8) = x(1)
Cells(4, 8) = x(2)
Cells(5, 8) = x(3)
Cells(6, 8) = x(4)

End Sub

CodePudding user response：

Just a suggestion; Not sure about VBA code (need to explore).

To get a polynomial curve in your chart, you can simply follow these steps;

1. Right click on data points on scatterplot
2. Click on "Format Trendline"
3. Under Trendline options, you will see "Polynomial" with order range of 2 to 6.