Animating Objects in Excel 2007 using the 3-D Animation Engine

Summary: Learn about the 3-D Animation Engine tool, which demonstrates the new OfficeArt rendering platform in Microsoft Office Excel 2007. Find out how you can use this tool to start programmatically manipulating three-dimensional shapes in the 2007 Microsoft Office system. (8 printed pages)

Jon Adams, Microsoft Corporation

Rod Chisholm, Microsoft Corporation

September 2007

Applies to: Microsoft Office Excel 2007

Contents

  • 3-D Animation Engine Overview

  • Animation Engine User Interface

  • Animating Objects

  • Parameter Table

  • Light Engine User Interface

  • Applying Lighting Effects in the Light Engine

  • Using Recorded Tracks to Generate Code

  • Conclusion

  • Additional Resources

Download3dAnimationEngine.xlsb.

3-D Animation Engine Overview

The 3-D Animation Engine tool demonstrates the new OfficeArt rendering platform in the 2007 Microsoft Office system. It animates any shape on the Microsoft Office Excel 2007 grid according to a parameters table. You can use the 3-D Animation Engine tool to add any shape, apply three-dimensional (3-D) animation, and change parameters to manipulate the object dynamically in real time.

One practical application is to use the 3-D Animation Engine tool as you would use a macro recorder to capture the properties that are used to programmatically modify objects in Excel 2007. By stepping through the Microsoft Visual Basic for Applications (VBA) code and viewing the recorded tracks, you can use the tool to start programmatically manipulating OfficeArt in the 2007 Office system.

Animation Engine User Interface

The 3-D Animation Engine download consists of an Excel Binary Workbook (.xlsb) file. You can save the file locally and open it in Office Excel 2007. The user interface, which is fairly straightforward, consists of the following four worksheets:

  • Instructions contains basic help information and is used for reference only.

  • Lights is the Light Engine, which you can use to apply shadowing and lighting effects to shapes or saved objects based on where the source lighting is located.

  • Motion is the starting point of the Animation Engine, where you insert the shape and add the 3-D effects.

  • Recorded Tracks contains the listing of properties from a saved object. In this worksheet, you can delete rows of data that are no longer needed.

Animating Objects

Animating an object involves inserting a shape onto a worksheet and then applying three-dimensional effects to it. The object cycles through coordinates that display texture and lighting effects. The 3-D Animation Engine is a workshop for animating such shapes. You can manipulate an object until you achieve the desired shape and then save it for property review, or you can move the object to the Light Engine for additional refinement and shadow work. Figure 1 shows the Animation Engine (Motion worksheet) and its controls.

Figure 1. Motion worksheet

Motion worksheet

Before you can animate a shape and apply shadow effects, you must insert the shape and prepare it to be set in motion.

To animate an object on the Motion worksheet

  1. On the Microsoft Office Fluent Ribbon, click the Insert tab, and in the Illustrations section, click Shapes.

  2. Select the shape that you want to add to the worksheet.

    The Parameter table contains default settings, which you can adjust as needed.

  3. Click Animate!

    Changes to the Parameter table have real-time effects. You can view how the object is manipulated as you change values.

  4. To step through the animation spectrum, select the Step Run? check box, and then click Animate! for each movement of the object.

  5. To change the direction of the object animation, click Reverse.

  6. When you finish manipulating the shape, you can either save it or export it to the Light Engine by clicking Export To Light.

After you save a shape, you can view the specific settings and parameters for that shape on the Recorded Tracks worksheet. This information can be helpful when you are applying settings for a programmatic solution.

NoteNote

When you work with multiple shapes, you can add more columns to the Parameter table. First add the shape, and then enter numbers in the column to the right of Column2. The new columns appear as Column3, Column4, and so on. The parameters in the new columns apply in the same order in which the objects were added.

Parameter Table

The Parameter table, shown in Figure 2, contains the basic properties that enable the object to be animated. As mentioned earlier, you can adjust these properties to achieve real-time visual effects. The Parameter table is located in the upper-left corner of the Motion worksheet. It starts with a default set of properties that can rotate and animate any imported object, and you can change parameters dynamically.

Figure 2. Parameter table on the Motion worksheet

Parameter table on the Motion worksheet

