Excel vba 'compile vba' in debug toolbar? 2

[ironmouse]

I can't find anything in Excel help that explains to me exactly what this action does and what the results are of this action relative to my code running speed. Can anyone pls help?

 

[joerd]

I think that (when you have the default options set) VBA will compile the code "on demand", i.e. as needed. It will also compile the rest of the project code in the background when there is time. So, if you compile manually first, or if you change the option to NOT compile on demand, your code runs a little bit faster in principle. This is, however, offset by the fact that it takes longer before your project actually starts running in the first place (it has to compile all of the code prior to start).

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[CajunCenturion]

Within Excel, and the other Office applications as well, you have the ability to write additional code, in the VBA source language. For the machine to execute that code, it must be coverted into an executable code, and the compile function performs that translation. The code will always be compiled before execution, either directly by you, or by the system when it needs to. For performance purposes, it's always a good idea to manually compile your application.

That being said, the compiler has other benefits as well. It will insure that all referenced controls are within their scope, that procedures are properly names, parameter lists match argument lists both in type and count, that If-Then-Else statements are blanaced, loops are not interlaced, and many many other structural, syntactic, and to a limited degree, semantic mistakes are not being made in the code. If you, and I highly recommend this, use Option Explicit at the top of the VBA modules, then the compiler will insure that every variable is properly declared, which greatly aids in finding a bug caused by a typo in a variable name.

Compiling should be an integral part of the code development process. It can identify many error in the code, but not all. Some will only be discovered at run-time.