I have developed a DebugSupport class that replaces using Debug.Print statements for debugging in Excel VBA code development. It gives the option of directing output to Immediate Window or Immediate Window and disk file or disk file only. The disk file is uniquely named and opens/closes as my application runs. This class has a method output_line. So, in my code I replace
Debug.Print "String including Tab() and Spc() "
with
Public dp as New DebugSupport
dp.output_line("String modified to mimic Tab and Spc functionality")

This supports a very useful debugging environment. My problem is with the class variable dp (declared global) and Personal.xlsb. I don't seem to be able to share dp between my app and macros in Personal.xlsb.
My Workbook Personal.xlsb
Main calls Level1macro calls Level2macro

If I open an instance of DebugSupport in Main, dp is unavailable in Level1macro. If I have a class module DebugSupport in both MyWorkbook and Personal then I end up logging to two separate disk files which is not what I want.

On Saturday, April 18, 2020 at 10:36:30 AM UTC-4, Desmond Walsh wrote:
I have developed a DebugSupport class that replaces using Debug.Print statements for debugging in Excel VBA code development. It gives the option of directing output to Immediate Window or Immediate Window and disk file or disk file only. The disk file is uniquely named and opens/closes as my application runs. This class has a method output_line. So, in my code I replace
Debug.Print "String including Tab() and Spc() "
with
Public dp as New DebugSupport
dp.output_line("String modified to mimic Tab and Spc functionality")

This supports a very useful debugging environment. My problem is with the class variable dp (declared global) and Personal.xlsb. I don't seem to be able to share dp between my app and macros in Personal.xlsb.
My Workbook Personal.xlsb
Main calls Level1macro calls Level2macro

If I open an instance of DebugSupport in Main, dp is unavailable in Level1macro. If I have a class module DebugSupport in both MyWorkbook and Personal then I end up logging to two separate disk files which is not what I want.

If I have the class module DebugSupport in MyWorkbook only, copy dp into dp1 (Dim as DebugSupport) and pass dp1 as a Variant into Level1macro, the code works but fails in Level2macro. I'm not sure I understand why that kludge worked at all, but it has given me some hope that there may be a solution.

So could anyone direct me to a solution or point out that my desired goal is technically impossible.

Thank you


Actually, I have found a solution that is a little convoluted but it works
In MyWorkbook
Public dp as New DebugSupport (Global class variable)
Public dp1 as DebugSupport (Declared outside macros)
set dp1=dp
call level1macro (dp1)

In Personal.xlsb
Include the DebugSupport class module
Public dp as Variant (Declared outside macros)

Sub level1macro (dp1 as Variant)
set dp=dp1

call level2macro()

Now dp.output_line statements work everywhere. One logging file is opened/closed.

I'm pleased that it works but not sure why it works and if I am breaking some VBA rules

On Saturday, April 18, 2020 at 12:19:11 PM UTC-4, Desmond Walsh wrote:
On Saturday, April 18, 2020 at 10:36:30 AM UTC-4, Desmond Walsh wrote:
I have developed a DebugSupport class that replaces using Debug.Print statements for debugging in Excel VBA code development. It gives the option of directing output to Immediate Window or Immediate Window and disk file or disk file only. The disk file is uniquely named and opens/closes as my application runs. This class has a method output_line. So, in my code I replace
Debug.Print "String including Tab() and Spc() "
with
Public dp as New DebugSupport
dp.output_line("String modified to mimic Tab and Spc functionality")

This supports a very useful debugging environment. My problem is with the class variable dp (declared global) and Personal.xlsb. I don't seem to be able to share dp between my app and macros in Personal.xlsb.
My Workbook Personal.xlsb
Main calls Level1macro calls Level2macro

If I open an instance of DebugSupport in Main, dp is unavailable in Level1macro. If I have a class module DebugSupport in both MyWorkbook and Personal then I end up logging to two separate disk files which is not what I want.

If I have the class module DebugSupport in MyWorkbook only, copy dp into dp1 (Dim as DebugSupport) and pass dp1 as a Variant into Level1macro, the code works but fails in Level2macro. I'm not sure I understand why that kludge worked at all, but it has given me some hope that there may be a solution.

So could anyone direct me to a solution or point out that my desired goal is technically impossible.

Thank you


