Performance Excel VBA vs. Multhreads Calculation

Asked

Viewed 1,188 times

2

I have an 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 for the best performance. I would also like to know if it makes a difference to use all cores my I7 processor or use only 2 or 3 cores would work faster. The impression is that with only 2 cores, the code is faster. Another piece of information I’d like to know is if I use 64 bits help in some way. I’ve heard that for lightweight spreadsheets it’s best to continue with 32 bits. My excel is 2010. Thank you

vba

  • Hello @Henrique, in my limited hardware knowledge, I imagine using more cores will be better. I tested some spreadsheets as big as in 32bit/64bit, the difference was minimal, getting better performance in 64 With respect to the code, would have many tips to pass: 1. use functions with the minimum of lines needed; 2.Disable the visualization; 3. Prefer to use With with environment variables; 4. optimize code using classes you can find here: http://www.cpearson.com/excel/optimize.htm and http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm. I hope I have helped!

2 answers

4

Microsoft implemented the concept of Multi-threading to solve the formulas of a spreadsheet (I believe from Excel 2007 onwards). Because of this, if a machine has more than one core, Excel will use the set amount (by default all) to evaluate the formulas contained in a spreadsheet. Note, however, that this is done only for the evaluation of native formulas. The same does not occur in VBA code.

In order to be able to solve a problem (via VBA) in parallel it would be necessary to divide your problem in several instances of Excel. As each Excel, in this scenario, is a separate process, then Windows itself will allocate each Excel in a core, thus accelerating the execution of the code.

However, it can be a little tricky to do this, since first you need to check what is parallelizable in your code. Done this, you must have some way to instantiate each Excel for the VBA code to run (a VBS script can help). After that, you need to join the result.

If your problem isn’t so easily parallelized, then there’s not much to do. It may be that VBA is not a good choice for the problem to be solved.

Anyway, the tips below can help:

  • Disable screen update (Application.Screenupdating = False) while VBA code is running

  • Put the calculation of manual formulas (Application.Calculation = xlCalculationManual) also while the VBA is running.

Some useful references:

https://stackoverflow.com/questions/16581868/vba-on-excel-only-use-one-processor-how-can-i-use-more

http://www.excelhero.com/blog/2010/05/multi-threaded-vba.html

https://superuser.com/questions/789154/excel-vba-program-only-running-at-25-speed

0

There are many ways to do the same thing, formulas usually leaves, macros and spreadsheets heavier, this is because they will always be recalculated depending on the case and formula.

If your macros are slow you have to analyze why, it can be by formulas, interaction with the spreadsheet that has many formulas if cell by cell causes slowness.

My first solution to this was to transfer the range to be analyzed to another data-only tab and then pass the values back nowadays use arrays for everything

I think the first step is to try to optimize these macros, especially in the structure.

Browser other questions tagged vba

You are not signed in. Login or sign up in order to post.