Converting a Recorded VBA Macro to Visual Basic in VSTO

In this section, you will record a simple macro in VBA and then convert the code to Visual Basic 2005. This example is similar to the one in

Chapter 6 in that you will record a macro that sets the selection to bold and italic. However, there isn't any toggle enumeration available in Excel that is similar to Word's wdToggle enumeration. Using Excel, you must write additional code to apply and remove the bold and italic formatting.

1. Open an Excel workbook outside Visual Studio, and type some text in cells A2 through B3.

2. Select the text within range A2:B3, and then click Tools, point to Macros, and click Record New Macro.

3. In the Record Macro dialog box, type Boldltalic in the Macro Name box, and then click OK.

4. On the Formatting toolbar, click the Bold button, and then click the Italic button.

5. On the Stop Recording toolbar, click Stop Recording.

6. Open the VBE by pressing ALT+F11.

Listing 7.4 shows the VBA code you should see in the VBE.

Listing 7.4. Using the VBA macro recorder in Excel ' VBA

Sub BoldItalic() ' BoldItalic Macro

' Macro recorded by Kathleen McGrath

Selection.Font.Bold = True Selection.Font.Italic = True

End Sub

Unlike Word's macro recorder, the Excel macro recorder sets the Bold and Italic property of the selection to True rather than to an enumeration. If you want to toggle the formatting, you need to modify the code.

Additionally, to bring this code into Visual Basic 2005, you must fully qualify the Selection object because you cannot access it directly. In Listing 7.5, you create a variable named Selection and set it to Application.Selection. If there is mixed formatting, bold and italic are applied to the entire range.

Listing 7.5. Modifying a recorded macro in Excel to work in VSTO ' Visual Basic 2005

Sub BoldItalic()

Dim Selection As Excel.Range = Me.Application.Selection

If Selection.Font.Bold = True And _ Selection.Font.Italic = True Then

Selection.Font.Bold = False Selection.Font.Italic = False


Selection.Font.Bold = True Selection.Font.Italic = True End If

End Sub

If you want to associate this code with a toolbar button, you must write additional code to programmatically add the button to the toolbar. Then you can call the BoldItalic method in the button's Click event handler. For more information about adding buttons to toolbars and menus, see Chapter 8.

+1 0

Post a comment