When I’m building a spreadsheet application, invariably I have to have a routine that initializes the application. Often times I have sheets I need to hide from the end user. These are usually sheets full of configuration settings or system tables, things like that. I might also have toolbars that call certain functions or routines. I might have password protection on some of the sheets. You get the idea.

What I usually do is to add a small function that allows me to crack open the full workbook without having to go into the code itself to open everything up.

I start by putting an empty file of some sort in the same directory location as the application workbook. I’ll call it something like “debug.ini” or “password.txt” or “backdoor.ini” or something similar. It can actually be anything really. The file won’t hold anything, it just needs to be present.

Once the file is in place, you can add the code below as a function in a module. If you’re following any of the practices from “Professional Excel Development” by Bullen, Bovey, and Green, then you’d probably stick this in a module called MSystemCode. But it’s totally up to you.

Function bDebugMode() As Boolean
' Description: This checks the whether the application should be opened in debug mode.
'
' Author: Scott Lyerly
' Contact: scott.c.lyerly@gmail.com
'
' Name: Date: Init: Modification:
' bDebugMode 21-Nov-2013 SCL Original development
'
' Arguments: None
'
' Returns: Boolean TRUE = success, FALSE = failure
'

' Review the length of the path and file name of the debug file using Dir.
If Len(Dir(ThisWorkbook.Path & "\debug.ini")) = 0 Then
' The the lenght is zero (0), the file does not exist and
' we are not in debug mode.
bDebugMode = False
Else
' If the length is greater than zero (0), we are in debug mode.
bDebugMode = True
End If

End Function

I’ve got plenty of comments in the code, so I don’t think I need to break it down further here.

Once this function is in place, add the code below to the Workbook_Open event so that it fires whenever the file is opened.

Private Sub Workbook_Open()

' Check if the application is in debug mode.
' If it is not, initialize the application for the user.
If Not bDebugMode Then
InitializeApplication
Else
' If we are in debug mode, open the file up and unprotect the Demand tab.
OpenFileForDevelopment
End If

End Sub

You can see that I have two branches this application can go down. One is to proceed as normal, with the application not opening in debug mode. I typically call the routine for that branch something like “InitializeApplication”. It’s where I store all the code I want to run before the user can use the file.

The second branch might be called “OpenFileForDeveloment”. This is the branch to go down when I want to unhide everything, unprotect everything, and prevent user interface items like toolbars from being created.

Using this practice, I can open the file in a user mode or a debug/development mode without touching a single line of code.