18 thoughts on “Conditional Compilation Arguments”

1. I often use a registry entry, so if I’m at a user’s desk, I can flip a registry setting and get much more information, developer-related error messages, less on-sheet protection, shortcut keys to access the VBProject etc.

2. Rob,

   In addition to Stephen’s approach I also use textfiles to track and log on user’s computers (and network). This is valuable when the engagements are on a ‘remote’ work as the files are easy to mail.

   Keep up the good work Rob :)

   Kind Regards,
   Dennis

3. C programmers have been struggling with this for 30 years. C++ and Java were actually designed to make it possible to avoid conditional compilation. Here’s why:

   Public Sub hello()
   Debug.Print “Hello World”

   #If DEBUGGING_VERSION Then
   Print “This is bad code” ‘ Does not compile…

   Call this_is_not_a_valid_function_name ‘ Does not compille…
   #End If
   End Sub

   The problem with this code is that the #If…#End if block does not mean “Don’t run this code”, what it really means is “don’t even compile this code”. As a result, all the syntax/name/type-checking goodness the compiler uses to verify your code never happens to the code in the #if…#endif block until the appropriate conditional compilation symbol is defined. It then becomes incredibly easy for nominally disabled blocks of code to start harbouring bugs, wierd syntax, references to no-longer-existant functionst, etc. and other problems you’ll never even check for until you define the correct conditional compilation symbol.

   Your first approach, the public const, is better in that it avoids this problem:

   Public Const DEBUGGING_VERSION = False

   Public Sub hello()
   Debug.Print “Hello World”

   If DEBUGGING_VERSION Then
   Print “This is bad code” ‘ Does not compile…

   Call this_is_not_a_valid_function_name ‘ Does not compille…
   End If
   End Sub

   In this version of the code, the compiler immediately detects the errors in the DEBUGGING_VERSION branch of the conditional, even when the flag is set to false, as above.

   The cost of this approach is that there’s some overhead at runtime: the environment needs to skip past the code in the debugging branch. However, does that really matter? If you’re really concerned about that level of optimization, you probably should not be using VBA. In theory, it’s possible to eliminate the code and the overhead entirely when the guard expression in provably constant FALSE. However, I don’t think VBA does this. MSVC++ does this only when optimizations are enabled.

4. mschaef,

   Conditional compilation comes useful in VBA however, particularily when you need to code applications that need to run in Excel 97. Excel 97 ran on VB5, so it doesn’t have a lot of functions that VB6 does, like Split(), Replace() and others.

   In fact, you can use it too to avoid compilation problems with objects/properties/methods that didn’t exist in Excel 97, but that do exist, and are quite useful, in Excel 2000 (Working with Pivot Tables comes to mind).

   I for one, use a text file in the directory where my app is installed to turn on/off this debug version variable. That way I can just add/delete this file and get the appropiate response.

5. Don’t get me wrong, I’m not one of the no-conditional-compilation purists saying that conditional compilation is always a bad thing. I’m just saying that there are better approaches that should be used if possible. Most of the time, you actually want (or should want) those ‘compilation problems’… they’re problems for a reason.

   “In fact, you can use it too to avoid compilation problems with objects/properties/methods that didn’t exist in Excel 97, but that do exist, and are quite useful, in Excel 2000 (Working with Pivot Tables comes to mind).”

   Are there standard symbols defined by the various versions of Excel? That is, is there an EXCEL_VERSION symbol you can use to automatically detect the version of Excel at compile time? If so, that’s a nice addition to the bag of tricks.

6. I’m I right in thinking that you would have to call this some how in a “deployed” application. You wouldn’t really use a pop up like in the example…then agian i guess you could a “developer/ment” password, but this would only really work if you used a password to enter your app. Otherwise it would have to be called from the app it’s self, no?

   I am currently using a “debug.txt” file method as suggested in PED, but i might go with some other option for “depolment”

   a couple of questions:
   1:How would you “flip” this on a deployed app – open up the project and change the setting?
   2:What is the benifit of this over a const?

   p.s, whats with the #if etc, it if just the formating?

   cheers

7. I use a text file in the same directory as the main app, as described in PED. In fact, for some projects I use a few different ones: one to bypass the password, another to indicate debugging mode, and a third so the program knows it’s me and not the programmer at the client’s place. The benefit of this over a constant is that I can adjust it from outside the project. It’s also easier than digging around in the registry.

   I solved the Excel 97/VBA 5 compatibility problem by deciding not to take any projects that require Excel 97.

8. mschaef: The predefined compiler constant is VBA6.
   You make a good point about bugs not being identified.
   I dont think it would trip me too often. Most of my code checks for testmode, which is the mode I develop in most of the time. Though it certain is something to keep in the back of your mind for release testing.

   Stephen:
   Some organisations have a domain policy restricting regedit.exe and *.reg file handling as a result. Have you found this to be a problem?
   Are you willing to share your code for registry checks?

   Ross: I wont go into what methods I use for user authentication – but just to say it’s stronger security than an inputbox.
   Flip the setting: I usually copy the .XLS to my home drive and work on it from there. Yes, I would open it then change that setting. The down side to this, of course, is that I might have to open it twice to get the OnOpen code to run in test mode.

   Cheers!

