Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

(cloning from question on SO) I created a function in VBA. I want Excel 2007 to show the Autocomplete when writing this function in the cell's Excel. Detail as enter image description here

How to use the autocomplete feature for VBA function in Excel 2007 with Excel Add-In (.xlam)?


In Excel 2010, the autocomplete works

In Excel 2007 with Excel Macro-Enabled Worksheet (.xlsm), the autocomplete works. The test file here.

But, in Excel 2007 with Excel Add-In (.xlam), the autocomplete NOT works. The test file here.

share|improve this question
Does it work if you put the abb() function inside a Module instead of in the code for Sheet1? – Jeremy Oct 29 '12 at 2:42

Based on Sean Cheshire’s link to Michaels’s answer:

“I have found an example over at JKP Application Development Services originally found by Laurent Longre. One caveat is explained below:

Disadvantage of this trick method, is that one is actually re-registering a function within the dll one uses, which might be used by any program.

This solution only registers/un-registers the UDF, but the user will still have to save the workbook as an .xlam and install the addin. I used the following code to automatically install the current workbook as an Excel addin (if you are going to be updating the addin, you'll need to add some error catching to determine if the addin is already installed).

'Saves current workbook as an .xlam file
sFile = Application.LibraryPath & "\" & "name_of_addin" & ".xlam"
ThisWorkbook.SaveAs sFile, 55
ThisWorkbook.IsAddin = True

'Adds temporary workbook

'Installs the addin
Set oAddin = AddIns.Add(sFile , False)
oAddin.Installed = True

'Closes temporary workbook
MsgBox ("Installation Successful. Please close Excel and restart.")

'Closes workbook without saving
Workbooks(sFirstFile).Close False

The link is still live.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.