1

So I searched online and most answers I got said that there is no concurrency in Excel. But some posts say that EXCEL 2007 offers multi-threading for faster calculations. I'm really confused, is it possible or not?

In my workbook, there are some cells are calculated using user defined functions (UDFs). These cells are updated through a RTD server once per second. So does this mean that these functions appear to be re-calculated paralleled, but in fact, Excel just schedules them sequentially? Does it make a difference if the computer has multiple cores or not?

If, in fact, these functions are running sequentially, would the following two be equivalent? In terms of performance and speed.

1)

cell1 = userFunc(1, switch1)
cell2 = userFunc(2, switch2)
cell3 = userFunc(2, switch3)

and 2)

cell = func()

where func() is

public function
        if(switch1)  call userfunc1
        if(switch2)  call userfunc2
        ....

end function

And let's assume all the necessary adjustments to the functions are made. I mean, would the first one be faster?

Thanks a lot!!!

UPDATE: So if there's no real concurrency in VBA, does that mean functions are called sequentially and shared global variables can be accessed without locking problem? I have an API object that's used by virtually every function, so can I shared it instead of initialize it everytime I use it? This object is just an API used to call some static functions, there's no modification or anything to the object itself.

DecimalTurn
  • 3,243
  • 3
  • 16
  • 36
user511792
  • 489
  • 1
  • 13
  • 23
  • A function must be considered by Excel to be thread-safe in order to make use of parallelization, and VBA UDF's do not fit that category. However if your UDF's are defined in something other than VBA then they might be candidates. See here for a full run-down: http://msdn.microsoft.com/en-us/library/office/bb687899.aspx#xl2007xllsdk_threadsafe – Tim Williams Feb 04 '13 at 06:58
  • Like C# or Java, in VBA you don't have a threading library or to qualify functions in that manner. VBA doesn't support multi-threading. If you are really in need to do multi-threading, like Tim pointed out, do call up *COM-able* app using `interop` to achieve what you need though. But I would find it problematic though - rather write entire programme in that other app layer instead, so to be able to use multi-threading. http://stackoverflow.com/questions/14143328/any-ideas-how-to-determine-what-age-group-an-individual-belongs-in-excel/14143756#14143756 – bonCodigo Feb 04 '13 at 07:32

1 Answers1

2

Yes Excel 2007 and later versions support multi-threaded calculation using multiple cores, but as Tim says - its not possible to write Multi-Threaded UDFs using VBA.

For good UDF performance you really need to be using the XLL interface and exploiting multi-threading.

You can create multi-threaded XLL UDFs in .NET C# or VB.Net using Excel DNA (free) or Addin Express (costs).
VSTO does not support UDFs, and .Net automation UDFs are very slow
For C++ XLL UDFs I recommend Planatech XLL+.

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