9. Hi Rob. Yes, if the company restricts registry manipulation, I’d go with a text file. However, I’ve seen cases where the Program Files folder was read-only (so I couldn’t use the file-in-same-folder method), while the HKCU hive of the registry was read-write (to save the user’s settings). If you just use the HKCU/Software/VB and VBA Program Settings key, you can read a value using GetSetting(). The main benefit of a registry key or file vs a constant or conditional compilation is that the former allows me to switch to ‘debug’ mode while the program is still running.

10. Hi Rob
    We had a discussion on this on Excel-l recently.
    Personally I’m not a big fan of Conditional Compilation in VBA. I’m sure it has some use somewhere, but I find all that #if #else stuff just makes things harder. Your code then also has a dependency on some global element elsewhere. I prefer to keep my unit tests in seperate modules and out of production code.

    For compiled work (VB/C++/.net) config files, log files, registry etc all make loads of sense, for vba though I find its easier just to switch into the VBIDE and set some breakpoints and watches. If its a remote client then like Dennis I’ll issue a debug version with verbose logging.
    cheers
    Simon

11. I cut my teeth in a mainframe environment where the conditional compilation exceeded even that supported by C. It’s unfortunate that VB(A) supports nothing more than the if statement.

    Given the limited supported for conditional compilation I’ve experimented with it to include alternate versions of code for use with VB5 and/or Macs and/or debugging. But, the most effective technique is for *unconditional* non-compilation.

    Confused?

    It’s a very effective technique to preserve old code while implementing new code. I use it in the form
    #If False then
    old code
    #else
    new code
        #end if
    With this approach I often leave the old code in the new version of an add-in.

12. My problem is that I usually forget to reset conditional compilation flags before sending the file to production. Embarrassment ensues.

    Instead, I simply check for the presence of an empty debug file in my root directory. I’m sure the user doesn’t have it, and I can just rename it when I want to test for production.

    I only want to check for the file once (or as infrequently as possible), so I set a static variable letting me know whether I’ve already looked for the file. When that goes out of scope (hence it goes false), I know it’s time to check for the file again.

    Function Debugging() As Boolean
    Static CheckedDebugging As Boolean  
    Static SavedDebugging As Boolean

        If Not CheckedDebugging Then
        ‘if this goes out of scope (no longer true), check it again
           SavedDebugging = (Dir(“C:Debug_This.txt”)  “”)
            CheckedDebugging = True
        End If
        Debugging = SavedDebugging
    End Function

    Of course, my test code now looks like:

    IF Debugging then
       ‘do something debug-like
    ELSE
       ‘do something production-like
    END IF

    Also, I use this for different projects, each one with a different debug file name.

    I know it’s simple, but it’s fool-proof for my purposes.

13. Longtime C/Unix programmer (retired), first time VBA programmer here.

    Why don’t ya’ll just use an environment variable?

    myUser = Environ(“username”)

    If myUser “Andy” then
    call production()
    else
    call test()

    The above is obviously semi-psuedo-code

    Choose whatever environment variable floats your boat that you can modify or otherwise uniquely test. Am I missing something?

14. I need to use conditional compilation becuase I do a set of Declare statements which refer to a library which is in different places on different machines. VBA does not interpert string constants at compile time in declares, as far as I can see.
    For example
    const MyPlotlib as string = “c:\plotstuff\MyPlotlib”
    Declare Function MyPlot Lib MyPlotlib (ByVal something…..
    results in “Compile error: Expected: string constant” (but I think it means string literal)
    The same is true using #const Myplotlib =”c:\plotstuff\MyPlotlib”
    I’d really like to set my conditional compilation constant from the command line when invoking Access.
    Happy to see any useful suggestions for workarounds

15. thanks all for this old brainstorming

16. I use a workbook-level defined name constant (bDEBUG_MODE) to determine whether I’m in Production Mode or Debug Mode.

    The defined name is assigned a value of False by the Workbook_Open routine, and can be toggled either manually or by using a routine in my “Developer’s Toolbar” workbook.

    This approach avoids problems associated with forgetting to change values when the production version of the workbook is released.

    The above approach seems to work well for me, but I’d certainly value any feedback from more experienced users.

17. Pingback: vba comments

18. I came to this page because Google had it as a high ranking result when searching for “vba Conditional Compilation Arguments”

    Wanted to point to a video using this conditional compilation argument approach at https://www.youtube.com/watch?v=CT7XkPXKVFw

Posting code? Use <pre> tags for VBA and <code> tags for inline.