How to Do Linear Interpolation Excel VBA (with Easy Steps)

Sometimes, you may deal with a dataset that follows a linear regression. You want to know an unknown value from a known value of that dataset. In this case, you need to do a linear interpolation. It helps you to find out the unknown data point. There are several functions to perform the interpolation. But whenever you need to create a custom function, you need the help of VBA Macros. It can create a function that will do a linear interpolation. In this article, we’re going to show you some easy steps to do linear interpolation with VBA in Excel. So, let’s get started.

Here’s the final output that you will get by the end of the article.

Overview Image

What Is Linear Interpolation in Excel?

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

Suppose you have a point W(a,b) and V(c,d). Now, for an unknown point of U(x,y), you want to know the y value for the known value of x. There is a very common mathematical equation for this scenario. The equation is:

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

From the above equation, you can find the unknown y value.

Calculate Linear Interpolation with VBA in Excel: 4 Easy Steps

There are several functions from which you can do linear interpolation in Excel. Also, you can create a function with the help of VBA from where you will be able to find the unknown value.

Here, we have used the Microsoft 365 version. You may use any other version at your convenience.

Step 01: Create a Dataset

  • In the beginning, you need to create a dataset. In our case, we have taken a dataset of Solubility in Different Temperatures. We all know that solubility changes with respect to temperature.

Sample dataset

Now, we want to know the solubility of a fixed temperature, which is not provided in the dataset.

Step 02: Open Visual Basic Editor

  • We need to create a function that will do our job for this interpolation. For creating a User Defined Function in Excel, firstly, navigate to the Developer tab and move to Visual Basic.

Opening Developer tab for linear interpolation Excel VBA

  • Apparently, Visual Basic Editor opens. Choose Insert tab >> Module >> Module1 from there.

Visual Basic Editor for linear interpolation Excel VBA

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

Step 03: Insert Function with VBA Macros

  • In Module 1, write up 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

VBA code for creating function for linear interpolation Excel VBA

Code Explanation:

We have declared a function named Lin here. We called out the WorksheetFunction Index and Match. The VBA Match function finds the relative position of the largest value of w and v which is the range of data you have in your dataset. The VBA Index function will return the largest value obtained by the Match function. Then, we used the same formula for the y value of the point w, but here, we insert +1 for the value after the largest value. Similarly, we set c and d, which are the coordinates of point v. We wrote up the interpolation equation and named it the Lin function.

Step 04: Use the Function for Interpolation

  • Eventually, run the code with the F5 key and it will create the function.
  • Sequentially, move to cell C13 and insert the below formula.

Here, C12 is the known x value. B5:B10 is the range of the w value, and C5:C10 is the range of the v value.

The custom Lin function will do linear interpolation from the given data.

  • Eventually, press ENTER.

You will get the following output, like the image below.

Using Custom Function for linear interpolation Excel VBA

Moreover, if you change the known value of the Temperature, the Solubility changes accordingly. See the below image for a better understanding.

Changing Y value with the Change of X

Read More: How to Use Non Linear Interpolation in Excel

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


That’s all about today’s session. These are some easy steps for linear interpolation with VBA in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet.

Related Articles

<< Go Back to Excel Interpolation | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF