Newbie question. Apologies in advance if it's too simplistic or in the wrong
forum.

I want to add an UDF to Excel. This UDF queries a SQL database.

Say the function name is 'MyFunc';
I want to be able to have (in a cell) =MyFunc("ABC") return some data from
the SQL database (for example here, say the Product Description for product
"ABC").

The user needs to select the database the first time he uses the workbook.

The UDF is called often in the worksheets of a given workbook so I would
prefer to open the connection once at the beginning and close it once at the
end instead of open/close each time the function is 'called' from a cell.

(After many tries) Here's what I did (simplified):
1. Created a XLA with the function.
2. Created a XLT with Workbook_Open event, something like this:
If DocumentProperties("MyDatabase") = NUL then
prompt for DatabaseName
store DatabaseName in DocumentProperties("MyDatabase")
End If
Open an ADO connection ("MyConn") to the Database specified in
DocumentProperties("MyDatabase")
End Sub
... and a Workbook_BeforeClose event:
MyConn.Close
Set MyConn = Nothing
End Sub
3. Things work fine: when I create a new workbook from the template it
prompts for the datatase and stores it in the workbook's properties... then
it creates and opens the connection fine ... so far so good.
4. Now the problem I have: In the XLA, when the function references the
connection ("MyConn") I get a compile error. Apparently because MyConn is
defined in the XLT and not in the XLA. I tried to set MyConn 'Public' but it
does not fix the issue.

My Question: Am I complicating things? Is there a better 'pattern' to use
here? Which one? Where is it documented? It seems to me this is all very
complicated for a simple task that someone else, surely, had to face before I
did!?!

I searched everywhere I could find before posting here. Any help/advice
would be appreciated.

Thanks

This is how I tackle situations like these:

1. The same way as you I create the XLA & XLT combinnation

2. But different to you I create the Connection in the xla as oppose
to xlt. So now both your function and your connection object are in
the same VBA project and there are no calling conflicts.
Further to this - I don't think it's a good idea to keep the
connection open all the time. What I do instead is I store a public
connection variable in one of the xla modules and it gets created only
one time but it gets closed (but not destroyed) once I don't need to
pull any data anymore. So I've got a function that checks if the
public connection variable is set (is not nothing), if it is set, then
i just open it up when needed. If it's not set (i.e., if it is
nothing) - i create the new one and open it up. Once done with pulling
the data I close it (as per above - I don't set it to nothing so that
next time i need it i could just re-open it up).
This way the connecting to the db is very fast but at the same time i
don't keep it open to risk corrupting the db nor beefing up the
traffic.

3. For the xla to establish connection to the db that you need (i.e.,
the db name stored in your xlt document properties (or .xls for that
matter)) you can have a function in xlt that calls (and passes the db
name as argument) a function from xla that in turn stores the
connection variable.
Remember - now you've got both the eventual function and the
connection in the same xla project so the only thing you need is your
xla to know which db to connect to and you do that by calling one
function (xla) with another (xlt) and passing the db name as
parameter.
For you to have xlt able to call xla functions you set a reference to
the xla project from your xlt project (VBA TollsReferencespick your
xla project here). And then you'll be able to call something like:

xlt:
Function ThisIsMyXLT_ProdDescripFunc() as string
ThisIsMyXLT_ProdDescripFunc =
ThisIsMyXLAProject.ThisIsMyXLAFunc(MydbVar)
End function


'For the xla you could write something like this (this is just a quick
and dirty snippet - it won't work and there might be some flaws in it
but you should get the idea)
xla:
Public ConnToDB as Object 'adjust as appropriate - i use late bind
Function ThisIsMyXLAFunc(MydbVar) as string
dim cn as object
If Not ConnToDB Is Nothing Then 'The variable already once
defined in this _
session - will reuse it

On Error GoTo CreateNewConnection:
set cn = ConnToDB
cn.Open
On Error GoTo 0
Else
'code here to create the new connection and store the
public ConnToDB variable for future reuse
End If

'''''''''''' here goes the code to set the ThisIsMyXLAFunc to
the necessary product description string using the cn connection


end function

Perhaps some of the above won't make sense straight away but it's
getting late here in Europe and I'm a bit tired...