Add in confusion

[Blade Hunter]

Hi Guys, so I created a couple of UDF's and I want to roll them out in an addin.

I created a new workbook, added a module, pasted my 2 UDF's in there and saved as an XLAM (Excel 2007)

I then went to the add ins option in Excel 2007 and added it in. All was well so far.

When I went to use the UDF's, they don't come up in the suggested list as I type them and I end up with #NAME? so it is clearly not picking them up. Have I done something wrong?

Option Explicit
 
Public Function GetProdID(UPC As String)
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=removed;USER ID=removed;PASSWORD=removed"
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
Cmd.CommandText = "SELECT temp_raas_prod_no_from_dig ('95', '" & UPC & "', '') FROM dual"
Set RS = Cmd.Execute
GetProdID = RS.Fields(0).Value
End Function
 
Public Function GetUPC(ProdID As String)
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=removed;USER ID=removed;PASSWORD=removed"
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
Cmd.CommandText = "SELECT barcode FROM b_vras001 WHERE prod_no = '" & ProdID & "' AND client_key = '0000'"
Set RS = Cmd.Execute
GetProdID = RS.Fields(0).Value
End Function

Cheers

Dan

 

[Blade Hunter]

if you have it in your personal workbook, you have to use the whole name in the formula bar

=Personal.xlsb!Module1.TestFunction()

but if you put the function in the "ThisWorkbook" section, you can just use the name.

I have it in an addin (XLAM). Not ideal as it is but it works so it will do for now

 

[RoryA]

They should appear in the function wizard automatically, but if you want them in the dropdown you need to register them, I think.

 

[dk]

I don't suppose you have an "Option Private Module" statement at the top of the module do you? This would be one way of creating the behaviour you're describing....i.e. the function would work but won't appear in the dropdown when you enter the formula into a cell.

FYI, I just copied your code into a XLAM module in Excel 2010 and it worked fine - function appeared as I typed =Getp.... (it showed GETPIVOTDATA and GetProdID in the dropdown) although obviously it didn't actually return anything.

HTH
DK

 

[Blade Hunter]

That is somewhat odd, There is no option private . Thanks for testing though, must just be oddness on these machines .

 

[dk]

OK, that is strange. I tested on 2010. I'll try on 2007 tonight as you've got me curious now

 

[RoryA]

2010 treats addin UDFs differently from 2007. In 2010 they appear automatically in the dropdown; in 2007 they do not.