best practices for message handling between subroutines?

[LQuinn]

Hi all, I've spent a lot of time googling as well as searching here, hoping someone could point me in the right direction for best practices on message handling please?
More of a soft concept search rather than a specific search for which methods and properties are exposed by any particular item, so not getting much joy on search results

I develop VBA to process documents that have been handed around multiple different users, all with their own interpretation and styles, so I remediate, normalise and then standardise document content - all in modular subroutines

What I'm looking for is advice on how to handle passing status messages, so all the routines run and then upon completion I can present a single message that has content from each subroutine (if required)



Thanks in advance

[eduzs]

If you mean "passing status messages" as passing variables values, I imagine two possibilities, maybe:
Pass variables as arguments on a sub or a function.
Declare variables as Public (within project) or Private (within module).

[LQuinn]

Originally Posted by eduzs

If you mean "passing status messages" as passing variables values, I imagine two possibilities, maybe:
Pass variables as arguments on a sub or a function.
Declare variables as Public (within project) or Private (within module).

Thanks, I've tried playing around with that but have asked here because I think there must be a better way

[LQuinn]

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

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:

ReDim varDebug(0 To 0)

write to the array from various different routines:

saveMyDebugValue Now
saveMyDebugValue "Clean up document"

write the output from the array:

Write_MyDebugValue

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

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