UDF autocomplete in excel

[Shrikant]

Hi All,
I have this query regarding UDF.

I have created sum UDFs for my routine work.
As you know when we start typing a function in a cell, excel (2007/2010) suggests a list of possible functions and then we select with arrow keys and hit tab to autocomplete upto the open parenthesis.

now when I created my UDF and tested it in the same workbook excel showed it in the list and I could select it and could autocomplete it as like in-built functions.

but when I saved it as an excel add-in and installed, I am now no longer be able to do this.
I can still insert the UDF with formula>insert function>user defined.
but I wanted it to be appeared in suggestions when I start typing.

Hope I've made myself clear.
Thanks in Advance.

 

[Shrikant]

 

[x68507]

If you're at the point in VBA programming that you're writing UDF Addins, then this should be a problem to follow. It's really straight forward once you have your UDF's written.

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

The registering/un-registering is done on the workbook_open and workbook_beforeclose events, but is still done on the premise of a macro enabled workbook. I added the following code to a module so that the current workbook that you have the UDF's defined in and the above Class Module saved in will automatically save and install the Addin.

'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

Hopefully this helps you (it took me over 24 hours to scrub this together and I thought I was going to pull my hair out over something that should be built into Excel).

 

[Shrikant]

appreciate your help, x68507.
I've been waiting so long for someone to answer.