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

 

[VoG]

This may help http://www.fontstuff.com/vba/vbatut08.htm

 

[Blade Hunter]

Thanks Peter, that's not working for me though, I don't want to add a reference to it in another macro, I want other people to be able to add the addin on their own Excel and use the UDF's out of it on any sheet.

Cheers

Dan

 

[Blade Hunter]

Sweet, I worked it out. It wasn't working because I had Public Function instead of Function (I would have though public would be what I wanted but oh well)

Thanks again for your help.

Dan

 

[Blade Hunter]

Second thoughts no, this didn't fix it. Still not working Any ideas? I have the code for a couple of UDF's and I just want to be able to use them from multiple workbooks without having to copy the code from sheet to sheet. Do I need some sort of a class module?

Cheers

Dan

 

[RoryA]

Did you add the required reference (some version of ActiveX Data Objects) to your add-in workbook before you saved it? If not, your code won't compile which may be the problem.

 

[Blade Hunter]

Hi Rorya, the code runs when it is a module in a workbook, but it only runs in that workbook. Syntactically it is fine.

This line was wrong in my original post but I have since fixed it:

GetProdID = RS.Fields(0).Value

to

GetUPC = RS.Fields(0).Value

However it didn't fix my problem

Cheers

 

[Blade Hunter]

This is seriously doing my head in now. Is an Addin the correct way to distribute a couple of UDF's so that it is available in ANY workbook on a whole bunch of machines?

Do I need to sign the code perhaps?

 

[Blade Hunter]

OK, The plot thickens, I CAN use the UDF's, they DON'T however appear in the drop down as you begin typing.

This will do though, I have to get this thing out there.

Thanks all

 

[RSXchin]

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.