The idea is to create functions that users can easily place in any cell with whatever parameters they want, so we don't have a situation where we can read ahead and do multiple calculations in one swoop. I am not designing the spreadsheet for the users, I am designing the functions to give them the data they need when they need it. We considered local caching, but since that introduces the same problems as setting function volatility, I would prefer to go with that as it is cleaner and easier to maintain.
We don't repeatedly connect, BTW. And in fact everything but the recordset is reused (this might be dangerous, but so far it has been working).
Here is our function:
(The ConnectionOpen() function calls a connection manager that creates a new ADODB.connection if one doesn't exist and then holds it open in a similar manner that SqlGetString creates and keeps its own ADODB.Command).
Using debug.print, we know that the nearly entirety of the time spent rendering the result to the cell is spent in Set rs = DB_SqlGetString_Command.Execute. The command being called is readonly, and the SqlGetString function's purpose is clear. Can any of the objects in use have any parameters set to optimize for this limited type of use?
Bookmarks