Recording Macros

Up to this point, all chapter projects have been preformatted with no specific instructions on how it was done. I assume you are an experienced Excel user and are comfortable with formatting worksheets; however, there will be occasions when you need to create new formatted worksheets programmatically. You could write VBA code that formats the worksheet as you want, but this is often a tedious exercise and is not really necessary. You will know how you want the worksheet formatted; you just don't want it done until the user has reached a certain stage in your program. This is one example of when recording a macro is very handy. The basic steps for recording a macro are as follows:

1. Turn on Excel's macro recorder.

2. Format the worksheet as desired.

3. Stop the recorder.

4. Proceed to the VBA IDE and find the VBA code you just recorded.

5. Clean the recorded code for readability and add it to your program.

Another situation in which recording macros is useful is when you need to learn how to use a particular VBA function. If you can't find what you need in the online help or get your code to run correctly, simply record a macro that uses the desired function of the Excel application. Of course, you must know how to perform the same task within the Excel application that you are trying to add to your VBA code. Once the task is recorded, return to the VBA IDE and examine the recorded VBA code.

To begin recording a macro, in the Excel application select Tools, Macros, and Record New Macro, as shown in Figure 4.8. You can also select the Record Macro button on the Visual Basic toolbar.

The Record Macro button

The Record Macro button

Starting the Macro Recorder.

Starting the Macro Recorder.

A dialog box will appear, as shown in Figure 4.9, asking you to input a name for your macro, where you want to store the code (a new workbook, the current workbook, or a personal macro workbook), and for a description of the macro. You can enter in new values or use the default. I recommend at least changing the name of the macro to something meaningful. Store the macro in whatever workbook you want, but keep in mind the macro will be saved with the workbook you choose, and will only be available when this workbook is open.

Naming and storing the macro.

Naming and storing the macro.

After selecting the name and location of the macro, a small toolbar with a small square button will appear, as shown in Figure 4.10. After you are finished recording the macro, click this button to stop the recorder. Until you click the stop button, every action you perform in the Excel application is recorded as VBA code.

The Stop Recording button.

After stopping the recorder, you can find the new VBA code stored in a standard module in the previously designated project. The module and code window that results from recording a macro that formats cells A1, B1, and C1 for the Math Game is shown in Figure 4.11.

To record this macro, I follow the procedure above, and then format the cells before stopping the recorder. Specific tasks carried out in the Excel application while the recorder was on were: adding the text to the cells, specifying font size, bold, centered text, word wrapped text, a border, row height, and column widths. The code, exactly as recorded, is as follows:

Sub MathGameFormat() ' MathGameFormat Macro

' Macro recorded 11/16/2004 by Duane Birnbaum

Module added by -macro recorder

The VBA IDE showing a recorded macro.

Module added by -macro recorder

The VBA IDE showing a recorded macro.

"Question" "Answer"

"Correct Answer"


Range("A1").Select ActiveCell.FormulaR1C1 Range("B1").Select ActiveCell.FormulaR1C1 Range("C1").Select ActiveCell.FormulaR1C1 Range("A1:C1").Select Selection.Font.Bold With Selection.Font .Name = "Arial" .Size = 12

.Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False

.Underline = xlUnderlineStyleNone .Colorlndex = xlAutomatic End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeLeft).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick .Colorlndex = xlAutomatic End With

Selection.Borders(xlEdgeRight).LineStyle = xlNone Selection.Borders(xlInsideVertical).LineStyle = xlNone Rows("1:1").RowHeight = 32.25 Columns("A:A").ColumnWidth = 10.71 Columns("B:B").ColumnWidth = 9 Columns("C:C").ColumnWidth = 10.86 With Selection

.HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .Addlndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With

Range("C1").Select With Selection

.HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = True .Orientation = 0 .Addlndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub

As you can see, recording just a few tasks will generate a considerable amount of code. (I even took care to minimize my worksheet cell selections knowing it would reduce the amount of recorded code.) Because of the volume of code generated by the macro recorder, I do not recommend recording many tasks at any one time. You want to be able to record small pieces, then clean up the recorded code and proceed to the next task.

Much of the recorded code can be eliminated by deleting the setting of default values and compressing multiple statements into one line of code. You will get better at this as you gain experience with VBA programming. The macro I just showed you can be quickly reduced to the following:

Sub MathGameFormat() ' Revised macro

Range("A1").Select ActiveCell.FormulaR1C1 = "Question" Range("B1").Select ActiveCell.FormulaR1C1 = "Answer" Range("C1").Select

ActiveCell.FormulaR1C1 = "Correct Answer" Range("A1:C1").Select Selection.HorizontalAlignment = xlCenter With Selection.Font .Bold = True .Name = "Arial" .Size = 12 End With

With Selection.Borders(xlEdgeBottom) .LineStyle = xlDouble .Weight = xlThick End With

Rows("1:1").RowHeight = 32.25 Columns("A:A").ColumnWidth = 10.71 Columns("B:B").ColumnWidth = 9 Columns("C:C").ColumnWidth = 10.86 Range("C1").Select Selection.WrapText = True End Sub

The macro is public by default and is contained inside a standard module.

The With/End With code structure is used to execute a series of statements on the same Excel object. This removes the requirement of constantly qualifying the object before setting one of its properties. The With/End With programming structure will be covered in Chapter 5.

To run a recorded macro in the Excel application, select Tools, Macro, Macros or press Alt+F8. A dialog box displaying a list of available macros will appear, as shown in Figure 4.12.

Selecting an available macro.

Selecting an available macro.

Select the macro you want and press the Run button to execute the code in the macro.

Any public procedure (recorded or not) stored in a standard or object module will appear in the list of available macros.

After recording the formatting of the worksheet cells A1 through C1, I record another manageable amount of formatting, clean up the code and paste it within the previously recorded procedure. After all recording is completed and the code is reduced, it can be copied to any sub procedure necessary to fulfill the algorithm for the program. For example, the recorded code may be needed inside the Click() event procedure of a Command Button control. Although the formatting macro is not a required part of the Math Game program, I have included the recorded macro (after editing) on the book's accompanying CD.

The macro-recording tool in Excel was really designed for non-programming users as a method to extend the capabilities of their spreadsheets and eliminate the tedium of repetitive tasks. As it turns out, the macro-recording tool can also serve the VBA programmer as a method of eliminating tedious programming tasks and learning how to carry out specific tasks in Excel with VBA code.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook

Post a comment