The problem must be very typical for those who write custom Excel
functions that retrieve data from a database server.
Even when a single call to such function works lightning fast (i.e.
recalculation of a single cell goes unnoticeable by a human user), the
fun begins when Excel tries to recalculate hundreds of such cells in a
practically random order...
One obvious solution is to program some sort of cache: at least, it
would take care about workbooks that need to display multiple fields of
each record. (For example, when workbook needs to show data from 100
records - e.g. 10 fields for each of those 100 records, - such cache
could generate only 100 databased queries instead of the worst case of
10x100=1000.) I think such workbooks are pretty typical (at least in
our workplace), and such performance improvements would be very
appreciated by users...
Probably, there are other, more sophisticated optimization tricks out
there...
However, my question is not how to program such cache or other tricks.
My question is: does anyone know about any VBA library/component that
could do something like abovementioned caching (and, perhaps, some
other smart optimizations) out-of-the-box?
In other words, before building it ourselves we would like to consider
buying it.
Thank you,
Yarik.
Bookmarks