Microsoft Office Forums - View Single Post - [Solved] best practices for message handling between subroutines?

LQuinn · 02-10-2021, 07:52 PM

ok, so it's taken a while because i'm no expert, but have managed to answer my own question

Code:

Public varDebug As Variant
Public Function saveMyDebugValue(DebugValue As Variant) As Long
'
' https://social.msdn.microsoft.com/Forums/office/en-US/5c2a83cf-0596-41a2-9f42-e64cc6b1e556/immediate-window-memory-limitation?forum=accessdev
'
    Dim I As Long
    If IsEmpty(varDebug) Then
        ReDim varDebug(0 To 0)
    Else
        ReDim Preserve varDebug(LBound(varDebug) To UBound(varDebug) + 1)
    End If
    I = UBound(varDebug)
    varDebug(I) = DebugValue
    saveMyDebugValue = I
End Function

clear the array before using it:

Code:

ReDim varDebug(0 To 0)

write to the array from various different routines:

Code:

saveMyDebugValue Now
saveMyDebugValue "Clean up document"

write the output from the array:

Code:

Write_MyDebugValue

my function to get the output from the array when complete:

Code:

Private Function Write_MyDebugValue()
'
' write log output to new document
'
    Dim varCounter As Variant
    Dim strOutPut As String
    Dim objDocTarget As Document
    
   ' For Each varCounter In varDebug
    For varCounter = 0 To UBound(varDebug)
        strOutPut = strOutPut & varDebug(varCounter) & vbCrLf
    Next varCounter
    'MsgBox strOutPut
    
    Set objDocTarget = Documents.Add
    
    objDocTarget.Range = ""

    With objDocTarget.Range
        .Font.Size = 9
        .Font.Name = "Arial"
        ' RGB value for 80% grey
        .Font.TextColor = RGB(80, 84, 77)
        '.InsertParagraphAfter
        '.InsertAfter "====================================================================================="
        .Text = strOutPut
        '.InsertAfter strOutPut
    End With
    
    Set varCounter = Nothing
    Set objDocTarget = Nothing
    
End Function