How to get custom functions in Excel add-ins (Excel 2007) for working with autocomplete?

Question

I created an add-in, and when I use it in the expression field, autocomplete does not work. I see them when I press the "f" function button according to user-defined functions. I would just like AutoComplete to work with them, so I don’t need to remember their names or press the "f" button every time.

Answer 1

Although this is an old thread, there seem to be very few solutions. I found an example in the JKP Application Development Services, originally found by Laurent Longre. The reservation is explained below:

The disadvantage of this method is that in fact there is a re-registration of the function inside the used dll, which can be used by any program

http://www.jkp-ads.com/Articles/RegisterUDF01.asp

This solution only registers / unregisters the UDF, but the user will still have to save the book as .xlam and install addin. I used the following code to automatically install the current workbook as an Excel add-on (if you are going to update the add, you need to add some error to determine if the add is added).

'Saves current workbook as an .xlam file
sFile = Application.LibraryPath & "\" & "name_of_addin" & ".xlam"
ThisWorkbook.SaveAs sFile, 55
ThisWorkbook.IsAddin = True
'Adds temporary workbook
Workbooks.Add
'Installs the addin
Set oAddin = AddIns.Add(sFile , False)
oAddin.Installed = True
'Closes temporary workbook
Workbooks(Workbooks.Count).Close
MsgBox ("Installation Successful.  Please close Excel and restart.")
'Closes workbook without saving
Workbooks(sFirstFile).Close False

Answer 2

AFAIK ( ) Excel, Excel 2010, UDF Autocomplete. .