Building Surface Charts and Controlling Orientation with Excel VBA

Let us now look at an example of creating a surface chart, which naturally has a three-dimensional orientation. As the data source, we will once again use the report of the computer club network’s performance. The surface will be created automatically from the table when the workbook is opened:

• the x-axis will represent the names of the clubs,
• the y-axis will represent the months,
• the z-axis will represent the clubs’ revenues.

Clearly, the clarity of a surface chart greatly depends on the side and angle from which the user views it. Therefore, in addition to programming the chart creation process, we will place two control elements (lists) on the worksheet.

• The first list is intended for changing the elevation angle from which the surface is viewed. Set its Name property to Elev.
• The second list is intended for rotating the surface around the z-axis. Set its Name property to Rotat.

When the worksheet is activated, the lists are populated with permissible angles. In the ThisWorkbook standard module and the Vedomost worksheet module, enter the code for the corresponding procedures.

As another example, let us consider a surface chart where the rotation takes place in three dimensions.

To implement this example, perform the following steps:

1. Prepare the data range for the chart. Enter values into cells A1:A16 and B1:L1 for the x-axis (interval: –1 to 6.5, step: 0.5) and y-axis (interval: –1 to 1.2, step: 0.2).

In cell B2, enter the formula for z = cos(x)cos(y)sin(xy):

=COS($A2)*COS(B$1)*SIN($A2*B$1)

Copy this formula across the range B2:L16.

• Select the range A1:L16 and build a surface chart using Excel’s built-in tools: go to the Insert tab on the ribbon, in the Charts group click Other Charts, and choose the type Surface.
• Format the resulting surface using the features of the contextual Chart Tools tabs.
• Place three CommandButton controls on the worksheet and set their Caption properties as follows:
  • ROTATION (for CommandButton1),
  • TURN (for CommandButton2),
  • PERSPECTIVE (for CommandButton3).
• Enter in a standard module and in the worksheet module Surface_Rotation the code for the procedures that support surface rotation in three dimensions.