Is it good practice to leave "Debug.Print" instructions in code that goes in "production"?

Question

In Excel VBA, is it good practice to leave Debug.Print instructions in code that goes in "production" ? That is quite useful to debug the sheets realtime at the user's machine when something goes wrong. Does it affect performance when Visual Studio is closed ? If not, what would you advise ?

You say "Visual Studio" but you are talking about VBA, right? Do you mean VBE? — Gaijinhunter, Dec 07 '11 at 09:14
Ahhh, yes I guess I just understand what people referred to as VBE now... Yes I guess so :) [ALT + F11] — BuZz, Dec 07 '11 at 09:17
1) If it is important to capture what the user was doing when the things went wrong, a transaction log would seen a better option. Start a new file per run or per day; delete any over 48 hours old. Yes there is a performance cost but how would you measure it. 2) Visual Studio is MS's development environment for its professional languages. VB 2010 is hundreds of times faster than VBA/VBE and has thousands of cool facilities. You can access Excel from it if you want to use a worksheet. — Tony Dallimore, Dec 07 '11 at 13:37
According to https://msdn.microsoft.com/en-us/library/aa716276(v=vs.60).aspx , debug.print statements are removed when Visual Basic is compiled.... but unlike VB, VBA is interpreted... (or is it compiled at run-time?), so I'm not sure about that one. I wanna say interpreted, because I can write VBA while at a breakpoint :P — ThomasRones, Dec 29 '17 at 23:41

iDevlop · Accepted Answer · 2011-12-07T08:57:06.160

29

Debug.Print instruction DO have a small performance cost. So I would avoid them in loops that are executed a zillion times. Except for those cases, I think it's ok to keep them.
You could also use conditional compilation directives (#if) in combination with a compiler constant (#const) to enable/disable them globally without performance impact.

#CONST developMode = True

sub Xyz
  #If developMode Then
    Debug.Print "something"
  #End If
End Sub

edited Dec 07 '11 at 08:57

answered Dec 07 '11 at 08:47

iDevlop

24,841
11
90
149

3

@iDevelop +1 Learn something new everyday - didn't realise Excel supported conditional compilation! Thanks. – dash Dec 07 '11 at 09:00
1

Clear enough to desserve a +1 :). Btw, conditional compilation (and some useful other stuff) was discussed in this interesting thread: http://stackoverflow.com/questions/1070863/hidden-features-of-vba – JMax Dec 07 '11 at 09:08
Do you have a source for this? – JimmyPena Dec 07 '11 at 18:18
That's the way I do it, with a global variable of `DebugMode`. I have a small check on `ThisWorkbook.Save` to flag if `DebugMode` is True, helps stop it sneaking into production. – Lunatik Jan 05 '12 at 13:22
1

@Lunatik: that's fine too, but in regarding performance impact, a global (well any "normal") variable WILL have an impact on performance, while a compiler constant will not (when set to production mode). – iDevlop Jan 05 '12 at 19:53

dash · Answer 2 · 2011-12-07T09:29:28.647

I usually have two versions; prod without debugging, and prod with debugging. That, combined with the catchall error handler logging, means that if a user experiences issues, I can deploy the debug version to them and they can run that up.

I have a macro that I run that comments out the debug.print statements, so it's not a real maintenance overhead.

The problem with running a debug version all the time (and, with Excel VBA it's not usually a performance thing) is that your app is constantly emitting information that it doesn't need too. In an environment with controlled spreadsheets, for example, this can be seen as a bad thing.

In terms of global error handling, you still need the On Error GoTo statement for every function you want error handling in. You can, however, pipe these to a common function:

Public Function HandleTheNastyErrors(E As ErrObject, ByVal writeLog As Boolean = True) 

    Select Case E.Number 

    Case xxx 

        ...specific error handling... 

    Case Else 
        ... Display a message to the user about how you dont know what happened....             
    End Select 

    If writeLog Then

       ...Log Writing Code...

    End If

End Function

And then, OnError:

ErrorHandler:
 Call HandleTheNastyErrors(Err, True)

Show do the trick

Could you comment on your "catch all" ? I don't really have ideas of how to handle errors smartly and somehow generically so far. Thanks ! — BuZz, Dec 07 '11 at 09:01
passing the error object it self to the functon...that's an interesting idea. — iDevlop, Oct 13 '16 at 12:10

Is it good practice to leave "Debug.Print" instructions in code that goes in "production"?

2 Answers2