• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Pixcels.nl

  • Home
  • Categories
    • Excel apps
    • Pivottable stuff
    • PowerBI stuff
    • RGS en auditfiles
    • Sheet stuff
    • Userform stuff
    • VBA stuff
    • VBE stuff
    • XML stuff
  • About
You are here: Home / Categories / VBE stuff / Debug and trace in VBA

Debug and trace in VBA

2013/03/01 by Frans

Often when you debug your Excel app you want to monitor execution. Unfortunately the VBE has no built in trace tools. In this post I describe a simple but effective trace utility.

Whenever I am developing a non trivial excel application or a more or less complex userform I want to monitor execution of the code: which procedures are called, which events are raised, in what sequence. In Visual Studio we can set tracepoints. Unfortunately the VBE has no built in trace functions, other than the Call Stack. The call stack gives a trace of all functions that were executed in the current event, but doesn’t show information about the history: events that were handled before.
A simple solution is to add a Debug.Print statement to every function/event you want to monitor. Let this command print the name of the procedure to the VBE Immediate Window, and you have a basic but complete trace of all functions that need monitoring.
Instead of a simple Debug.Print we can call our own procedure, like this:

Private Sub UserForm_Initialize()
    LogStart "UserForm_Initialize", Me.Caption
    ' your code
    LogPrint "Half way"
    ' more code
    LogEnd
End Sub

When we add the LogStart and LogEnd to all userform events and then open the userform the resulting trace list could look like this:

72624,82  UserForm_Initialize UserForm1
72625,32      Half way  
72625,32  TextBoxA_Enter   
72625,33  UserForm_Activate   

The line number is the Timer. It helps to analyze the performance of our app. In the example it took the userform 0.5 seconds to get “half way”. Maybe reason to check “your code”?

The module LogTrace has three procedures:

  • LogStart: prints the parameters to the VBE Immediate Window and indents a level;
  • LogEnd: prints “End” and its parameters and unindents one level;
  • LogPrint: prints its parameters at the current level.

Every procedure has a few optional parameters. Usually you will start every function with LogStart “ProcedureName” and end the procedure with LogEnd. Tip: use a tool like MZ-Tools to insert these lines in your code easily.

A few contstants in the module make it easy to customize the layout of the trace list:

  • bNOTRACE: turns the trace function on or off completely;
  • bNOEND: print or don’t print “End” lines to the VBE Immediate Window (a condensed mode);
  • bNOPRINT: print or don’t print the LogPrint lines;
  • bNOTIMER: print or don’t print the line number;
  • iDELAY: the number of seconds between two Log commands after which a blank line is printed.

Let me know which options you miss. To see this utility at work: The sequence of events in workbooks
Finally note that the buffer of the VBE Immediate Window has a limit of 199 lines. If you need more you could easily redirect the output to a textfile or a worksheet.
I hope this pixcel helps. Excel!

Download

DebugAndTrace-v1.xlsm

Filed Under: VBE stuff Tagged With: Debug

Primary Sidebar

Recent posts

  • The Grand Totals range of a pivottable in Excel
  • Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • AuditfileValidator for Excel: validate and inspect Xml auditfiles
  • SAF-T auditfiles for test and development
  • Een postcodetabel op basis van BAG Extract 2.0

Recent comments

  • Harry Cuntapay on Replace drill-through for Power Pivot and Power BI pivottables in Excel
  • Lucio on Disable Shift key on open
  • Lucio on Disable Shift key on open
  • Julius Peter on The sequence of events in workbooks
  • Ben on The sequence of events in workbooks

Categories

Tags

ActiveControl ActivePrinter ADCS auditfiles BAG Connection Custom tab Database Debug Direct Debit drill-through Events event trapping Focus IBAN ISO 21378 MDI MSXML Multiple Document Interface normalize PAIN.008 PivotTable postcode Power BI RGS Ribbon SAF-T schema SEPA SOM Table Userforms validate VBA XAF xml XPath

Copyright © 2023 · eleven40 Pro on Genesis Framework · WordPress · Log in