I have UDF's which can be placed thousands of times in large spreadsheets. Internally, the connect to our SQL Server DB - typically, they call custom SQL functions which do some calculations and return a result, which in turn return the value to the current cell. Each function takes about 1/10 of a second to execute. Which seems fast, but multiply it over thousands of cells and you are in trouble.

My client lives in fear that anything he touches will trigger a workbook recalculation, and he can never predict when that will happen.

I know the SQL functions run very quickly, and running equivalent queries from a SQL client on the same machine over thousands of rows returns near-instantaneous results. My research so far into ADO via Excel VBA is that it's just plain horribly slow.

I have done everything I can think of to optimize things from the VBA side (for example, persistent, cached connections). Has anyone solved a similar problem?

Thanks,

Carlo