Speed up the vba engine

[helmekki]

Hi there

Is there any way to speed up the vba engien, either by follwo certain code lines or through loading a specific compiler or getting an external application that helps invresing the vba engin ?

[dominicb]

Good afternoon Helmekki (I've answered one of yours before haven't I...?)

VBA is an interpreted language as it is compiled at run time - there are no third party compilers and VBA (unlike regular VB) cannot run outside of Excel (or Access or Powerpoint et al).

However, by clever programming you can speed things up a bit.
1. Turn off screenupdating using:
Application.Screenupdating=False
but don't forget to turn it back on before the routine finishes.
2. Use Option Explicit at the start of your code - this forces you to declare all variables.
3. Use the With - End With construct if you are manipulating the same object over several consecutive lines, rather than keep referring to it.
4. Use the Case function instead of If - Then decisions if possible.
5. If you are referencing a number of cells that are then recalculating, then consider setting the recalc function to manual and forcing the recalc once the code has finished. The commands below should help you out.
Calculate
Application.Calculation = xlManual
Application.Calculation = xlAutomatic

HTH

DominicB

[helmekki]

Thank u very much, dominicb

[dominicb]

No problem Hesham. Thank you for the feedback.