Table 1 describes the properties in the Parameter table and their effects on the active object. Many of the parameter names align directly to the shapes object model, and many depend on the shape.ThreeD property (perspective through extrusion). These parameters give you an idea of the functionality of the object model as you program against OfficeArt objects.

Table 1. Properties used in the 3-D Animation engine

Property Name

Description

RotateX

Speed of rotation around the x-axis.

RotateY

Speed of rotation around the y-axis.

RotateZ

Speed of rotation around the z-axis.

RotateHorizontal

Speed of rotation around the horizontal axis.

RotateVertical

Speed of rotation around the vertical axis.

MoveX

Speed at which the shape will move across the x-axis.

MoveY

Speed at which the shape will move across the y-axis.

Perspective

Camera (or point-of-view) depth perspective. To move between camera angles, set the parameter to Cycle. To turn the perspective off, set the parameter to Off, or select a value from 1 to 120 for field of view angle.

Depth

3-D depth of the object. Setting the parameter to Cycle rotates through various depths, and setting it to a fixed number sets the object to that depth.

BevelDepth

Equivalent to Bevel Height in the Format Shape dialog box. Changing it causes the bevel to extrude farther. Using the keyword Cycle causes BevelDepth to cycle through varying values.

BevelInset

Equivalent to Bevel Width in the Format Shape dialog box. Changing it causes the width of the bevel to change; smaller values create a cone effect. Using the keyword Cycle causes BevelInset to cycle through varying values.

BevelType

Maps directly to the BevelType enumeration on the ThreeD object. To locate the equivalent in the Format Shape dialog box, on the 3d Format tab, under Bevel, click the button next to Top or Bottom. Using the keyword Cycle cycles through the different bevel types.

Extrustion

Maps directly to the ThreeD.PresetExtrusionDirection property and returns the direction of the extrusion's sweep path away from the extruded shape (the front face of the extrusion). Visually it appears to change the camera angle. Changing to the Cycle keyword cycles through all known extrusion presets that are referenced in MsoPresetExtrusionDirection enumeration.

Adjustments

Maps to the adjustment values of some objects, such as stars or callouts, that have yellow adjustment handles. Most range from -1 to 1, although each shape can have a different range. Using the keyword Cycle causes the first adjustment handle to cycle from -1 to 1.

Freeze Shapes

To have the Animation Engine ignore an object, increase this number for the object and set its z-order so that it is on top of all other objects.

NonMoving

The number of non-animated OfficeArt objects on the worksheet. To have the Animation Engine ignore an object, increase this number by one and set the z-order of the object so that it is on top of all other objects. You must include comments, buttons, and charts, or they will be animated by the settings in the Parameter table.

Define Range

The active range that will contain the animated OfficeArt.

# of Snapshots

The current number of objects that are stored on the Recorded Tracks worksheet.

Light Engine User Interface

You can use the Light Engine to further analyze a shape that was designed in the Animation Engine and see how light and shadows change its appearance. The Light Engine is located on the Lights worksheet. You can export a shape from the Animation Engine and simulate light and shadows based on where you drag the light source.

Figure 3 shows the Light Engine (Lights worksheet) and its controls.

Figure 3. Lights worksheet

Lights worksheet

When you open the Lights worksheet, the exported object and a sun appear on the worksheet. The sun represents the light source. The worksheet contains controls around the main working area. On the left side is a column of suns. Each sun represents a type of light source that can shine on the center object.

You can change the color of the object by clicking one of the colored circles or lines on the right side of the worksheet. The plus (+) sign and minus (-) sign on the right side control the tint and shade of the current color. You can also use the import image button located on the far right side to browse to an image and have it appear on the current object.

NoteNote

You can have either a color or an image appear on the object at the same time, but not both. Switching back to a color removes the image; the image must then be reimported if it is needed again.

At the top of the worksheet are blue boxes, which you can use to change the material of the object from glass to stone to wireframe. The plus (+) sign and minus (-) sign let you control shape transparency.

The blue buttons at the bottom of the worksheet control shadows. If the image is casting a shadow based on the light source, you can change the color and size of the shadow.

The Lights worksheet also contains the following buttons:

  • Shed Light recalculates the angle of shadow and reflected light based on the current position of the object and the sun (light source).

  • Export copies the object and the light settings to the Animation Engine on the Motion worksheet.

  • Snap saves the object and lists the properties on the Recorded Tracks worksheet.

  • Restore restores the object to its original configuration, undoing any changes that were applied.

