Bicubic Interpolation – One VBA Function Compatible with Both Arrays and Ranges
People write VBA functions to use in their spreadsheets. People write VBA functions to use in their VBA scripts. How about a single VBA function that works in both script and spreadsheet?
Sometimes your tabular dataset is in a worksheet. Other times it is already in an array and you don’t have any need to copy it to a worksheet. Can you just use the bicubic interpolation function on both? This article shows how to bring versatility to an Excel VBA function for both spreadsheets and VBA scripts using bicubic interpolation as an example.
Download Spreadsheet and Source Code
Click to download the example workbook Bicubic Interpolation Excel Function Example with Source Code v2.
BicubicInterpolation contains the function in this article. The module
Array_Tests contains code that generates an array of tabular data and calls the interpolation function using that array.
VBA Variant Type
One of the (dangerous but sometimes) useful characteristics of VBA is that it includes the versatile Variant type. A Variant is a type of variable that can have any, uh, type. It can contain a string of text or a decimal number or an array of numbers or an object, etc. Whenever you have a variable that might have to store a variety of data types, that variable needs to be a Variant.
In this example we start with a VBA function with three arguments: a grid of numbers and two individual numbers. Originally the first argument was a Range so that the user just had to reference the cells that contained the grid of numbers. This worked fine because the Range object has Rows and Columns collections and we took advantage of these things in the code. For example, we needed to isolate the first column and first row which comprise the left and top headers of the tabular data.
Change from Array to Variant
If we pass an array to the function, it causes an error. The way to prevent the error is to change the first parameter to a Variant. If you do that, you can pass anything in the first parameter without causing an error.
Public Function BicubicInterpolation(Table As Range, TopPoint As Double, LeftPoint As Double) As Double
Public Function BicubicInterpolation(Table As Variant, TopPoint As Double, LeftPoint As Double) As Double
Handle the First Argument Separately, Depending on What It Is
The next step is to add an IF statement that checks to see what was actually passed to the function. If it is a Range, then we just do what we did before. If it is an array, we handle it differently.
If TypeName(Table) = "Range" Then
When we have an array instead of a Range, we lose all of those nice properties. For example, we need to add this code to replace the Range.Columns() property. We have to do the work to allocate space for the array Left and then copy the values in manually. There are no shortcuts here.
' Allocate space for the array Left so that it is the same size as the first column.
ReDim Left(UBound(Table, 1))
' Loop once for each valid row in the first column and copy the values to Left.
For i = LBound(Table, 1) + 1 To UBound(Table, 1)
Left(i) = Table(i, LBound(Table, 2))
Other than the special handling, the interpolation calculations follow the same process.
Option Base 0 and Option Base 1
We have to consider another important difference between tabular data in a Range vs. tabular data in a 2D array. With a Range we know exactly where to find the data. The first datum is in Range.Cells(1, 1) and that will never change. The first column is Range.Columns(1) and that too will never change.
Arrays are not so predictable (in VBA) because they can be 0-based or 1-based. That means that the first element can be indexed starting with 0 or starting with 1. For example, in a zero-based system you use Table(0, 0). In a one-based system you use Table(1, 1) instead.
To make matters more confusing, there is no guarantee that the first element is 1 or 0. For example, you can define an array like this:
Dim Table(5 To 8, 6 To 9) as Double
In that case the first element is Table(5, 6).
Why would somebody do such a strange thing?
There are many reasons to use Option Base 0 or Option Base 1 or other numbering. Here are some of them.
- Some Excel functions that take array parameters will not work with anything but Option Base 1. (e.g. QueryTable.Add in certain versions of Excel)
- You are translating code from another language to use in a VBA script and it is easiest to maintain the same array indexing scheme.
- A certain numbering scheme just makes more sense in your particular application.
The Answer Is to Use Lbound and UBound
You can’t predict what kinds of crazy arrays the user will send to a custom VBA function, but that’s ok. If you access your array using LBound and UBound, you can’t go wrong.
' BAD Idea:
For i = 1 To 10
Table(i) = ...
' GOOD Idea:
For i = LBound(Table, 1) To UBound(Table, 1)
Table(i) = ...
Variants Can Be Bad
Why not just always use Variants? The bad thing about the Variant is that it slows down the script.
The compiler lets you store anything you want in a Variant, but that flexibility comes at a cost. Every time it arrives at a line of code with a Variant, the compiler has to stop what it’s doing and figure out what the Variant variable contains.
Dim MyVar as Variant
Dim X as Double
Dim Y as Long
MyVar = 1
X = MyVar + 1.5
Y = MyVar + 1
This code looks like a simple addition of two numbers, but it’s not so simple for the compiler! The compiler has to consider each and every possible type that can be added to 1.5. MyVar equals 1, but is it a Single? A Double? A Short? A Long? A String? An Object? The compiler figures it out (Double for the X assignment and Long for the Y assignment), but each time it has to consider every possible option and decide which one to use, and that takes a lot of time. That’s why we only use the variant for the function argument and specify a type for everything else.
Why not just write two functions like BicubicInterpolationWorksheet() and BicubicInterpolationArray()?
Writing separate functions is just another approach, and there is nothing wrong with that. The drawback of having two functions is that you have to remember the name of the function and as well as the name of its evil twin.
The benefit of a single function is its consistency. The function’s name is short, simple, and intuitive, and it always works.