Why VBA is slower than other languages?

[bartrein]

I've been recently reading a lot about excel addins written in other languages which are widely considered to be quicker than VBA.
I understand that this is because VBA is being interpreted line by line as opposed to the languages compiled to the machine code.
But then what is the reason for this?
Why VBA is not compiled?
And what is this so called "machine code" ? Pure curiosity.

Many thanks

 

[macropod]

Hi bartrein,

Contrary to what you've been told, vba is not interpreted line by line - it is compiled at run time. However, the compilation obviously has to be done whenever the code is run, and that takes time. Also, as I understand it, the compilation is not a fully-optimised one, and that slows execution.

Full compilation (of the kind you get with exe programs) might run faster but it could also cause a simple office file with a complex macro to take up tens of Mb, instead of perhaps a couple of hundred Kb.

As in everything else, it's about balance. MS has taken a particular approach they feel gives the best compromise for most users. For those who need something more powerful, MS has other tolls available.

Cheers

[MS MVP - Word]

 

[strongm]

>it is compiled at run time

Well, sort of. VBA follows the same model as the VB IDE. To simplify, as you enter lines of code they are tokenised into op-codes. At runtime these op-codes are 'compiled' into p-code, and the p-code is interpreted by a virtual machine built in to the application.

The use of op-codes and p-code means this 'compilation' process is transparently fast - but the resulting code, whilst faster than it would be if it were interpreting the actual source code, is by its nature significantly slower than something compiled to native code (although some code, for example string handling, shows a less marked performance difference between native and p-code). VBA further slows this down by providing continuous runtime error checking of the code (and adding your own error handling can slow it down more). In addition no optimisation is carried out during VBA's 'compilation'.

>take up tens of Mb, instead of perhaps a couple of hundred Kb

Er - upon what do you base this assertion?

 

[byundt]

bartrein,
I learned how to program using an interpreted dialect of FORTRAN called WATFOR in the late Sixties. One of the big advantages of using the interpreted language was the run-time error messages, which could point to the exact line of code that had blown up. I also enjoyed eliminating the job control language needed to compile and link code in a compiled language. In contrast, compiled versions of FORTRAN had informative error messages only when you committed a syntax error. Run-time errors gave you a core dump that was hard to interpret, despite my motivation. As a result, I could write and debug code much faster using the interpreted language than with the compiled equivalent.

The WATFOR interpreter ran on an IBM 360. I imagine the 360 had computing power somewhat akin to an IBM PC AT from the mid Eighties. As a student, I was allowed 60 seconds of computer time a day. I was doing ray tracing to design a mirror to display stars on the ceiling of my dorm room. Despite the inherent inefficiencies of interpreting one statement at a time, it was amazing how much calculation could be accomplished. After fine-tuning the algorithm, I was eventually able to solve the complete problem within the 60 second period. I suspect that the same calculations could run in an interpreter as fast as I can type on a modern PC.

If you are working on a program intended to be used by many people, it might be worth your while to spend more time writing and debugging the code in a compiled language. You gain both execution speed and security (humans can't read machine language very easily) by compiling the code.

In the early Nineties, Microsoft product managers knew that interpreted languages could run fairly fast on a 486 CPU. They probably expected most Excel macros to be used by a single person or perhaps a small group. The typical "developer" would probably be working part-time on the project. It was therefore more beneficial to offer "acceptable speed" while making it easy to write and debug the code. They chose to use a dialect of their very popular Visual Basic language for this purpose. The resulting VBA language was so successful in Excel 4, that it later spread to all the other Office applications.

Using VBA, non-programmers could write very useful code. And they could do so without ever needing to submit a project request to the MIS department and wait for resources to be allocated. If you look at the Microsoft MVPs, I'll wager that very few of them for the Excel/Word/Access applications started out in the MIS department. Most of the ones I have met are gifted amateurs who program on a part-time basis.

Brad

 

[macropod]

Hi strongm,

I'm aware of the p-code issue, but vba isn't interpreted in anything like the sense that the old DOS versions of BASIC were. Even good old MS QuickBasic used p-code for the compiled programs.

Re "upon what do you base this assertion?":
Experience with the conversion of userforms to stand-alone exe files (I've used Realbasic to get native Windows and Mac apps without having to rely on the users having .NET on the PC or Mono on the Mac). Note too the operative word you omitted - *could* - and the order of scaling - from "a couple of hundred Kb".

Cheers

[MS MVP - Word]

 

[bartrein]

thanks for your answers , it's all very useful. Brad thanks for giving me all the context information.

I remember now when i was doing a c# course (just the basics)there was always a need to compile your code before being able to run it. In VBE there is also a similar command (Debug - Compile VB Project) but that is only to spot the bugs i guess?

And one more thing - what actually is this machine native code ? Does it have a name? What language is it written in?

thanks

 

[byundt]

bartrein,
The Debug-Compile VB Project is good for finding syntax errors and undeclared variables (assuming that you follow the good practice of requiring declaration). The compiler in a compiled language will do the same thing in its first pass through the code.

Machine language is CPU-specific and entirely numeric. You might refer to Wikipedia for more details:

http://en.wikipedia.org/wiki/Machine_code

For example, an instruction to add a number from one register into another might look like:
130913

As you can see from the above example, machine language is not very readable. And a single VBA statement might be translated into a dozen or more machine language statements.

Assembly language is a more human-readable version of machine language, but is still CPU-specific. Instead of numbers, you get mnemonic codes. Each assembly language statement translates into a single machine language instruction. For example, the above instruction might be rendered as:
AR R09 R13 'Add contents of register 9 to register 13


Brad

 

[bartrein]

Once again thank you Brad.