Can using the option explicit command speed up very complicated macros in VBA? I'm assuming so, since VBA doesn't have to treat every variable as a variant (unless explicitly declared to be otherwise, I believe that's the default for VBA?), but would like to hear others experiences.
1 Answer
Option Explicit acts more as a compile-time check rather than a runtime optimization. VBA is compiled into an intermediate code which is then interpreted rather than native code.
Depending on the number and types of declarations, it's possible that your script might take longer to parse the explicit declarations than if left to implicitly declare them on the fly. If you use a lot of variants, then I would not expect much performance change, as the resulting intermediate code would be very similar or the same. If, however, you explicitly declare the simpler types (like int), then you should benefit from reduced memory allocation and faster calculations.
Option Explicititself will not "speed up code". Declaring variables as the "best possible type" for the job will assist.Option Explicitjust forces variable declaration, but doesn't require you to declare them as any particular type of variable. Avoiding Select, Using With ... End With constructs and assigning parentage will surely speed up code. So will avoiding unnecessary loops.