1

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.

Community
  • 1
  • 1
theholyhandgrenade
  • 80
  • 6
  • What's large? And highly doubtful regardless. – findwindow Jun 28 '16 at 18:30
  • 2
    `Option Explicit` itself will not "speed up code". Declaring variables as the "best possible type" for the job will assist. `Option Explicit` just forces variable declaration, but doesn't require you to declare them as any particular type of variable. [Avoiding Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros), Using [With ... End With](https://msdn.microsoft.com/en-us/library/wc500chb.aspx) constructs and assigning parentage will surely speed up code. So will avoiding unnecessary loops. – Scott Holtzman Jun 28 '16 at 18:39
  • Sorry, should have been more clear, I meant declaring variables vs leaving them all as variables. – theholyhandgrenade Jun 30 '16 at 13:44

1 Answers1

2

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.

GuitarPicker
  • 316
  • 2
  • 11