Linear Interpolation in VBA


New Member
Jul 22, 2019
Hey Everyone,

I have been working on a function to linear interpolate some data- I got it to where it was giving values however they seem to be incorrect. I cant find an error in the equation so far so i am not sure where to fix things.

I am also extremely new to VBA to bear with me on the code

Thanks for the help in advance!

Dim X1 As Double, X2 As Double, X3 As Double, Y1 As Double, Y3 As Double

' X1 is used as the smallest x value
X1 = ActiveCell.Value
' X is used as the middle value
Application.ActiveCell.offset(1, 0).Activate
X = ActiveCell.Value
' X2 is used as the largst x value
Application.ActiveCell.offset(1, 0).Activate
X2 = ActiveCell.Value
' Y1 is used as the smallest Y value
Application.ActiveCell.offset(-2, 1).Activate
Y1 = ActiveCell.Value2
' Y2 is used as the largest Y value
Application.ActiveCell.offset(2, 0).Activate
Y2 = ActiveCell.Value2

Dim Pt1 As String, Pt2 As String, Pt3 As String

Pt1 = (X - X1)
Pt2 = (Y2 - Y1)
Pt3 = (X2 - X1)

MsgBox "" & Y1 + Pt1 * (Pt2 \ Pt3)

End Function


MrExcel MVP
May 17, 2006
welcome, Annie

Please post a sample of the input data. What values are in what cells, and maybe describe what each value means if it might aid understanding.

And then explain what the result is for those inputs and where/how it will be used.

regards, Fazza


Well-known Member
May 1, 2002
Can you also post the first part of your code - possibly only one line from the top (Public Function, perhaps?
Also, where does this code reside? in a code module, or worksheet module, or ThisWorkbook module?

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...