VBA on Excel only use one processor, how can I use more?

Question

VBA is known to work on only one processor at the same time, so when I run a macro, Excel only uses 50% of CPU instead of totally (dual-core).

Is there a workaround to make VBA use both processors ?

Thanks.

Answer 1

The answer is no unless you split the workload across multiple Excel instances and recombine the results at the end.

But the vast majority of cases of slow VBA execution can be speeded up by orders of magnitude:

• switch off screen updating
• set calculation to manual
• read and write excel data using large ranges and variant arrays rather than cell-by-cell
• look closely at the coding in VBA UDFs and bypass the VBE refresh bug
• minimise the number of calls to the Excel object model

Answer 2

You could split the problem up across multiple instances of Excel.Applications, that way you could do half in one copy of Excel and half in the other.

I've seen someone doing simulation work in Excel VBA using 8 cores. A bit of a pain to bring the results back together at the end, but it worked.