Applying Lighting Effects in the Light Engine

Follow these steps to export a shape from the Motion worksheet, or Animation Engine, and apply and adjust lighting and shadows on the Lights worksheet, or Light Engine.

To adjust lighting and shadows on the Lights worksheet

  1. When you are satisfied with the shape on the Motion worksheet, click Export To Light.

    When you open the Lights worksheet, the exported object and a sun appear on the worksheet. You can move the object and the sun around on the worksheet.

  2. To see how the source lighting affects the object, click Shed Light.

  3. On the left side of the worksheet, click one of the suns to adjust the way the light reflects off the object.

  4. Click one of the controls at the top to apply that texture to the object.

  5. Click one of the color controls on the right side to apply the color that you want.

  6. To cast a shadow effect on the object, click one of the shadow controls at the bottom.

  7. When you are finished, you can view the properties by clicking Snap and renaming the object.

    The properties are listed on the Recorded Tracks worksheet under the name that you gave the shape.

  8. If additional animation is needed, you can export the object back into the Motion worksheet by clicking Export.

Using Recorded Tracks to Generate Code

After you save the shape, and the properties are available on the Recorded Tracks worksheet, you can generate sample code that retrieves and sets properties from the recorded shape.

To develop custom code based on settings in the Recorded Tracks worksheet

  1. Press ALT+F11 to open the Visual Basic Editor.

  2. In Office Excel 2007, ensure that the Developer tab is visible on the Office Fluent Ribbon:

    1. Click the Microsoft Office Button, and then click Excel Options.

    2. Click Popular, and then select the Show Developer tab in the Ribbon check box.

  3. In VBAproject(3dAnimationEngine_Final.xlsb), double-click Module2.

    The first function in the Code window is the sub procedure writeMyCode, which is shown in the following example. This code generates sample code in the Immediate window based on settings from the Recorded Tracks worksheet.

    Sub writeMyCode()
        Dim Alist As ListObject
        Dim col As ListColumn
        Dim prop As String
    
       ' Location of the list, column headers must exactly match object model 
       ' properties and methods of the Shape object.
        Set Alist = Sheet2.ListObjects(1)
    
        For Each col In Alist.ListColumns
            prop = Intersect(Alist.HeaderRowRange, col.Range)
    
           ' Prints code that can be used to save object
           ' properties from a list.
            Debug.Print "Intersect(row, AList.ListColumns(""" & prop & """).Range).value = ." & Intersect(Alist.HeaderRowRange, col.Range)
    
           ' Prints code that can be used to restore object 
           ' properties from a list.
            Debug.Print "." & Intersect(Alist.HeaderRowRange, col.Range) & " = Intersect(row, AList.ListColumns(""" & prop & """).Range).value"
        Next col
    End Sub
    
  4. Click in the procedure, and press F5 to run the code.

You must explicitly call the functions in VBA. Each column in the Recorded Tracks worksheet relates directly to an object model property of the Shapes object.

To add, store, and return more shape properties, just modify the table, rerun this code, and paste the results from the Immediate window into the application. You can then use the generated code from the function in the writeValues or getValues procedure, or you can paste it into any existing method to programmatically retrieve and set all the values stored in the table.

To create a shape by using stored values in the Recorded Tracks workbook, use the following VBA code, adding the generated code from the Immediate window in the line that states "Add output from writeMyCode Here."

Sub writeValues(ListRow)
    On Error Resume Next
    Dim Alist As ListObject
    Dim Row As Range
   ' This should point to the list object defined in "writeMyCode"
   ' that contains all the shape properties.
    Set Alist = Sheet2.ListObjects(1)
    Set Row = Alist.ListRows(ListRow).Range
    
    With ActiveSheet.Shapes.AddShape(1, 10, 10, 10, 10)
                ' Add output from writeMyCode here.
    End With    
End Sub

Conclusion

When you are working programmatically with OfficeArt objects, the 3-D Animation Engine tool can provide you with some of the properties that are necessary to animate 3-D objects. You can take an object and change the properties to represent a shape that has the 3-D and shadow effects that you want. By stepping through the VBA code and viewing the recorded tracks, you can use the 3-D Animation Engine tool like a macro recorder to help you start programmatically manipulating OfficeArt in the 2007 Office system.

Additional Resources

To learn more about Office Excel 2007, see the following resources: