I use the OutputDebugString API in Windows to send debugging messages without the line limitations of the VBA Immediate Window. This is also useful when tracing the causes of crashes which kill the Excel process.
I use TraceSpy to capture the debug output. It is faster than DbgView which tends to bog down when there are many traces.
Event Tracing for Windows (ETW) is faster than OutputDebugString. Calling ETW from VBA takes 26 microseconds, three times the speed of OutputDebugString 77us. And WpfTraceSpy correctly displays Unicode strings like Cyrillic.
Simon Mourier has provided 32 and 64 bit VBA modules for WpfTraceSpy on GitHub.
I have contributed a module ETWModule3264.bas suitable for Excel add-ins that need to run without modification in either 32 or 64 bit version of Office.
This has been tested in 32-bit Excel 365 and 64-bit Excel 365 on Windows 10.
I work only in Excel, but it should apply to other Office products too.
I noticed that the speed of the ETW output depends on the folder the xlsm is in. It can take as little as 1 microsecond for an ETW call, or as much as 26 as shown above.
I realised that the fast performing xlsm files are in Excel Trusted Locations. I wonder is Windows Defender sniffing at the untrusted API calls & slowing them down? In Trusted Locations, OutputDebugString takes 23 us, ETW takes 1 us. It is so fast because all EventWriteString does is push the event on to the Trace event queue and return, so there is very little overhead.
This could be an idea for API performance enhancement, if this applies to other Win APIs called from VBA.
So… make that “Event Tracing is twenty times faster than OutputDebugString” !