Programmatically Determining the Event Handler for an OnClick Event in VBA

Question

I'm working on an application that traces execution paths in MS Access VBA. However, I've run into a problem. It seems that you can dynamically assign handlers to control OnClick and and other such events in VBA, but I don't see a way to read events from those controls. For example:

With Forms("Order Entry").Controls("OK") 
 If .OnClick = "" Then 
 .OnClick = "fnProcessOrder" 
 End If 
End With

This dynamically assigns the function "fnProcessOrder" to the "OK" button. However, if you try to get the value of that button, you get this:

With Forms("Order Entry").Controls("OK") 
  Debug.Print "My Handler -->" + .OnClick
EndWith

Results in:

--> [Event Procedure]

And no further detail. This does seem to be the behavior stated by Microsoft in here: https://msdn.microsoft.com/en-us/vba/access-vba/articles/commandbutton-onclick-property-access

But I'm wondering if there is anyway to go further than that.

I would have thought that that information would be in the MSysObjects and MSysQueries tables, but I don't see it there. I've tried extracting my test database using the Database Documenter, and I get that value from the generated documentation as well.

Is there a way to find the VBA function that was assigned to the control?

Well, I plan to do some basic processing, either with pyparsing, lex, or something hand-rolled. So, there will definitely be a symbol tree, I've begun building it already. Not being able to read the .OnClick property is holding everything else up though. I'm hoping that I don't have to write anything to parse the binary... — Albert Perrien
– Albert Perrien, Commented Sep 20, 2017 at 18:17
It is possible the value that is actually assigned is not readable or reversible (like a memory pointer to a function for example or an internal handle). Add a non visible text control next to your OK button and whenever you dynamically change OnClick, update that one as well. Or have a fixed OnClick function that then reads that text field and redirects accordingly. — SunKnight0
– SunKnight0, Commented Sep 20, 2017 at 18:22
I think one of our contributors has been working on this - I'll ping him. Pretty sure it does involve parsing the binary. Are you writing this in VBA? — Mathieu Guindon
– Mathieu Guindon, Commented Sep 20, 2017 at 18:55
I'm currently fiddling with it in VBA, trying to learn about the problem. I'm not welded to just VBA, though. If there's some other solution out there that works better, I'll happily look into it, as I'm looking at Rubberduck right now. I'm always open to learning something new. — Albert Perrien
– Albert Perrien, Commented Sep 20, 2017 at 19:41

this · Accepted Answer · 2017-09-20 19:03:40Z

1

Actually, the code does not look right to me. If you want to assign a VBA function to an event procedure, you have to use syntax similar to this: .OnClick = "=fnProcess()" Note the literal = and the () -- those are mandatory for making Access recognize you want to call a function.

As additional consideration, you can have a class module (let's call it Observer) that has code similar to this:

Private WithEvents ctlInterest As Access.Textbox

Public Sub Init(TargetControl As Access.Control)
  Set ctlInterest = TargetControl
  With ctlInterst
    If .OnClick = "" Then
      .OnClick = "[Event Procedure]"
    End If
  End With
End Sub

Private ctlInterest_OnClick()
  fnProcess()
End Sub

and you can then deploy the class in a form like so....

Private objObserver As Observer

Private Sub Form_Load()
  Set objObserver = New Observer
  objObserver.Init(Me.txtInterest)
End Sub

which will yield the same result, and you end up with multiple event handlers.

For more information this may be of help.

In conjunction with the fact that function expressions are possible and the fact that it can be dynamically assigned at any time, it pretty much require code-path analysis to identify possible entry points for all events handlers in a general. Which means you need Rubberduck's parser for that. Note that static code path analysis is planned but not implemented yet, AIUI.

Also, I want to be sure -- if your intention is to build a call stack trace for error handling, you might need to look at third party add-in. For example, vbWatchDog, a commerical product, can provide that information in VBA.

answered Sep 20, 2017 at 19:03

this

1,42612 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Albert Perrien Over a year ago

I understand that with dynamic assignment, the current value of OnClick can change, but is there a way to see what the current value is while the application is running? That way I can log it to a file at least. I think you might be right, though. A good stack trace utility might be the way to go. Does vbWatchDog support logging the stack without tripping an error?

this Over a year ago

Just to make it clear - it's not just dynamic -- it can be handled by multiple VBA procedure, with the syntax I showed above. This won't apply if we use the =fnProcess() syntax, of course but I wanted to be sure that was known. RE: vbWatchDog, you can use LiveCallStack that doesn't require tripping an error but the thing is you'd still have to write code to read the call stack somewhere... perhaps within the fnProcess function for instance.

Erik A · Accepted Answer · 2017-09-20 20:31:37Z

1

This will return the full code for the event. If you just use one sub inside that event, it should work.

Public Sub TestModulePrinter()
    Dim subName As String
    With Forms("Order Entry").Controls("OK")
      subName = "Private Sub " & .Name & "_OnClick()"
    End With
    Dim i As Long
    Dim IsPrinting As Boolean
    With Forms("Order Entry").Module
        For i = 1 To .CountOfLines
            If Not IsPrinting Then
                If Trim(.Lines(i, 1)) = subName Then
                    IsPrinting = True
                End If
            Else
                If Trim(.Lines(i, 1)) = "End Sub" Then
                    IsPrinting = False
                Else
                    Debug.Print .Lines(i, 1)
                End If
            End If
        Next i
    End With
End Sub

edited Sep 20, 2017 at 20:31

answered Sep 20, 2017 at 19:48

Erik A

32.8k13 gold badges49 silver badges75 bronze badges

4 Comments

Albert Perrien Over a year ago

I'm trying to find the current OnClick function of a control, without altering that particular control. I'm assuming that the value for OnClick is set elsewhere in the program.

this Over a year ago

@AlbertPerrien wait, is this a one-off thing? If that is the case, you might be fine with using a watch expression set to Break when value changes on the OnClick property of the control...

Albert Perrien Over a year ago

That's a really decent suggestion! If I set that property with an error handler to catch it, I may be able interrupt and log all the activity in the app. That won't handle things that are set by the VB editor at compile time, but it's a great start.

Erik A Over a year ago

@Albert Have you tried running my code? It returns the full code for the OnClick event in the form module, and doesn't alter anything

Collectives™ on Stack Overflow

Programmatically Determining the Event Handler for an OnClick Event in VBA

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related