Actually, I have found a solution that is a little convoluted but it works
In MyWorkbook
Public dp as New DebugSupport (Global class variable)
Public dp1 as DebugSupport (Declared outside macros)
set dp1=dp
call level1macro (dp1)

In Personal.xlsb
Include the DebugSupport class module
Public dp as Variant (Declared outside macros)

Sub level1macro (dp1 as Variant)
set dp=dp1

call level2macro()

Now dp.output_line statements work everywhere. One logging file is opened/closed.

I'm pleased that it works but not sure why it works and if I am breaking some VBA rules


A little further investigation showed that the situation is simpler. Setting up the class DebugSupport in PERSONAL.xlsb is completely irrelevant. So here is the trick

In MyWorkbook
Public dp as new DebugSupport (Global class variable)
Public dp1 as DebugSupport (Declared outside macros)
set dp1 = dp
call LinkToDebugSupport (dp1) (A PERSONAL subroutine)

In PERSONAL
Public dp as Variant (Declared outside macros)
Sub LinkToDebugSupport (dp1 as Variant)
set dp = dp1
End Sub

Now dp.output_line is available everywhere with logging to a single uniwuely named file.

One side benefit of the covid crisis. Its like a retirement within a retirement. Lots of time to investigate esoteric issues !

It sounds like you want to use your class as a separate object rather than
include it in each VBA Project you want to use it in. Not a good idea! Just
insert the class into each project you want to use it in so it is exclusive to
that project and NOT USED BY ANY OTHER PROJECTS!

Then you should create a variable local to each routine that uses it,
instantiate it, then destroy it when done with it after each use.

Not sure why you are going to this extent when Debug.Print works for most
developers during design time, but most will log to a text file via a central
logger component, which, in most cases is part of their error handling system.
Ideally, though, the process to write to the log file should be a stand-alone
reusable routine that accepts a FileOut, TextOut, and output mode as args. For
example:


Sub FSO_WriteTextFile(FileOut, TextOut, ioMode)
' Reusable procedure that Writes or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**

Dim oFSO, oFile

On Error GoTo ErrHandler
Set oFSO = CreateObject("Scripting.FileSystemObject")
If ioMode = lOpenA Then '//add to file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenA)
oFile.Write (vbCrLf & TextOut)
Else '//overwrite file
Set oFile = oFSO.CreateTextFile(FileOut, lOpenW)
oFile.Write (TextOut)
End If

'ErrHandler:
oFile.Close: Set oFSO = Nothing: Set oFile = Nothing
End Sub

-OR-

Sub WriteTextFile(ByVal TextOut$, Filename$, _
Optional AppendMode As Boolean = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Const sSrc$ = "WriteTextFile"

Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then '//start a new line
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else '//overwrite existing file
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, sSrc, Err.Description
End Sub 'WriteTextFile()

--
Garry

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

On Sunday, April 19, 2020 at 12:20:20 AM UTC-4, GS wrote:
It sounds like you want to use your class as a separate object rather than
include it in each VBA Project you want to use it in. Not a good idea! Just
insert the class into each project you want to use it in so it is exclusive to
that project and NOT USED BY ANY OTHER PROJECTS!

Then you should create a variable local to each routine that uses it,
instantiate it, then destroy it when done with it after each use.

Not sure why you are going to this extent when Debug.Print works for most
developers during design time, but most will log to a text file via a central
logger component, which, in most cases is part of their error handling system.
Ideally, though, the process to write to the log file should be a stand-alone
reusable routine that accepts a FileOut, TextOut, and output mode as args.. For
example:


Sub FSO_WriteTextFile(FileOut, TextOut, ioMode)
' Reusable procedure that Writes or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**

Dim oFSO, oFile

On Error GoTo ErrHandler
Set oFSO = CreateObject("Scripting.FileSystemObject")
If ioMode = lOpenA Then '//add to file contents
Set oFile = oFSO.OpenTextFile(FileOut, lOpenA)
oFile.Write (vbCrLf & TextOut)
Else '//overwrite file
Set oFile = oFSO.CreateTextFile(FileOut, lOpenW)
oFile.Write (TextOut)
End If

'ErrHandler:
oFile.Close: Set oFSO = Nothing: Set oFile = Nothing
End Sub

-OR-

Sub WriteTextFile(ByVal TextOut$, Filename$, _
Optional AppendMode As Boolean = False)
' Reusable procedure that Writes/Overwrites or Appends
' large amounts of data to a Text file in one single step.
' **Does not create a blank line at the end of the file**
Const sSrc$ = "WriteTextFile"

Dim iNum%
On Error GoTo ErrHandler
iNum = FreeFile()
If AppendMode Then '//start a new line
Open Filename For Append As #iNum: Print #iNum, vbCrLf & TextOut;
Else '//overwrite existing file
Open Filename For Output As #iNum: Print #iNum, TextOut;
End If

ErrHandler:
Close #iNum: If Err Then Err.Raise Err.Number, sSrc, Err.Description
End Sub 'WriteTextFile()

--
Garry

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



Thank you for looking at my posts. I believe we had a discussion before about why it was neccessary to replace Debug.Print. The reason is because I wanted the option to log the Immediate Window stream and I could not put a Debug.Print statement into a string if it contained the formatting functions Tab() and Spc(). I find these functions very useful in getting easy to read output. My class has a method to replace Debug.Print statements with equivalent dp.output_line statements.

In a perfect world PERSONAL should only contain commonly used macros that are fully debugged and therefore should not need to generate debug output. I find I tend to develop applications that rely heavily on my PERSONAL macros. So during development having the entire code body (application+ macros called from PERSONAL) output to a single disk stream is very useful. My application creates its own instance of DebugSupport and now without any coding modification in PERSONAL I get my desired logging.

The situation is less satisfactory when I want to run a PERSONAL macro stand-alone and if I want debug logging. Again I create an instance of DebugSupport. But I have to manually disable the declaration (Public dp as Variant). I see no way to do this programatically. I have the declaration in a separate module so its a quick edit. However, a more elegant solution would be more satisfactory.

I checked my last posting again. And of course, using the additional variable dp1 on the Workbook side and the PERSONAL side is completely unnecessary. So the only requirement is that the workbook passes dp ( new DebugSupport) to PERSONAL as a Variant and dp is declared as a Public Variant in PERSONAL.

Thanks for the detailed explanation. While your detailed explanation makes
sense in concept, I'm having difficulty understanding the following leading
comments.

Tab and spaces are normal string content and so if you build a string
containing them it can be output to a text file and/or the ImmediateWindow at
any time.

Thank you for looking at my posts. I believe we had a discussion before
about why it was neccessary to replace Debug.Print. The reason is because I
wanted the option to log the Immediate Window stream and I could not put a
Debug.Print statement into a string if it contained the formatting functions
Tab() and Spc(). I find these functions very useful in getting easy to read
output. My class has a method to replace Debug.Print statements with
equivalent dp.output_line statements.

If you are trying to 'collect' progressive Debug.Print outputs for a design
session (as I sometimes do so I can review 'actual' outputs), your class seems
like a lot of work unless you are using it as a subclassing component where
each project instantiates its own instance independant of any other projects
using it simultaneously; - in this case it makes sense to go the distance!

My approach is project-centric in that any D.P statements get passed to a
function similar to the WriteTextFile ones I posted earlier; the output to the
log is same as how error.log stuff gets done, but the new text also goes to IW.
I suppose having a 'globally available' solution is not much different (and
cudos to you for doing that), I just prefer my approach because it's integrated
with my central error handling system in every project.

--
Garry

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

On Monday, April 20, 2020 at 1:21:02 PM UTC-4, GS wrote:
Thanks for the detailed explanation. While your detailed explanation makes
sense in concept, I'm having difficulty understanding the following leading
comments.

Tab and spaces are normal string content and so if you build a string
containing them it can be output to a text file and/or the ImmediateWindow at
any time.

Thank you for looking at my posts. I believe we had a discussion before
about why it was neccessary to replace Debug.Print. The reason is because I
wanted the option to log the Immediate Window stream and I could not put a
Debug.Print statement into a string if it contained the formatting functions
Tab() and Spc(). I find these functions very useful in getting easy to read
output. My class has a method to replace Debug.Print statements with
equivalent dp.output_line statements.

If you are trying to 'collect' progressive Debug.Print outputs for a design
session (as I sometimes do so I can review 'actual' outputs), your class seems
like a lot of work unless you are using it as a subclassing component where
each project instantiates its own instance independant of any other projects
using it simultaneously; - in this case it makes sense to go the distance!

My approach is project-centric in that any D.P statements get passed to a
function similar to the WriteTextFile ones I posted earlier; the output to the
log is same as how error.log stuff gets done, but the new text also goes to IW.
I suppose having a 'globally available' solution is not much different (and
cudos to you for doing that), I just prefer my approach because it's integrated
with my central error handling system in every project.

--
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 is the problem I had with handling Debug.Print statements ;

Sub testz()
Dim line As String
Debug.Print "Hello World"
line = "Hello World"
Debug.Print line
Debug.Print "Hello"; Tab(10); "World"
line = "Hello; Tab(10); World"
Debug.Print line
End Sub

Output:
Hello World
Hello World
Hello World
Hello; Tab(10); World

Once I put the statement in a string, I seem to lose the Debug.Print ability to parse Tab() and Spc() correctly.

I apologetically must provide another flip flop on my found solution to passing a global class variable to PERSONAL.xlsb. The use of the Public variable dp1 is essential on the PERSONAL side. If LinkToDebugSupport's input parameter is named dp then dp behaves correctly in LinkToDebugSupport (dp.output_line works) but causes a runtime error in any other PERSONAL macro using it. I don't really understand what is going on here and am amazed that I stumbled on something that works !.

In MyWorkbook
Public dp as new DebugSupport (Global class variable)
call LinkToDebugSupport (dp) (A PERSONAL subroutine)

In PERSONAL
Public dp as Variant (Declared outside macros)
Sub LinkToDebugSupport (dp1 as Variant) (Declaring dp as the parameter makes
dp local to this subroutine)
set dp = dp1
End Sub

Hi Desmond,

Am Mon, 20 Apr 2020 12:03:08 -0700 (PDT) schrieb Desmond Walsh:

Sub testz()
Dim line As String
Debug.Print "Hello World"
line = "Hello World"
Debug.Print line
Debug.Print "Hello"; Tab(10); "World"
line = "Hello; Tab(10); World"
Debug.Print line
End Sub

line with a line wrap:
line = "Hello" & Chr(10) & "World"
or
line = "Hello" & vbCrLf & "World"

words with a tab:
line = "Hello" & vbTab & "World"

Sub Test()
Dim line As String
Debug.Print "Hello World"
line = "Hello World"
Debug.Print line
line = "Hello" & Chr(10) & "World"
Debug.Print line
line = "Hello" & vbCrLf & "World"
Debug.Print line
line = "Hello" & vbTab & "World"
Debug.Print line
End Sub

Output:
Hello World
Hello World
Hello
World
Hello
World
Hello World


Regards
Claus B.
--
Windows10
Office 2016

As Claus explains, your use of Tab() (and Spc()) is not correct VB syntax and
so it why it doesn't work in VBA. My bad for assuming you were using correct
syntax; - all of these formatting functions work for me and this is why I
didn't understand the problem you were having. Sorry I missed that!

--
Garry

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

On Monday, April 20, 2020 at 4:45:03 PM UTC-4, Claus Busch wrote:
Hi Desmond,

Am Mon, 20 Apr 2020 12:03:08 -0700 (PDT) schrieb Desmond Walsh:

Sub testz()
Dim line As String
Debug.Print "Hello World"
line = "Hello World"
Debug.Print line
Debug.Print "Hello"; Tab(10); "World"
line = "Hello; Tab(10); World"
Debug.Print line
End Sub

line with a line wrap:
line = "Hello" & Chr(10) & "World"
or
line = "Hello" & vbCrLf & "World"

words with a tab:
line = "Hello" & vbTab & "World"

Sub Test()
Dim line As String
Debug.Print "Hello World"
line = "Hello World"
Debug.Print line
line = "Hello" & Chr(10) & "World"
Debug.Print line
line = "Hello" & vbCrLf & "World"
Debug.Print line
line = "Hello" & vbTab & "World"
Debug.Print line
End Sub

Output:
Hello World
Hello World
Hello
World
Hello
World
Hello World


Regards
Claus B.
--
Windows10
Office 2016

Hi Claus

Thank you for showing me the more correct way to use the formatting functions. Its great that people on this forum take the trouble to help others.

Desmond