• Creating a Debugging Mode

    I can't claim the original idea for this article. The concept actually comes from Professional Excel Development, but I developed the code, such as it is here, on my own. The reason for this was because I couldn't find it in the book when I went looking for it, so I knocked up my own version, and decided to post that here.

    This is kind of a neat little routine, which adds a "DebugMode" property to the ThisWorkbook module. It also checks the MS Office username every time someone opens the file, and if it's me, it asks me if I want to use Debugging Mode.

    Why use a Debug Mode?
    There are a ton of things that you can use this for, so many that I can't possibly think of them all. I'll give you some examples of exactly what I've done with it, and how it may be useful to you.

    Usually when I develop addins, I'll add a custom menu to the menu bar, which is where I'll hold the kickoff routines for anything I've programmed. With the DebugMode, this has given me the ability to add a submenu, if DebugMode is true, that holds all of my utility routines. Routines to kick of build and version changes, unprotecting/protecting all worksheets are just a couple of examples of routines that I may throw in there. If DebugMode is false, however, it means that none of my users ever see those menu items.

    I've encapsulated print code in a test of the DebugMode property. If DebugMode is true, the code uses PrintPreview, in lieu of Printout. It saves me a ton of paper when I'm Alpha testing.

    You could even encapsulate all of your public routines and functions, which you want called from menus, in the test. If it fails, the routine could be triggered to exit. This could help protect routines that are for your use, but need to be made public for one reason or another.

    I've made a concious decision to use a variable to hold the value of the DebugMode property. The reason for this is that the variable goes out of scope when the workbook is closed. Because Boolean values are initiated with a default value of False, this means that by default, DebugMode will always be off when the workbook is opened, and therefore only set to true when the correct conditions, (in this case it's opened by me,) are met. It could also, of course, be triggered by a menu item or other routine. I would suggest that you do some kind of password on it, however, to keep the curious out if you do create a full submenu of debugging routines.

    Code Required:
    Place the following code in the ThisWorkbook module.

    Private Const strCoderName = "Ken Puls"
    Private bDebugMode As Boolean
    Property Let DebugMode(b As Boolean)
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro purpose: To set the value of the DebugMode property
        bDebugMode = b
    End Property
    Property Get DebugMode() As Boolean
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro purpose: To return the value of the DebugMode property
        DebugMode = bDebugMode
    End Property
    Private Sub Workbook_Open()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro purpose: To check if the workbook is being opened by the
    '   coder and, if so, offer to open in debuggging mode
        'Check if the username matches the Coder's name
        If Application.UserName = strCoderName Then
            'It does, so set to Debugging Mode if required
            If MsgBox("Hello " & strCoderName & "!" & vbNewLine & _
                "Would you like to use debugging mode?", _
                vbYesNo + vbQuestion) = vbYes Then
                ThisWorkbook.DebugMode = True
                ThisWorkbook.DebugMode = False
            End If
            'It doesn't, so set Debugging Mode to False
            ThisWorkbook.DebugMode = False
        End If
    End Sub
    You will want to modify the first line to change my name to yours. Make sure that it matches your MS Office username. You can get this by checking the Tools|Options|General tab, and looking at the UserName field. Alternately, just typing the following in the immediate window and hitting enter.

    Save the workbook, and try running the Workbook_Open component.

    This article has also been published at Professional Office Developers Association.


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!