How to Do Linear Interpolation Excel VBA

This is a final output of linear interpolation in Excel:

What Is Linear Interpolation in Excel?

Interpolation is a method of finding a value from known values. In linear interpolation, you get a new data point from two known data points.

Suppose you have a point W(a,b) and V(c,d). For an unknown point of U(x,y), you want to know the y value for the known value of x. TThe equation is:

y=c + ((d - c) * (x - a) / (b - a))

Step 1- Create a Dataset

The dataset showcases Solubility in Different Temperatures.

To know the solubility of a fixed temperature:

Step 2 – Open the Visual Basic Editor

To create a User Defined Function in Excel, go to the Developer tab and select Visual Basic.

In the Visual Basic Editor, choose Insert >> Module >> Module1.

Note: you can also open the Visual Basic Editor pressing ALT + F11.

Step 3 – Enter VBA code

In Module 1, use the following code.

Function Lin(x, w, v)
a = Application.WorksheetFunction.Index(w, Application.WorksheetFunction.Match(x, w, 1))
b = Application.WorksheetFunction.Index(w, Application.WorksheetFunction.Match(x, w, 1) + 1)
c = Application.WorksheetFunction.Index(v, Application.WorksheetFunction.Match(x, w, 1))
d = Application.WorksheetFunction.Index(v, Application.WorksheetFunction.Match(x, w, 1) + 1)
Lin = c + ((d - c) * (x - a) / (b - a))
End Function

Code Breakdown

The Lin function is declared. WorksheetFunction Index and Match is called. The VBA Match function finds the relative position of the largest value of w and v which is the range of data in your dataset. The VBA Index function will return the largest value obtained by the Match function. The same formula is used for the y value of w, but +1 is inserted in the value after the largest value. c and d, which are the coordinates of point v are set.