On Tuesday, November 6, 2018 at 4:43:23 PM UTC-5, Desmond Walsh wrote:
I have developed a DebugSupport class that gives the option of directing Debug.Print output to Immediate Window or Immediate Window and disk file or disk file only. This class has a method output_line. So, in my code I replace
Debug.Print "Line: " & "Test stuff"
with
Public dp as New DebugSupport
dp.output_line ("Line: " & "Test stuff")

This works fine. However if the Debug.Print message contains formatting functions such as Tab() or Spc(), then I get a compile error i.e.
dp.output_line ("Line: "; Tab(10); "Test stuff") Compile error

So, my question is how do I pass such a message into a subroutine.

The method output_line does two things. It puts the input argument into a Debug.Print statement and then prints it to disk. I assume that the Tab() function is specific to Debug.Print and that I would have to supply some code to replicate this formatting control while printing to disk. That is not difficult. But I am stumped by the compile error.
Thanks

In my opinion, the problem is due to a limitation on how Debug.Print handles a message enclosed in a string. It is unable to recognize usage of the formatting functions Tab() and Spc(). I have developed the following workaround to overcome this restriction ;

1 Add a new method ReplaceDebugStatements to the class DebugSupport. It
modifies Debug.Print statements as follows ;
;Tab(N) - & "Tab(N)";
;Spc(N) - & "Spc(N)";
Debug.Print message - dp.output_line (message)
NOTE 1: The code is modelled on Chip Pearson's SearchCodeModule
(www.cpearson.com/Excel). The edits are done with regular expressions
using VBScript.RegExp
NOTE 2: The code handles the following complexities ;
The token: Debug.Print appears in a comment
A Debug.Print statement is commented out
A Debug.Print statement is split over several lines with the last
line containing an optional comment
NOTE 3: This code is an example of an Excel workbook modifying its own VBA
code. The target of ReplaceDebugStatements is the VBA source code in a
different module of the workbook

2 Add code to the method output_line that replaces occurrences of Tab() and
Spc() by correct spacing format. The implementation uses
VBScript.RegExp,and the functions Replace,Split and Space

For maintenance ease, the ClassModule DebugSupport resides in a dedicated workbook debugsupport.xlsm. The process of incorporating this code in a new workbook is automated by the subroutine ArrangeDebugSupport in PERSONAL.XLSM. ArrangeDebugSupport creates the appropiate modules and copies in the source code from debugsupport.xlsm.

With these changes, any VBA source module may be switched from debug processing handled by Debug.Print to debug processing handled by ClassModule DebugSupport. The important gain is the ability to capture the output flow in the Immediate Window in a disk file.


The functions Tab() and Spc() are very useful in helping to format Debug.Print output with columns of data that need to be lined up. A simple strategy is to work with Debug.Print until the output is formatted optimally. Then switch to DebugSupport debug processing.