I have written a custom Excel function in VBA which retrieves data from a
remote server. This function can take up to several seconds to run, depending
on a variety of factors. I have encountered some problematic behavior in the
process of inserting the function in cells at the point where the function
arguments are entered. Due to the slow response of the function, I can
observe that after the last argument is entered, the function runs once to
fill the results field in the lower-right corner of the Function Arguments
dialog box, then a second time to fill the formula result field in the
lower-left corner. Clicking OK after both fields have calculated closes the
dialog normally and the function appears to run a third time to place the
result in the target cell. The problem arises when OK is clicked before both
fields have finished calculating. In this case, Excel appears to start
queuing calls to the function in a loop until eventually it freezes or
aborts. I have been able to duplicate the behavior by creating a test
function which employs a timer to imitate the response time, so I know that
the issue is with the Insert Function process and not my function.
--
Christina L.