Updating References to Procedures in Add-Ins

When you write and debug your add-in code, the workbook that contains the code probably has the filename extension .xls. When you compile the workbook, the add-in you create has the filename extension .xla.

Note

If your system isn't set up to display filename extensions, you won't be able to tell the difference between your source workbook and your add-in in the Open dialog box (File menu). To display filename extensions, open the Windows Explorer, and then click Options on the View menu. Next, clear the Hide MS-DOS File Extensions For File Types That Are Registered check box on the View tab.

Suppose you create a custom button that refers to a procedure in a workbook before it's compiled to an add-in. The button's OnAction property contains a reference to the original workbook filename — the one with the .xls extension. When you create an add-in from the workbook, the add-in has a different filename extension. Unless you update the button's OnAction property to refer to the procedure in the add-in, clicking the button will cause Microsoft Excel to open the original workbook and run the version of the procedure stored there, instead of calling the procedure in the add-in.

In most cases, you can update OnAction references using code similar to that shown in the following example. Remember to run this code in the workbook that contains custom buttons.

Sub ConvertRefs()
    For Each sh In ActiveWorkbook.Sheets
        If TypeName(sh) <> "Module" Then
            For Each b In sh.Buttons
                mystr = b.OnAction
                strpos = InStr(mystr, ".xls")
                If strpos <> 0 Then
                    l = Len(mystr)
                    b.OnAction = Left$(mystr, strpos - 1) & _
                        ".xla" & Right$(mystr, l - strpos - 3)
                End If
            Next b
        End If
    Next sh
End Sub