COM Add-in, Multi-core and Multi-thread

Add-in Express™ Support Service
That's what is more important than anything else

Only registered users are allowed to subscribe.
COM Add-in, Multi-core and Multi-thread
 
Subscribe
Albert Siagian




Posts: 86
Joined: 2012-05-25
Hello,

We know that Excel VBA doesn't really support multi-core and multi-threading. In my quad-core processor machine, VBA only use 30% of CPU utilization.

Does this limitation also apply to COM Add-in, since add-ins runs inside Excel ? Can I use (and safely) multi-thread in COM Add-in (using .NET), to achieve max CPU utilization ? This is for a process that doesn't involve user interaction.

Current issue: we have VBA code that uses arrays and write once into a worksheet, to create 100 millions cells (i.e. 1 million rows by 100 columns). Currently it took over 5 hours to process and would like to make it faster using .NET and multi-thread.

Regards
Albert
Posted 04 Dec, 2017 20:22:59 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
Hi Albert,

You can use additional threads for loading/preparing/handling data in the code of your add-in. Note, since Excel works as STA, it is not recommended to call its Object Model from additional threads, this may lead to Excel crashes.

Current issue: we have VBA code that uses arrays and write once into a worksheet, to create 100 millions cells (i.e. 1 million rows by 100 columns). Currently it took over 5 hours to process and would like to make it faster using .NET and multi-thread.


Please have a look at the following blog post, I think it will be helpful:
Populating Excel workbooks and ranges with arrays
Posted 05 Dec, 2017 04:50:39 Top
Albert Siagian




Posts: 86
Joined: 2012-05-25
Dmitry Kostochko writes:
You can use additional threads for loading/preparing/handling data in the code of your add-in. Note, since Excel works as STA, it is not recommended to call its Object Model from additional threads, this may lead to Excel crashes.


Thanks Dmitry. So, from additional threads, it's not recommended doing something like this ?

dim abcd as string = rng.Value
rng.Value = "testing"


Dmitry Kostochko writes:
Please have a look at the following blog post, I think it will be helpful: Populating Excel workbooks and ranges with arrays


Current VBA running 64-bit code is already doing similar like this (except doesn't use SELECT SQL). It works fine up to certain million cells only. That is why we are thinking alternatives.

Regars
Albert
Posted 05 Dec, 2017 05:29:54 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
Hi Albert Siagian,

Thanks Dmitry. So, from additional threads, it's not recommended doing something like this ?


Yes, exactly.

Current VBA running 64-bit code is already doing similar like this (except doesn't use SELECT SQL). It works fine up to certain million cells only. That is why we are thinking alternatives.


Probably an alternative way in this case would be a direct access to a file, say via the Open XML SDK.
Posted 05 Dec, 2017 06:49:53 Top
Albert Siagian




Posts: 86
Joined: 2012-05-25
Thanks Dmitry.
Posted 05 Dec, 2017 07:00:40 Top
Dmitry Kostochko


Add-in Express team


Posts: 2740
Joined: 2004-04-05
You are welcome!
Posted 05 Dec, 2017 07:03:08 Top