# 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

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

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 