To find x2, we’ll use essentially the same function. Of course, this assumes that the values of x are in ascending order from top to bottom. We’re telling VBA to look in the array of “xvalues” and find the position largest value that is less than x with the MATCH function, then return the value of x in that position and assign it to the variable x1. So the line of code that extracts the x1 value from the table looks like this: We can do that by preceding the function name with “Application.WorksheetFunction”. In order to use them in our function, we have to tell VBA that they are worksheet functions. VBA doesn’t recognize these functions by themselves. See also 3 Ways to Change the Data in an Excel Scatter Chart We can use INDEX and MATCH to pull the values from the spreadsheet into the linear interpolation VBA function, but there’s a catch. Gather the InputsĪccording to the linear interpolation equation, to estimate y, we’ll need to gather a few values from our table of x- and y-data: x1, y1, x2, and y2. The first line of the function should look like this:Įxcel automatically adds the last line “End Function” when you type enter after the first line. So we’ll enter those arguments, separated by commas. The value of x for which we want to estimate a corresponding y-value.To perform the calculations, three arguments are required: We’ll need to give the function a name – I’ll call it “LinInterp”. Adding the module automatically opens a new code window. With the VBA editor open, insert a module into the workbook by right-clicking on the workbook in the project window and selecting Insert>Module. We’ll need to gather the input values and estimate the value of y using the following equation:Ĭreate the Linear Interpolation VBA Function The code and the math are straightforward.

Since I’ve created this function for my own worksheets, I’ve been surprised at the number of times that I’ve used it myself. This is an essential function to keep in your toolbox if you find yourself needing to interpolate from tables of data frequently. In this post, I’ll show you how to wrap this entire process into a linear interpolation VBA function.

Recently I wrote about linear interpolation in Excel and showed how to do this in a worksheet.

Vba expected named parameter