An Addin Example

In this section I discuss the basic steps involved in creating a useful add-in. The example uses the ChangeCase text conversion utility that I describe in Chapter 16.

The XLS version of this example is available at this book's Web site. You can create an add-in from this workbook.

Setting up the workbook

The workbook consists of one blank worksheet, a VBA module, and a UserForm. In addition, I added code to the ThisWorkbook object that creates a new menu item on the Tools menu.

ThisWorkbook object

The following code, located in the Code window for the ThisWorkbook object, is executed when the workbook (soon to be an add-in) is opened. This procedure creates a new menu item (Change Case of Text) on the Tools menu. (See Chapter 20 for details on working with menus.) This menu item executes the ChangeCase macro, which is listed in the next section.

Const MenuItemName = "Change Case of Te&xt..." Const MenuItemMacro = "ChangeCase"

Private Sub Workbook_Open() ' Adds a menu item to the Tools menu Dim NewItem As CommandBarControl Dim Msg As String ' Delete existing item just in case On Error Resume Next Application.CommandBars(l) _


' Set up error trapping On Error GoTo NoCanDo

' Create the new menu item

Set NewItem = Application.CommandBars(l) _ .Controls("Tools").Controls.Add

' Specify the Caption and OnAction properties NewItem.Caption = MenuItemName NewItem.OnAction = MenuItemMacro

' Add a separator bar before

the menu item

NewItem.BeginGroup = True

Exit Sub

' Error handler


Msg = "An error occurred."

& vbCrLf

Msg = Msg & MenultemName

Msg = Msg & " was added to

the Tools menu."

MsgBox Msg, vbCritical

End Sub

The following procedure is executed before the workbook is closed. This procedure removes the menu item from the Tools menu.

Private Sub Workbook_BeforeClose(Cancel As Boolean) ' Delete the menu item On Error Resume Next Application.CommandBars(l). _ Controls("Tools").Controls(MenuItemName).Delete End Sub

Module1 module

The VBA module contains a short macro named ChangeCase that serves as the entry point. This procedure makes sure that a range is selected. If so, the UserForm is displayed.




Exit if a range is not


If TypeName(Selection)

<> "Range" Then Exit Sub


Display the dialog box




Figure 22-2 shows UserForml. It consists of three OptionButtons, named OptionUpper, OptionLower, and OptionProper. These OptionButtons are inside a Frame control. In addition, the UserForm has a Cancel button (named CancelButton) and an OK button (named OKButton).

The code executed when the Cancel button is clicked shows up next. This procedure simply unloads the UserForm with no action:

Private Sub CancelButton_Click()

Unload UserForml End Sub

Figure 22-2:

The UserForm for the Change Case add-in.

Figure 22-2:

The UserForm for the Change Case add-in.

The code executed when the OK button is clicked follows. This code does all the work:

Private Sub OKButton_Click() Dim TextCells As Range Dim cell As Range

' Create an object with just text constants On Error Resume Next

Set TextCells = Selection.SpecialCells(xlConstants, _ xlTextValues)

' Turn off screen updating

Application.ScreenUpdating = False

' Uppercase

If OptionUpper Then

For Each cell In TextCells cell.Value = UCase(cell.Value) Next cell End If ' Lowercase

If OptionLower Then

For Each cell In TextCells cell.Value = LCase(cell.Value) Next cell End If ' Proper case

If OptionProper Then

For Each cell In TextCells cell.Value = _

Application.WorksheetFunction.Proper(cell.Value) Next cell End If

' Unload the dialog box

Unload UserForml End Sub

This version of ChangeCase differs from the version in Chapter 16. For this example, I use the SpecialCells method to create an object variable consisting of only those cells in the selection that contain constants (not formulas) or text. This makes the routine run a bit faster if the selection contains lots of formula cells. See Chapter 14 for more information on this technique.

0 0

Post a comment