Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 12th 18, 11:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,001
Default Problem encountered with Debug.Print message being passed to subroutine

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.


Pretty complex approach compared to the simplicity of my suggestion!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


  #12   Report Post  
Old November 14th 18, 06:36 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2014
Posts: 22
Default Problem encountered with Debug.Print message being passed to subroutine

On Monday, November 12, 2018 at 6:10:15 PM UTC-5, GS wrote:
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.


Pretty complex approach compared to the simplicity of my suggestion!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus


I can't argue with that. But I really liked the functionality of Tab(N) that allows you to specifically pick any column. Unlike vbTab that jumps to a discrete set of Tab positions.

I was also unaware of regular expression processing in VBA until I saw a reference to VBScript.RegExp. As a Perl programmer who relies heavily on regular expressions I was delighted to find decent regexp support in VBA.

Sometimes coding has to be fun !!.

Thank you for contributing to the discussion.
  #13   Report Post  
Old November 15th 18, 01:31 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,001
Default Problem encountered with Debug.Print message being passed to subroutine

I can't argue with that. But I really liked the functionality of Tab(N) that
allows you to specifically pick any column. Unlike vbTab that jumps to a
discrete set of Tab positions.

I was also unaware of regular expression processing in VBA until I saw a
reference to VBScript.RegExp. As a Perl programmer who relies heavily on
regular expressions I was delighted to find decent regexp support in VBA.

Sometimes coding has to be fun !!.

Thank you for contributing to the discussion.


I say "go with what you know" and so makes sense now why you chose this
approach. And yes, coding IS fun!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Bypass error message to debug Teddy Excel Programming 5 March 8th 10 12:43 AM
Can't get length of a variant array passed to a subroutine Craig Remillard Excel Programming 6 November 23rd 09 08:27 PM
Error Message: NULL IDispatch passed to Autowrap() Aviral Sharma Excel Discussion (Misc queries) 0 March 13th 09 05:13 AM
VBA causing "Excel has encountered a problem" message arch Excel Programming 5 May 23rd 07 07:18 PM
Problem encountered Pat Setting up and Configuration of Excel 1 March 3rd 06 03:01 PM


All times are GMT +1. The time now is 12:52 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017