Creating an Add-in

[avbanovice]

Hi, I'm wondering if the code below (which I found on this website, thanks!) can be made into an add-in so that it can be used as a user defined function in any worksheet. I was able to get the code working in my spreadsheet but when I tried to create an add-in I get a compile error - user-defined type not defined, it's refering to adoCN As ADODB.Connection. From what I researched, it's looks like a UDF needs to begin with Function and end with End Funtion. In this case, Sub SetUpConnection() is outside of that function so it doesn't seem to work.

Is it possible?

Hope that makes sense.

Thanks in advance for any advice!
______________________________________________________________
Option Explicit
Dim adoCN As ADODB.Connection
Dim strSQL As String
Const DatabasePath As String = "C:\database.mdb"

'Function argument descriptions
'LookupFieldName - the field you wish to search =
'LookupValue - the value in LookupFieldName you're searching for =
'ReturnField - the matching field containing the value you wish to return =
Public Function DBVLookUp(TableName As String, _
LookUpFieldName As String, _
LookupValue As String, _
ReturnField As String) As Variant
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection
Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
" FROM " & TableName & _
" WHERE " & LookUpFieldName & "='" & LookupValue & "';"
'If lookup value is a number then remove the two '

adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
If adoRS.BOF And adoRS.EOF Then
DBVLookUp = "Product not Found"
Else
DBVLookUp = adoRS.Fields(ReturnField).Value
End If
adoRS.Close
End Function

Sub SetUpConnection()
On Error GoTo ErrHandler
Set adoCN = New Connection
adoCN.Provider = "Microsoft.Jet.OLEDB.4.0" 'Change to 3.51 for Access 97
adoCN.ConnectionString = DatabasePath
adoCN.Open
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub