1

I have an Excel "project" that includes a .dll where I have written some complex statistical calculations called through VBA. I have done that for speed reasons. The calculations take about a second each. Since they are called through VBA it stalls Excel for the duration of the calculations and that is acceptable. (The choice of Excel is not mine but a result of the way a third party has chosen to deliver data)

But for the purpose of the project I need to have the results of the calculations turn up after not one second but after ten. I could either expand the calculations for greater accuracy or simply include a pause in the code. But since it is done via VBA it stalls the whole project for all ten seconds and that is not acceptable.

I have looked into ExcelDNA since it avoids VBA completely and might make it possible to do ALL that is done via VBA with ExcelDNA or existing build in functions. I have modified this example for testing:

https://grumpyop.wordpress.com/2009/11/25/a-third-way-dna/

and included a simple Thread.Sleep(10000); to the code to simulate the pause. But that ALSO stalls Excel for the duration of the calculation.

Is there a way to include a pause in functions that doesn´t make Excel wait for the result but where the result is "pushed" to the cell/the cell "subscribes" to the result? Can it be done via ExcelDNA, XLL or via a third solution? I would prefer a soution where I can use C or very lightly modified C since all the statistical functions are written in C.

blagstar
  • 23
  • 4
  • The "usual" way, if Excel were like a UI framework, would be to shift the work to a worker thread (e.g. via `ThreadPool.QueueUserWorkItem`, `Task.Factory.StartNew`, `Task.Run`) and call back on the original thread when you're done (e.g. using some `synchronizationContext.Post`). This approach might work if ExcelDNA sets up a suitable `SynchronizationContext` (but I suspect that it would not, since Excel is *not* a UI framework). – stakx - no longer contributing Apr 20 '15 at 06:27
  • Probably the answer in this [SO question](http://stackoverflow.com/questions/2319683/vba-macro-on-timer-style-to-run-code-every-set-number-of-seconds-i-e-120-secon) or [this](http://stackoverflow.com/questions/2341762/excel-vba-application-ontime-i-think-its-a-bad-idea-to-use-this-thoughts-eit) will help you (adding some seconds to `Application.OnTime` arguments) – PatricK Apr 20 '15 at 06:57
  • Maybe use the WAIT method? http://stackoverflow.com/questions/1544526/how-to-pause-for-specific-amount-of-time – Shauno_88 Apr 20 '15 at 07:13

1 Answers1

1

You need to make your function asynchronous.

Excel supports this from Excel 2010. https://msdn.microsoft.com/en-us/library/office/ff796219%28v=office.14%29.aspx

ExceDNA also supports Asynchronous functions https://exceldna.codeplex.com/wikipage?title=Asynchronous%20Functions

But you cannot use a VBA UDF to call an external resource asynchronously: the UDF has to be an XLL.

Charles Williams
  • 23,121
  • 5
  • 38
  • 38