Quick switch between "PROD" and "DEV" modes

Question

I develop VBA code using some advanced automated macros that do "plumbing" and extra debugging during DEV phase. However, when I ship the files to users (PROD phase), I want all of this to be turned off.

Those switches between DEV and PROD happen quite a lot (git + devops methodology).

What would be the best / most elegant way to implement a quick "switch" between DEV and PROD? Yes, I could just use a global Const isDebug (and I did it for some projects), but it's burdensome, inelegant and error-prone (more than once I forgot to "flip" the switch - as I said earlier, quick and agile "almost" CI/CD pipeline).

EDIT

Right now I'm using a quick dirty hack (I don't like dirty hacks) that checks whether the Excel file is being ran from a location with my login name in the path. If yes - that means I'm working on the file. If no - someone else is using it and we don't need any debugging modes. But it's ugly and I can foresee a lot of problems (for one, tomorrow someone else might be developing those VBAs... so the debug code would have to be rewritten).

Answer 1

In VBE goto Extras -> Properties of VBAProject and enter an argument for compiling eg:

DEV_MODE = -1

^{Sorry for the German screenshot.}

Then use the following code

Option Explicit

Sub test()
    'the following #If is a compile condition
    #If DEV_MODE Then
        'this is only compiled in dev mode
        Debug.Print "debug mode is on"
    #Else
        'this is only compiled in production mode
        Debug.Print "debug mode is off"
    #End If
End Sub

Note that in VBA -1 is True and 0 is False. Switching the compile argument DEV_MODE = -1 will switch the dev mode in the whole VBA project.

Answer 2

You can use a textfile in workbooks path as condition.

If the file is missing ('DebugMode.txt' in example) or the debug option ('DebugMode:On' in textfile) is not set, turn off debug mode, else turn it on.

If you deploy the workbook without the textfile, debug mode is off.

If some else needs debug mode on, share/create the file.

Example code:

Function IsDebugMode() As Boolean

Const DebugOptionFileName As String = "DebugMode.txt" 
Const DebugIsOnString As String = "DebugMode:On"
Const ForReading as Long = 1

Dim txtStream As Object
Dim DebugOptionFilePath As String

IsDebugMode = False
DebugOptionFilePath = ThisWorkbook.Path & "\" & DebugOptionFileName

With CreateObject("Scripting.Filesystemobject")
    If .FileExists(DebugOptionFilePath) Then
        Set txtStream = .OpenTextFile(DebugOptionFilePath, ForReading, False)
        Do Until txtStream.AtEndOfStream
            If txtStream.ReadLine = DebugIsOnString Then
                IsDebugMode = True
                Exit Do
            End If
        Loop
    End If
End With

End Function

Answer 3

I don't like the attempt of having the switch as Compiler-Setting - forgetting to change it is even more likely than changing a Const definition.

I find your attempt checking username, devicename, path of the file or stuff like that not too bad. Just put the logic into a small function isTestMode() (even if it is a one-liner) which returns a boolean. Within this function, it is easy to change to logic if another person will take over the work. Use this function (and nothing else!) to check if or if not to execute your debugging statements.

Public Function isTestMode() as boolean
    ' Checking current user
    isTestMode = (environ("Username") = "FunThomas")
    ' Checking device name
    isTestMode = (environ("ComputerName") = "MySuperPC")
    ' Checking if a specific file is present
    isTestMode = Dir(thisworkbook.Path & "\IsTestMode") <> "")
End Function