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.

Methodology

I've made a conscious 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
        Else
            ThisWorkbook.DebugMode = False
        End If
    Else
        '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.

?Application.Username

Save the workbook, and try running the Workbook_Open component.