Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 6. February 2010 13:26

I am currently working on a project which extends an Excel VSTO add-in model I had developed a few months back. This is a joint project, and my add-in has to interact with a classic Excel worksheet model, which got me worried. The original model read data from Excel into a C# object, which handles the heavy-duty computation, and writes back results to a spreadsheet once it is done. The modified model has to proceed in 2 steps: perform a partial read of the inputs, compute some outputs, feed them into the worksheet model, read some results from that worksheet, resume computations and write out the final outputs.

The reason I was worried is that the spreadsheet I have to interact with is a bit slow, and I was concerned about a race condition type of problem. What if the add-in attempted to read data from the worksheet, before Excel had time to update the values in the worksheet?

In order to check whether there was a problem, I created a small test case. I first wrote a VBA function which was on purpose very slow:

Public Function SlowFunction(arg As String) As String

    WaitFor 10
    SlowFunction = arg

End Function

Public Function WaitFor(seconds As Integer)

    Dim startTime As Double
    startTime = timer
    Do While timer < startTime + seconds

End Function
The SlowFunction simply takes a string as input, calls the WaitFor function, which stays busy for a few seconds, and returns the input string after 10 seconds have elapsed. 

This allowed me to artificially create an extremely inefficient worksheet: when the input cell A1 is modified, the output cell A2 is updated only 10 seconds later.




Comment RSS