Macro recording is very useful when trying to find the Visual Basic methods and properties that you would like to use. If you do not know which properties and methods to use, activate macro recording and execute the procedure manually. Macro recording compiles your actions into Visual Basic code. There are, however, some restrictions to recording macros. The following cannot be recorded:

•Conditional branches

•Variable assignments

•Loop structures

•Custom forms

•Error handling

•Customizing the FlexPro user interface

To enhance your macros, you might want to edit the code that was recorded in your module.

Removing the Selection Property

Macros created through macro recording often depend on the selection you make. At the beginning of most recorded macros, you will see "Selection". Recorded macros use the Selection property to return the Selection object. In the following example, a line is added to a data set by entering the value 0 into the gray field in the editor beneath the last line of the data set.

Sub Macro1()

    ActiveDatabase.ActiveObject.Selection.ActiveValue = 0#

    ActiveDatabase.ActiveObject.Selection.Move fpDataSelectionDown

End Sub

Although this macro executes the task, it has the disadvantage that it only runs correctly if the data set is currently being displayed in the data editor and the entry field beneath the last line is selected. These two problems can be solved by editing the macro in such a way that it does not use the Selection object. Here is the revised macro:

Sub MyMacro()

    ActiveDatabase.ActiveObject.NumberOfRows = _

        ActiveDatabase.ActiveObject.NumberOfRows + 1

    ActiveDatabase.ActiveObject.Value(fpDataComponentAll, 1 _

        , ActiveDatabase.ActiveObject.NumberOfRows) = 0#

End Sub

The first statement adds a line to the active data set. The second statement uses the Range method to return a Range object that precisely represents the newly inserted value. By assigning the value 0 to the Value property of this Range object, the newly inserted value is initialized. For more information on how to use the Range object, go to Working with Data Sets.

Using With...End With

Macro statements that refer to the same object can be simplified by using the structure With...End With. For example, the following macro was recorded when the selected text was changed to red, a bold font and a 10-point font size.

Sub Macro1()

ActiveDatabase.ActiveObject.Selection.Font.Bold = True

ActiveDatabase.ActiveObject.Selection.Font.Size = 10

ActiveDatabase.ActiveObject.Selection.LineFormat.Color = fpColorRed

End Sub

The Selection property is used with every statement to return a Selection object. The macro can be simplified in such a way that the Selection property is used only once.

Sub MyMacro()

With ActiveDatabase.ActiveObject.Selection

    .Font.Bold = True

    .Font.Size = 10

    .LineFormat.Color = fpColorRed

End With

End Sub