How can I save VBA watches manually or add them via code?

Question

I have a fair amount of global variables and to keep track of them during debugging I use watches. However it is annoying to add all of them again and again at the beginning of each session. Is there a way to save and load them? Or if that's not possible to add them via code?

This question concerns the watches for expressions in the VBA Editor window (see screenshot).

Answer 1

In a class called WatchedVariables, you could have

Option Explicit

Private str_Variable1_Prev As String
Private str_Variable1 As String

Public Property Let strVariable1(value As String)
    str_Variable1_Prev = str_Variable1
    str_Variable1 = value
    If str_Variable1 <> str_Variable1_Prev Then
        Debug.Print "Variable 1 has changed"
    Else
    End If
End Property

Then you'd access your watched variables, like so clsWatchedVariables.strVariable1="nathan"

However, this may help Find specific text in VBA watch list

Answer 2

It is not very pretty, but you can use Application.SendKeys to add Watches. I have no idea if the language locale affects the shortcut keys. The below applies to English.

The limitation is you can only use things like Current Module or All Modules (i.e. scroll all the way up or all the way down), but not a specific other module for scope. You could probably fix this limitation by using the VBIDE to find out how many modules there are etc, and therefore how many times to scroll up for a particular module. I have not done this for the code below as this is a proof of concept - I leave the fun part to you :-)

Usage: call AddWatch sub with the specified arguments. You can add these to a sub you call when you start a new session, as demonstrated in my "HelloNewSession()"

The VBE must be in focus when the code is run. You can either do this manually or use the VBIDE object to set the focus.

Option Explicit

Enum enumWatchType
    WatchExpression
    BreakWhenTrue
    BreakWhenChange
End Enum
Enum enumProceduresType
    AllProcedures
    Caller
End Enum
Enum enumModuleType
    AllModules
    CurrentModule
    ThisWorkbook
End Enum

Public testVar As Boolean
Sub HelloNewSession()
    AddWatch "testVar = True", AllProcedures, CurrentModule, BreakWhenTrue
    testVar = True
End Sub

Sub AddWatch( _
    expression As String, _
    Optional proceduresType As enumProceduresType = enumProceduresType.Caller, _
    Optional moduleType As enumModuleType = enumModuleType.CurrentModule, _
    Optional watchType As enumWatchType = enumWatchType.WatchExpression)

    Dim i As Long

    Application.SendKeys "%DA"
    Application.SendKeys getEscapedSendkeysText(expression)
    If proceduresType = enumProceduresType.AllProcedures Then
        Application.SendKeys "%p"
        For i = 1 To 1000 'You could use VBIDE to count the valid types to actually scroll up the right number of times!
            Application.SendKeys "{UP}"
        Next
    End If
    If moduleType = enumModuleType.AllModules Then
        Application.SendKeys "%m"
        For i = 1 To 1000 'You could use VBIDE to count the valid types to actually scroll up the right number of times!
            Application.SendKeys "{UP}"
        Next
    ElseIf moduleType = enumModuleType.ThisWorkbook Then
        Application.SendKeys "%m"
        For i = 1 To 1000 'You could use VBIDE to count the valid types to actually scroll up the right number of times!
            Application.SendKeys "{DOWN}"
        Next
    End If
    Select Case watchType
        Case enumWatchType.WatchExpression
            Application.SendKeys "%w"
        Case enumWatchType.BreakWhenTrue
            Application.SendKeys "%t"
        Case enumWatchType.BreakWhenChange
            Application.SendKeys "%c"
    End Select

    Application.SendKeys "~"

End Sub
Function getEscapedSendkeysText(ByVal text As String) As String
    Dim char As String, i As Long
    Const chars As String = "~%+^()[]"
    For i = 1 To Len(chars)
        char = Mid$(chars, i, 1)
        text = Replace(text, char, "{" & char & "}")
    Next
    getEscapedSendkeysText = text
End Function