Why "compile"
Mitchell F. Powell
VBA project? I've noticed that it does NOT seem to make the project run any
faster, so what exactly is the point (except for catching syntax errors and
the like)?
The question is really in the context of trying to speed up the calculation
of several User Defined Functions (UDFs) I've written. I've noticed that a
large worksheet with many UDFs calcs VERY slowly. I've also noticed that
the calc speed is less a function of the complexity of calculations inside
the UDF as it is the fact that it is a UDF in the first place. For example,
a sheet containing 1000 of the the follwing functions:
Function Test()
Test = 10
End Function
will calc as slowly as one containing long and complex UDFs.
Is there any way to speed up calculation of UDFs?
Chip Pearson
As I understand it, here's what "Compiling" does and doesn't do. Note, much
of this comes from the Baarns Consulting web site. www.baarns.com
Unlike compiling source code written in VB or C or another language, the VBA
"compilation" process does not produce machine executable or "native" code.
It does not produce an EXE file. You can't really separate the source VBA
from the "compiled" VBA, since they are always stored in the same file.
What "compilation" does is translate the internal representation of the VBA
code from the platform-independent code to the platform-dependent code.
When you type in some VBA source code, it is never really stored "as text"
in the workbook. Instead, it stores a symbolic byte-code representation of
the source. In other words, it doesn't store the words "Select Case" in the
file. It stores some internal representation of this code in the file.
This representation, called OpCodes, is the same for all platforms. Mac,
Windows, Windows98, WindowsNT, etc. When you "compile" a VBA project, those
OpCodes are translate into what are ExCodes. ExCodes vary from one platform
to the next. For example, Mac will store the ExCode for "Select Case"
differently than Windows will store it. The OpCodes are the same, because
OpCodes represent the text. The ExCodes are different, because they are
read by different Run Time Libraries, which read in the ExCodes as data, and
then execute machine instruction "on behalf" of the ExCodes. Essentially,
the ExCodes are still data, not instructions, as far as the system is
concerned.
So why compile? In addition to the syntax checking you mention, compiling
a VBA project writes out the ExCodes and stores them in the Workbook. This
will improve your workbook's startup time, since object references are
resolved, if possible. Excel/VBA won't have to do a compilation when the
code if first run. It does not make your VBA code run any faster, because
VBA will compile at run time, if necessary.
In general, compilation buys you basically nothing during the development
phase. For workbooks that will never be saved after distribution (e.g.,
Add-Ins) compilation will improve the startup time, since they won't be
compile as they are loaded. If you distribute and Add-In that is not
compiled, Excel/VBA will compile it while it is added. Since Add-Ins are
essentially read-only, the compilation ExCodes are not saved in the file.
So next time the Add-In is loaded, it must be recompiled.
For small workbooks, compilation doesn't do anything for you in a practical
matter. Excel/VBA can compile and bind fast enough that it is typically
transparent to the user. For large projects, it can decrease the startup
time.
Unfortunately, it doesn't make your code run any faster. For that, you have
to write in a language that can create a DLL, and really compile to machine
language.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting Services
www.cpearson.com ch...@cpearson.com
"Mitchell F. Powell" <mpo...@rcapital.com> wrote in message
news:ea8jY7Ne$GA....@cppssbbsa02.microsoft.com...
Gordon Wilmot
Chip Pearson answered the first part of your question very well, I think this
may answer the second part, although I am no real expert on the workings of
computers.
When a procedure is called, either a subroutine or a function, a reference to it
is on what is called the "stack" , also it has to be loaded into ram, and
various registers assigned to it to do the required operations. This may entail
moving other data being moved out of the registers. When the procedure finishes
the registers are freed, and any data which was in them is returned, and the
procedure is "popped" of the stack and the ram is freed. Of course this all
takes time, and if you have a lot of procedures being called, it may take more
time setting them up than doing the operations in them. Also if there is
insufficient ram, then the data which cannot be retained in ram will be off
loaded to the swap file which is very slow compared to operations between the
CPU and ram and the high speed caches.
If you have a long complex procedure in ram, then the CPU is able to operate on
it more efficiently as every thing is set up for it.
I know with structured programming it is considered best to have subs and
functions which do one particular thing, but in your case it may be better to
combine some of them if it is possible.
Gordon Wilmot
Ioan Jones
Jake Marx
the workbook.
Excel 95: Creating an add-in will compile the VBA code.
Regards,
Jake Marx
Ioan Jones <ioan...@btinternet.com> wrote in message
news:88kk9s$gtp$1...@neptunium.btinternet.com...