3

I have created an Add-In and when I go to use it in the expressions box the autocomplete doesn't work. I do see them when I click the functions 'f' button under user defined functions. I would just like the AutoComplete to work with them so I don't have to memorize their names or have to click the functions 'f' button each time.

Mike Cheel
  • 12,626
  • 10
  • 72
  • 101

2 Answers2

1

AFAIK there is no way (unfortunately) in current Excel versions including Excel 2010 to make a UDF use Autocomplete. There are ways to add argument descriptions and help for the function wizard.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • I think you have confirmed what I have been finding online. – Mike Cheel Nov 10 '10 at 12:34
  • http://blogs.msdn.com/b/excel/archive/2005/10/19/482826.aspx -- this link says that UDFs can show up in formula auto complete. BTW any details on "There are ways to add argument descriptions and help for the function wizard" - for COM based automation addins? Do we need to add information to the Type libraries? – A9S6 Dec 20 '10 at 14:17
1

Although this is an old thread, there seems to be very little solutions out there. 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

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

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
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
Michael
  • 2,158
  • 1
  • 21
  • 26