I have a excel spreadsheet of 5M, but with many formulas and more than 7000 lines of code VBA . I would like to receive information on what I can do to improve performance. I would also like to know if it makes a difference to use all the cores of the processor of my I7 or if using only 2 or 3 cores would work faster. The impression is that with only 2 cores, the code is faster. Another information I would like to know is if using 64 bits help in any way. I've heard that for light spreadsheets it's best to continue with 32 bits . My excel is 2010. Thanks
Performance Excel VBA vs. Multhreads Calculation
2
asked by anonymous 20.10.2015 / 04:22
2 answers
4
Microsoft has implemented the concept of Multi-threading to solve the formulas in a spreadsheet (I believe from Excel 2007). Because of this, if a machine has more than one core, Excel will use the configured amount (by default all) to evaluate the formulas contained in a worksheet. Note, however, that this is done only for the evaluation of native formulas. The same does not occur in VBA code.
In order to solve a problem (via VBA) in parallel, it would be necessary to divide your problem into several instances of Excel. Since each Excel in this scenario is a separate process, then Windows itself will allocate each Excel to a core, thus speeding up the execution of the code.
However, it can be a bit tricky to do this, since you first need to check what's parsable in your code. That done, you should have some way to instantiate each Excel for VBA code to run (a VBS script can help). After that, you need to merge the result.
If your problem is not so easily paralleled, then there is not much to do. It may be that VBA is not a good choice for the problem to be solved.
Either way, the tips below can help:
-
Disable screen refresh (Application.ScreenUpdating = False) while VBA code is running
-
Place the calculation of the formulas manually (Application.Calculation = xlCalculationManual) also while VBA is running.
Some useful references:
26.10.2015 / 15:37
0
There are many ways to do the same thing, formulas usually leaves, macros and worksheets heavier, because they will always be recalculated depending on the case and the formula.
If your macros are slow you have to analyze why, it may be by the formulas, interaction with the spreadsheet that has many formulas if it is cell by cell causes slowness.
My first solution to this was to transfer the range to be parsed to another auxiliary data-only flap and then pass the values back nowadays I use arrays for everything
I think the first step is to try to optimize these macros, especially in structure.
11.01.2016 / 20:24