Custom user function sheet recalculate problem

J

jdvuyk

Hi All,

I have a custom user function called in many cells in my spreadshee
much like -=getdata(ref1,ref2)- which works great! Within this functio
a call is made to an SQL database to retrieve one piece information an
return it to the spreadsheet. Althought it seems really quick for on
cell its obviously a major overhead for many cells. Currently 100
cell updates takes about 15 secs which is actually not bad when it
happening only once. However we will run large and complicate
spreadsheets with perhaps many times more data being refreshe
regularly in different ways.

What I want to achive is that if a cell has already retireved a valu
from the database (cell is not null value) that it does NOT re-retriev
the value when the sheet recalculates. It should just ditch out of th
custom function before the database call is made.

After some thought I tried to find an event for cell updates that
catch before each cell is updated but I cant find an effective lookin
one. I also tried to have the function send its own value to itself (
bit like -=getdata(ref1,ref2,selfref)- ) for checking but this jus
creates a circular reference and the system breaks.

Anyone have any ideas of how to get around this one?
Thanks
Jami
 
J

jeff

Hi,

I don't think there's an easy or fast solution to your
problem. You could use a set of hidden cells for each
cell retrieved and when you do the first database
retrieve, you also set this to True, so when it's
called again, it sees that it's already set and skips
out. But I'm not sure this is worth the time or the
effort - or whether it'll save you time in the long run.

(Obviously, these flag values would have to be cleared
either when you open or close the file so that when
you re-open, you're all set to go.)

jeff
 
Top