12

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). Screenshot

Community
  • 1
  • 1
Christian
  • 534
  • 4
  • 18

2 Answers2

8

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

Community
  • 1
  • 1
Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
  • 1
    The trouble with this is that when would executing code query if there has been a change? You would need to chuck in calls to this class all over the place (admittedly you could use compiler conditionals to only have the performance hit during development) – Cor_Blimey Nov 12 '15 at 17:50
  • 1
    you'd set the class to be public, declared on opening, then instead of myVar=1 it would be clsWatchedVars.myVar=1, then in the immediate window, you'd see the change. – Nathan_Sav Nov 12 '15 at 18:11
7

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
Cor_Blimey
  • 3,260
  • 1
  • 14
  • 20
  • What is the $ sign in the expression Mid$(chars, i, 1) for? VBA help displays Mid without it. Thanks for the long answer, I'm trying to implement it. Will mark as accepted if it all works out :) – Christian Nov 18 '15 at 13:40
  • @ChrisUnbroken pretty much the same thing. Returns a String rather than a Variant. Due to implicit type conversion in VBA, the difference is negligible. Indeed - I didn't do Replace$(..). Not sure why I used one or the other ;) – Cor_Blimey Nov 18 '15 at 13:42
  • so one can always use the $ sign to get a variable with the explicit type in the variant wrapper? Do you maybe have a link that explains this more in Detail? – Christian Nov 18 '15 at 13:55
  • Works perfectly, much appreciated! – Christian Nov 18 '15 at 14:04
  • @ChrisUnbroken thanks - glad it helps. There was a bug in the `getEscapedSendkeysText` function. I have updated it in the above. No changes elsewhere. – Cor_Blimey Nov 19 '15 at 10:49
  • @ChrisUnbroken r.e. the $: https://msdn.microsoft.com/en-us/library/office/gg278535.aspx explains it. As I said, it makes a negligible difference. And I now know why I didn't use Replace$: there isn't one! :) – Cor_Blimey Nov 19 '15 at 10:54