Creating a Debugging Mode
Posted by Ken Puls, CMA, Microsoft MVP (Excel) on August 14th, 2007
Introduction:
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 of an Excel workbook. 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. This concept can also be deployed, with some modification, to other Office applications.
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 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 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.
Save the workbook, and try running the Workbook_Open component.
Article Source:
This article was originally published, and is maintained at Excelguru.ca.