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