|
Albert Siagian
Guest
|
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 |
|
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
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:
https://www.add-in-express.com/creating-addins-blog/2013/11/29/populate-excel-workbooks-ranges-with-arrays/ |
|
|
Albert Siagian
Guest
|
|
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 |
|
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
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. |
|
|
Dmitry Kostochko
Add-in Express team
Posts: 2875
Joined: 2004-04-05
|
|
Forums
Topics
Search
Help
Login
Register