Creating Shapes, Graphics, Illustrations - Part 2

Here is yet another way of creating graphics in Excel - using VBA macro, like this one:

Sub DrawDefShape()
'Draws any defined shape representing Bézier curves
Dim x As Integer, y As Integer
Dim pt(1 To 7, 1 To 2) As Single
Dim rng As Range
Set rng = Sheets("Data").Range("A2:B8")
'Fill array of control point coordinates with values
For x = 1 To 7
    For y = 1 To 2
        pt(x, y) = rng.Cells(x, y).Value
    Next y
Next x
Set myDoc = Worksheets("Data")
myDoc.Shapes.AddCurve SafeArrayOfPoints:=pt
Range("A10").Select
'Display the whole 2D array in MsgBox
For i = 1 To UBound(pt)
dPts = dPts & pt(i, 1) & "   " & pt(i, 2) & vbNewLine
Next i
MsgBox dPts
End Sub

Before going into the details of the macro structure and its use, let's look at some examples of curves and shapes you can easily create with it. Here they are:

 

All these shapes have been created using the macro above and then filled with pictures or gradient color fills, and eventually formatted in other available ways.

The macro uses so-called control points that you can enter anywhere in your worksheet. In my example I'm using the range of data shown here:

The x, y numbers represent coordinates of the control points. They are expressed in pixels. If the resolution of your computer screen is e.g., 1980 x 1080 pixels, then - in order to see your created whole shape within the screen size - you would specify control points within these limits. To obtain your specific shape you can experiment with this data setup, including the number of points. Just remember the following rules:

• the first point that you specify is the starting vertex
• the next two points are control points for the first segment of the curve
• for each additional segment of the curve, you must add another vertex and two control points
• the last point that you specify is the ending vertex for the curve
• you must always specify odd number of points, i.e. 3*n + 1, where n is the number of segments in the curve (4 points for one segment, 7 points for two segments, 10 points for three segments, etc.).

We are dealing here with Bézier curves, and if you aren't familiar with their definition and applications, you can start to learn here .

In the macro, this fragment of code: "AddCurve SafeArrayOfPoints:=pt", actually returns a shape object representing the Bézier curve segments that are based on the specified array of coordinates, pt.

In addition to producing a curve/shape, the macro displays the whole two-dimensional array in a message box, as shown in this example:

 

If you're new to adding and using macros in your workbook, follow these steps:

• Press Alt + F11 in Excel to open the VBA editor.
• Insert a new module by clicking "Insert" > "Module."
• Copy and paste the above code into the module.
• Modify the sheet name and data range as per your requirements.
• Close the VBA editor.
• Press Alt + F8 to open the macro dialog box.
• Select the macro and click "Run." Alternatively, you can add a control button to your worksheet and assign the macro to it.
  

If your setup is correct, the macro should display the shape and the array of numbers used for its creation. You can then format and manipulate settings of the shape per your specific needs.