When I first started programming for Excel, I decided it would be a good idea to standardise on a method for switching between Production mode and Testing mode.
You know, so you save face after your code just failed to build the where clause into a delete query.
I’d seen a few approaches but I eventually settled on a Public Const in the first module of the workbook.
However, I’ve just discovered another way to switch between Production and Test modes: Conditional Compilation Arguments
I think if I had the choice again, this is the way I’d have chosen.
You get to them from the Visual Basic Editor menu: Tools – VBAProject Properties…
Usually that’s VBAProject Properties, but will be whatever you’ve named your project.
You can also get to the same window by right-clicking the Project from the Project Explorer pane.
The Project Properties window appears…
The last textbox of the General tab is Conditional Compilation Arguments
This is where you put your Compiler Constants.
They behave just like #Const constants. They will save when your save the Workbook.
You can enter more than 1 argument by separating them with a colon :
So your VBA code could look as follows:
#If TestMode Then
MsgBox “This is Test Mode”, vbExclamation
MsgBox “This is Production Mode”, vbInformation
#If Not AdministratorMode Then
InputBox “What’s the password?”
I’m interested in your feedback – please post a comment.
How do you separate Production from Test?