Connecting to SQL Server database

[jere413]

I have a VBA function called GetBalance(parm1,parm2,parm3,parm4,parm5) that is used to connect to a SQL Server database, execute a query, and return a dollar amount, based on the parameters (type of account, fiscal year and period, balance type, etc.) The function allows users to build very customized reports against their financial data for monthly analysis and works as expected.

The VBA code has three basic parts:
1. All the stuff needed to connect to the SQL Server database
2. Stuff to run the query and format the result
3. Stuff to close the connection to the database.

My question is, if I have this function in (potentially) hundreds of cells on a worksheet, and want to recalculate the entire sheet, can it be done without the hundreds of connect/drop connect steps? Right now, the performance isn't terrible, but I'd like to know if I can improve it. To put it another way, can I do this:

1. Start Excel and present a form allowing the user to select the proper database, and enter login credentials.
2. Connect to the SQL Server database.
3. Allow the user to create, edit, calculate and recalculate worksheets, using my formula, but without the formula needing to do its own connect/drop connect each time it is used.
4. Close Excel and drop the db connection at that time.

 

[Jon von der Heyden]

You could use a private object variable for the connection. The function then would test the variable. If nothing then create the connection (and set object variable). If not nothing then use that connection object for the query.

Hard to be more specific though without seeing the function.