Calculate smooth control points for an Excel curve shape

Recently I was faced with the challenge of plotting shipping routes on a world map in MS Excel. After fiddling a bit with the Google MAPS API I decided to generate the routes myself. How hard could it be?

First I had to determine the shape of choice to display the routes. This was quickly established: the curve shape. Excel Help offered following VBA how to generate this type of shape:

Sub TestAddCurveShape_1()

    Dim pts(1 To 7, 1 To 2) As Single

    pts(1, 1) = 0
    pts(1, 2) = 0
    pts(2, 1) = 72
    pts(2, 2) = 72
    pts(3, 1) = 100
    pts(3, 2) = 40
    pts(4, 1) = 20
    pts(4, 2) = 50
    pts(5, 1) = 90
    pts(5, 2) = 120
    pts(6, 1) = 60
    pts(6, 2) = 30
    pts(7, 1) = 150
    pts(7, 2) = 90

    Sheet1.Shapes.AddCurve SafeArrayOfPoints:=pts

End Sub

After running the code this was the result:

“This is going to be easy!” I thought. Next step was to plot a test route based on some data points. A data point pair is a vertex that contains a X and Y value; it determines the actual location of a point in a curve shape. Control points are needed to define the direction and smoothness of the line as it passes through a point of the shape. When you manually put a curve shape on a worksheet smooth curves are generated automatically. That’s why I assumed that the control points would be calculated for me as I ran this code:

Sub TestAddCurveShape_2()

    Dim pts(1 To 7, 1 To 2) As Single

    pts(1, 1) = 0
    pts(1, 2) = 0
    pts(4, 1) = 20
    pts(4, 2) = 50
    pts(7, 1) = 150
    pts(7, 2) = 90

    Sheet1.Shapes.AddCurve SafeArrayOfPoints:=pts

End Sub

The result was this:

Not exactly what I had in mind. I wouldn’t want to be on that ship!

Disappointed, I realised I would have to come up with an algorithm that would calculate the control points in such a way that a line would pass smoothly through all the points of the curve shape. Not being aware of terms like Bézier, interpolation or spline it took a while to google up the info. I finally came across this article by Jon Bittner. Apparently I was not the only one looking for the algorithm!
The article mentioned this example file (deep link) by Brian T. Murphy that contained the desired code.

I needed to make three changes to the file to make it work for me.
1. I slightly changed the way the start- and end segment of the curve shape is calculated
2. Refactoring was done to make it fit intended routine interfaces
(f.i. GetArrayOfCurvePoints(sngarrDataPoints() As Single) As Single())
3. I altered the script to meet my coding standards

The result was this sample spreadsheet that acted as a proof of concept.
The sample allows you to enter up to six vertices that are defined in input range C3:N3. After hitting the ‘Draw Curve’ button the curve shape is generated. As a default random vertices are generated in the input range.

Note regarding the ‘Random’ data points sample (copied from C7:N7):
After the shape is created the entered data points do not match the shown curve points (in named range CurvePoints). This reason for this is that the RAND() formulas generate new values after a worksheet change event.

Have fun with it. Click the image below in case you were wondering how the final solution looked.