I am new to VBA programming. I think I have seen it somewhere but I totally forgot where I saw it. Maybe I heard it from someone.
My question: does the amount of comments and whitespace in VBA affect the code's performance?
I guess it should be the same in Excel and Access but I don't want to assume so I am going to specify I am using Access 2003.
White space characters and comments are generally ignored by compilers and interpreters. I can't find an official statement to this effect for VBA specifically (similar to this for C code in Visual Studio), but from experimental testing VBA seems to behave the same way.
Regarding the amount of code in VBA affecting a procedure's performance, obviously the more the code, the longer the procedure will take to complete execution.
From MSDN MS-VBAL Lexical Rules
Also remember variable and constant names don't exist in a compiled program.
module-body-physical-structure = *source-line [non-terminated-line] source-line = *non-line-termination-character line-terminator non-terminated-line = *non-line-termination-character line-terminator = (%x000D %x000A) / %x000D / %x000A / %x2028 / %x2029 non-line-termination-character = <any character other than %x000D / %x000A / %x2028 / %x2029>An implementation MAY limit the number of characters allowed in a physical line. The meaning of a module that contains any physical lines that exceed such an implementation limit is undefined by this specification. If a <module-body-physical-structure> concludes with a <non-terminated-line> then an implementation MAY treat the module as if the <non-terminated-line> was immediately followed by a <line-terminator>.
For the purposes of interpretation as VBA program text, a module body (section 4.2) is viewed as a set of logical lines each of which may correspond to multiple physical lines. This structure is described by the Logical Line Grammar. The terminal symbols of this grammar are Unicode character codepoints.
module-body-logical-structure = *extended-line extended-line = *(line-continuation / non-line-termination-character) line-terminator line-continuation = *WSC underscore *WSC line-terminator WSC = (tab-character / eom-character /space-character / DBCS-whitespace / most-Unicode-class-Zs) tab-character = %x0009 eom-character = %x0019 space-character = %x0020 underscore = %x005F DBCS-whitespace = %x3000 most-Unicode-class-Zs = <all members of Unicode class Zs which are not CP2-characters>An implementation MAY limit the number of characters in an <extended-line>. For ease of specification it is convenient to be able to explicitly refer to the point that immediately precedes the beginning of a logical line and the point immediately preceding the final line-terminator of a logical line. This is accomplished using <LINE-START> and <LINE-END> as terminal symbols of the VBA grammars. A <LINE-START> is defined to immediately precede each logical line and a <LINE-END> is defined as replacing the <line-terminator> at the end of each logical line:
module-body-lines = *logical-line logical-line = LINE-START *extended-line LINE-ENDWhen used in an ABNF rule definition <LINE-START> and <LINE-END> are used to indicated the required start or end of a <logical-line>.
The syntax of VBA programs is most easily described in terms of lexical tokens rather than individual Unicode characters. In particular, the occurrence of whitespace or line-continuations between most syntactic elements is usually irrelevant to the syntactic grammar. The syntactic grammar is significantly simplified if it does not have to describe such possible whitespace occurrences. This is accomplished by using lexical tokens (also referred to simply as tokens) that abstract away whitespace as the terminal symbols of the syntactic grammar. The lexical grammar defines the interpretation of a <module-body-lines> as a set of such lexical tokens.
The terminal elements of the lexical grammar are Unicode characters and the <LINE-START> and <LINE-END> elements. Generally any rule name of the lexical grammar that is written in all upper case characters is also a lexical token and terminal element of the VBA syntactic grammar. ABNF quoted literal text rules are also considered to be lexical tokens of the syntactic grammar. Lexical tokens encompass any white space characters that immediate precede them. Note that when used within the lexical grammar, quoted literal text rules are not treated as tokens and hence any preceding whitespace characters are significant.
3.3.1 Separator and Special Tokens
WS = 1*(WSC / line-continuation) special-token = "," / "." / "!" / "#" / "&" / "(" / ")" / "*" / "+" / "-" / "/" / ":" / ";" / "<" / "=" / ">" / "?" / "\" / "^" NO-WS = <no whitespace characters allowed here> NO-LINE-CONTINUATION = <a line-continuation is not allowed here> EOL = [WS] LINE-END / single-quote comment-body EOS = *(EOL / ":") ;End Of Statement single-quote = %x0027 ; ' comment-body = *(line-continuation / non-line-termination-character) LINE-END<special-token> is used to identify single characters that have special meaning in the syntax of VBA programs. Because they are lexical tokens (section 3.3), these characters may be preceded by white space characters that are ignored. Any occurrence of one of the quoted <special-token> elements as a grammar element within the syntactic grammar is a reference to the corresponding token (section 3.3).
<NO-WS> is used as terminal element of the syntactic grammar to indicate that the token that immediately follows it must not be preceded by any white space characters. <NO-LINE-CONTINUATION> is used as terminal element of the syntactic grammar to indicate that the token that immediately follows it must not be preceded by white space that includes any <linecontinuation> sequences.
<WS> is used as a terminal element of the syntactic grammar to indicate that the token that immediately follows it must have been preceded by one or more white space characters.
<EOL> is used as element of the syntactic grammar to name the token that acts as an "end of statement" marker for statements that must be the only or last statement on a logical line.
<EOS> is used as a terminal element of the syntactic grammar to name the token that acts as an "end of statement" marker. In general, the end of statement is marked by either a <LINE-END> or a colon character. Any characters between a <single-quote> and a <LINE-END> are comment text that is ignored.
You might notice that VBA is parsed into executable tokens at edit time, not at run time.
Multiple inline spaces are compiled as a single n-space token, so they can be re-displayed for editing, but really they are just a single token in the compiled code. So it doesn't matter how many spaces you put between words. 100 is the same as 1.
So this "Correct-by-construction" effectively removes any spaces when you finish editing (not when you run it).
Try it yourself. Put some extra spaces a the end of a line, move to the next line, then move back and the spaces are gone. Also notice that if you try to put in invalid code it complains until you correct it. This is the signature of early parsing and tokenization.
So the answer to your question is that there are no extra spaces when the code runs because the code is pre-compiled, so it CAN'T effect speed.
I think Correct-by-construction is really pretty cool stuff. It's the best of a run time interpreter and fast compiler all in one!
The parser in the Forth computer language did something similar, in how it worked, but of course it didn't have the correct by construction editor. I had always hoped to add one to it.
For most practical situations, it really shouldn't have any impact.
You would do well to consider the use of spaces and comments not for performance but for readability.
VBA is an interpreted language, which means that the interpreter must parse through all your human readable code every time it runs, as opposed to a compiled language in which the human readable code is compiled one time into machine readable code. In both cases, white space & comments are removed before execution.
Theoretically, you could put enough extra white space and/or comments into your VBA code that you would eventually slow down the interpreter, but you would probably have to have thousands or 10s of thousands of lines of extra junk in there to ever notice a difference.
It would make for an interesting experiment, though!
