tricks of gurus

[Richard Schollar]

Surely, you would use conditional compilation?

Care to enlighten me, Mr P? I could probably stretch to another metaphorical gin in return

 

[barry houdini]

Wouldn't that be

=A1-DAY(A1)+1

Barry?

Most definitely, Richard, good catch, as they say

just testing if anybody's actually out there

 

[xld]

Surely, you would use conditional compilation?

Care to enlighten me, Mr P? I could probably stretch to another metaphorical gin in return

Conditional compilation allows you to create testing and production versions of your code.

For example, you may want to include message boxes, or Debug.Print or Debug.Assert statements while you are developing and testing your code, but you don't want those to be active when you release the code. VBA allows you to include or exclude blocks of code with conditional compilation.

Conditional compilation uses If, Then, and Else statements to include or exclude a block of code. All conditional compiler directives start with #.
Note that this is the directive, not the code that is conditional upon those directives.

So a typical use of conditional compilation would look like

#If TestRun Then
‘some normal code that is executed if a test run
#Else
‘some other normal code that is executed when it is NOT a test run
#End If

You will notice that a variable is tested, this is called TestRun in the example and would also be a conditional compiler directive,

#CONST TestRun = True

The #CONST directive is used to create the variable.

Then, delimit the blocks using the compiler directives to include various blocks of code.

Many other conditional blocks of code can use this conditional constant, so there is only one place to change when releasing to production.

There are a few built-in conditional constants as well that can be tested, such as VBA6, which I use to include an Excel 97 split method

#If VBA6 Then 
#Else 
'-----------------------------¬¬-----------------------------¬-¬------ 
Function Split(Text As String, _ 
        Optional Delimiter As String = ",") As Variant 
'-----------------------------¬¬-----------------------------¬-¬------ 
Dim i As Long 
Dim sFormula As String 
Dim aryEval 
Dim aryValues 
    If Delimiter = vbNullChar Then 
        Delimiter = Chr(7) 
        Text = Replace(Text, vbNullChar, Delimiter) 
    End If 
    sFormula = "{""" & Application.Substitute(Text, Delimiter, """,""") & _ 
        """}" 
    aryEval = Evaluate(sFormula) 
    ReDim aryValues(0 To UBound(aryEval) - 1) 
    For i = 0 To UBound(aryValues) 
            aryValues(i) = aryEval(i + 1) 
    Next 
    Split = aryValues 
End Function 
#End If

And there is also a Mac and a Win32 constant.


I reckon we are upto a case now . I am only about 50 miles away from you, so you can deliver

 

[Richard Schollar]

Thank you Bob

I can obviously see where it's very useful (the xl97 and Split thing), but does it have a big advantage over using a standard If Then Else? Does it do anything differently?

I had a couple of Sapphire bottles put aside for you, but unfortunately was *forced* into drinking them myself...

 

[xld]

Yes of course it does. If you have an If ... Else ... End If all of that code is compiled, if you have #If .. #Else ... #End If then only the part of the code that satisfies the condition is compiled. Leaner, meaner and better all round.

Plus, you have the built-in constants that you don't have to maintain.

How would you do the Split thing your way?

The Excel 97 is just a simple example, I use it to build early/late binding all in one file, testing/production where my testing/production variable is set by an environment variable, etc. I agree you could do all that with your own code, but that GETS COMPILED as well.

As for the Sapphire, I don't mind new bottles, as long as they are full.

 

[Richard Schollar]

I see, I see. So, am I right in thinking that compiled in this instance refers to when you create an Add-In rather than say simple code in an every day workbook that just exists in a standard module? There's no compilation in the second instance, or am I incorrect here? In which case (other than the built in constants) the #IF would not have an advantage over standard If (unless you will compile at some future point)? Am I getting this right, or have I missed the point/got it uterly wrong? Maybe I am misinterpreting what is meant by 'compiled'?

I've picked up a new bottle of Sapph - 'course, there's no guarantee it'll survive past tomorrow night

 

[xld]

No, it is not addin specific, all code gets compiled, at run-time at the latest.

VBA is not stored in the workbook as text, but is converted to something else
when a file is saved. This can easily be demonstrated, it is not visible in
the file when viewed with a text editor.

Equally clearly, VBA is not fully compiled to machine language as the code
runs on Windows PCs and Macs.

It is also clear that the code is interpreted at run-time, a programming error,
such as incorrectly setting a range variable, is not trapped until that code
is executed.

VBA code is translated and stored as machine independent codes, Opcodes. When
the code is compiled or executed, the Opcodes are translated to Excodes, which
are specific to the platform.

When you run VBA code which has not been compiled, the compilation is performed
as-needed.

In addition to syntax checking, compiling a VBA project writes out the ExCodes
and stores them in the workbook. This improves the workbook's startup time,
since object references are already resolved where possible, and there is no need
for a compilation when the code is first run. It does not make the VBA code faster,
as VBA compiles at run time if necessary, that is the code is always compiled.

The #If will always score over a standard IF, as the compilation doesn't compile the
#If, only the code that satisfies the condition makes it to Excodes. In a standard If,
all code makes it to Excodes, so that IF is always evaluated at run-time.


If you are interested, there is a white paper on the subject, on the Baarns Group
site, "VBA - Maximum Performance vs. File Size", at http://archive.baarns.com/IE4/index_devonly.asp.

 

[Richard Schollar]

Thanks Bob - I feel like I've learned something today! I am going to check out that site too.

Sapph on the rocks, or as it comes?

 

[xld]

So now you know why I wondered why he didn't use conditional compilation.

Chilled glass, chilled alcohol, but don't water it (I only water my scotch!).

 

[erik.van.geit]

So now you know why I wondered why he didn't use conditional compilation.

Chilled glass, chilled alcohol, but don't water it (I only water my scotch!).

Hi, xld,

do not wonder, but try to see the things from the point of view of "lower-skilled" people
You have a large experience and are (close to or real) professional programmer: I'm not at all, just quite interested in this stuff. As you have seen perhaps, I just like to "play" (a search for bleeding_nose would be helpful )

I do not know if you're playing piano concerts, but wouldn't wonder if you didn't use your thumbs the first time. It's just a matter of experience and knowledge. On this webpage I found you a good teacher. So thanks for the explanation.

have a nice "last-day"2006
Erik

PS: now nbrcrunch, are you happy your thread is alive again