Excel VBA Programming For Dummies
Book image
Explore Book Buy On Amazon
This Excel add-in example discusses the basic steps involved in creating a useful add-in which you can use to package your VBA procedures. This Excel VBA example is based on the Change Case text conversion utility.

Setting up the Excel workbook

The Excel workbook consists of one blank worksheet, a VBA module, and a UserForm.

The original version of the utility includes options for uppercase, lowercase, and proper case. The add-in version includes two options to the UserForm so it has the same options as the built-in tool in Microsoft Word:

  • Sentence Case: Makes the first letter uppercase and all other letters lowercase.
  • Toggle Case: All uppercase characters are converted to lowercase, and vice versa.
This image shows UserForm1. The five OptionButton controls are inside a Frame control. In addition, the UserForm has a Cancel button (named CancelButton) and an OK button (named OKButton).

Change Case Excel add-in The UserForm for the Change Case add-in.

The code executed when the Cancel button is clicked is very simple. This procedure unloads the UserForm with no action: 

Private Sub CancelButton_Click()
  Unload UserForm1
End Sub
The code that's 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
  Dim Text As String
  Dim i As Long
'  Create an object with just text constants
  On Error Resume Next
  Set TextCells = Selection.SpecialCells(xlConstants, xlTextValues)
'  Turn off screen updating
  Application.ScreenUpdating = False
'  Loop through the cells
  For Each cell In TextCells
    Text = cell.Value
    Select Case True
    Case OptionLower 'lowercase
      cell.Value = LCase(cell.Value)
    Case OptionUpper 'UPPERCASE
      cell.Value = UCase(cell.Value)
    Case OptionProper 'Proper Case
      cell.Value = WorksheetFunction.Proper(cell.Value)
    Case OptionSentence 'Sentence case
      Text = UCase(Left(cell.Value, 1))
      Text = Text & LCase(Mid(cell.Value, 2, Len(cell.Value)))
      cell.Value = Text
    Case OptionToggle 'tOGGLE CASE
      For i = 1 To Len(Text)
      If Mid(Text, i, 1) Like "[A-Z]" Then
        Mid(Text, i, 1) = LCase(Mid(Text, i, 1))
      Else
        Mid(Text, i, 1) = UCase(Mid(Text, i, 1))
      End If
      Next i
      cell.Value = Text
    End Select
  Next
'  Unload the dialog box
  Unload UserForm1
End Sub

Testing the Excel workbook

Test the Excel add-in before converting this workbook. To simulate what happens when the workbook is an add-in, you should test the workbook when a different Excel workbook is active. Because an Excel add-in is never the active sheet or workbook, testing it when a different workbook is open may help you identify some potential errors.
  1. Open a new workbook and enter information in some cells.

    For testing purposes, enter various types of information, including text, values, and formulas. Or just open an existing workbook and use it for your tests. Remember that any changes to the workbook cannot be undone, so you may want to use a copy.

  2. Select one or more cells (or entire rows and columns).
  3. Execute the ChangeCase macro by choosing the new Change Case command from your Cell (or Row or Column) shortcut menu.

If the Change Case command doesn’t appear on your shortcut menu, the most likely reason is that you didn’t enable macros when you opened the change case.xlsm workbook. Close the workbook and then reopen it — and make sure that you enable macros.

Adding descriptive information to your Excel add-in

Although not required, it’s considered to be a best practice to enter a description of your Excel add-in. Follow these steps to add a description:
  1. Activate the change case.xlsm workbook.
  2. Choose File → Info, and click Show All Properties at the bottom right.

    Excel expands the Properties list.

  3. Enter a title for the add-in in the Title field.

    This text appears in the list of add-ins in the Add-Ins dialog box. For this example, enter Change Case.

  4. In the Comments field, enter a description.

    This information appears at the bottom of the Add-Ins dialog box when the add-in is selected. For this example, enter

  5. Changes the case of text in selected cells.

    Access this utility by using the shortcut menu.

This image shows the Properties section with the Title and Comments fields filled out.

Excel add-in properties Use the Properties section to enter descriptive information about your add-in.

Protecting the VBA code

If you want to add a password to prevent others from viewing the VBA code, follow these steps:
  1. Activate the VBE, and select the change case.xlsm workbook in the Project window.
  2. Choose Tools → VBAProject Properties, and click the Protection tab on the dialog box that appears.
  3. Select the Lock Project for Viewing check box, and enter a password (twice).
  4. Click OK.
  5. Save the workbook by choosing File → Save in the VBE or by going back to the Excel window and choosing File → Save.

Creating the Excel add-in

At this point, you've tested the change case.xlsm file, and it's working correctly. The next step is creating the add-in. Follow these steps:
  1. If needed, reactivate Excel.
  2. Activate the change case.xlsm workbook, and choose File → Save As → Browse.

    Excel displays its Save As dialog box.

  3. From the Save as Type drop-down menu, choose Add-In (*.xlam).
  4. Specify the location, and click Save. A new add-in file (with an .xlam extension) is created, and the original XLSM version remains open.

Opening the Excel add-in

To avoid confusion, close the XLSM workbook before opening the add-in that you created from that workbook.

Open the add-in by following these steps:

  1. Choose Developer → Add-Ins → Add-Ins (or press Alt+TI).

    Excel displays the Add-Ins dialog box.

  2. Click the Browse button.
  3. Locate and select the add-in you just created.
  4. Click OK to close the Browse dialog box.

    After you find your new add-in, the Add-Ins dialog box lists the add-in. As shown below, the Add-Ins dialog box also displays the descriptive information you provided in the Document Properties panel.

    Excel add-in dialog box The Add-Ins dialog box has the new add-in selected.
  5. Make sure that your new add-in is selected in the Add-Ins dialog box.
  6. Click OK to close the dialog box.

    Excel opens the add-in. Now you can use it with all your workbooks. As long as it remains selected in the Add-Ins dialog box, the add-in opens every time you start Excel.

Distributing the Excel add-in

If you’re in a generous mood, you can distribute this add-in to other Excel users simply by giving them a copy of the XLAM file. (They don’t need the XLSM version.) When they open the add-in, the new Change Case command appears on the shortcut menu when they select a range, one or more rows, or one or more columns. If you lock the VBA project with a password, others cannot view your macro code unless they know the password.

Modifying the Excel add-in

An add-in can be edited just like any other workbook. You can edit the XLAM file directly (you don’t need to work with the original XLSM version) by following these steps:
  1. Open your XLAM file, if it's not already open.
  2. Activate the VBE.Double-click the project’s name in the Project window.

    If you protected the code, you’re prompted for the password.

  3. Enter your password, and click OK.
  4. Make your changes to the code.
  5. Save the file by choosing File → Save.

If you create an add-in that stores information in a worksheet, you must set the workbook’s IsAddIn property to False to view the workbook. You do this in the Properties window when the ThisWorkbook object is selected . After you’ve made your changes to the workbook, make sure that you set the IsAddIn property back to True before you save the file.

undoing Excel add-in Making an add-in not an add-in.

About This Article

This article is from the book:

About the book author:

Dick Kusleika has been helping users get the most out of Microsoft Office products for more than 25 years through online forums, blogging, books, and conferences.

This article can be found in the category: