CODEWITHSUNDEEP
vbaexcel
GeorgeSBF
GeorgeSBF

Reputation: 33

Passing a parameter to an event handler procedure

I am generating a scripting dictionary using one button on a userform, using it to populate a listbox, and then need to use that same dictionary using a second button on the form. I have declared my dictionary either using early binding as so:

Dim ISINDict As New Scripting.Dictionary

or late binding as so

Dim ISINDict as Object
...
Set ISINDict = CreateObject("Scripting.Dictionary")

When I try to pass the dictionary to the other button like so:

Private Sub OKButton_Click(ISINDict as Scripting.Dictionary) 'if early binding
Private Sub OKButton_Click(ISINDict as Object) 'if late binding

I get the following error: "Procedure declaration does not match description of event or procedure having the same name" on that line.

Any ideas what I'm doing wrong?

Upvotes: 2

Views: 2994

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

An event handler has a specific signature, owned by a specific interface: you can't change the signature, otherwise the member won't match the interface-defined signature and that won't compile - as you've observed.

Why is that?

Say you have a CommandButton class, which handles native Win32 messages and dispatches them - might look something like this:

Public Event Click()

Private Sub HandleNativeWin32Click()
    RaiseEvent Click
End Sub

Now somewhere else in the code, you want to use that class and handle its Click event:

Private WithEvents MyButton As CommandButton

Private Sub MyButton_Click()
    'button was clicked
End Sub

Notice the handler method is named [EventSource]_[EventName] - that's something hard-wired in VBA, and you can't change that. And if you try to make an interface with public members that have underscores in their names, you'll run into problems. That's why everything is PascalCase (without underscores) no matter where you look in the standard libraries.

So the compiler knows you're handling the MyButton.Click event, because there's a method named MyButton_Click. Then it looks at the parameters - if there's a mismatch, something is wrong: that parameter isn't on the interface, so how is the event provider going to supply that parameter?. So it throws a compile-time error, telling you you need to either make the signature match, or rename the procedure so that it doesn't look like it's handling MyButton.Click anymore.

When you drop a control onto a form, you're basically getting a Public WithEvents Button1 As CommandButton module-level variable, for free: that's how you can use Button1 in your code to refer to that specific button, and also how its Click handler procedure is named Button1_Click. Note that if you rename the button but not the handler, the procedure will no longer handle the button's Click event. You can use Rubberduck's refactor/rename tool on the form designer to correctly rename a control without breaking the code.

Variables in VBA can be in one of three scopes: global, module, or procedure level.

When you do:

Sub DoSomething()
    Dim foo
End Sub

You're declaring a local-scope variable.

Every module has a declarations section at the top, where you can declare module-scope variables (and other things).

Option Explicit
Private foo

Sub DoSomething()
End Sub

Here foo is a module-scope variable: every single procedure in that module can access it - read and write.

So if you have data you want to pass between procedures and you can't alter their signatures, your next best option is to declare a module-scope variable.

[ignores global scope on purpose]

About As New - consider this:

Public Sub Test()
    Dim foo As Collection
    Set foo = New Collection
    Set foo = Nothing
    foo.Add 42
    Debug.Print foo.Count
End Sub

This code blows up with run-time error 91 "object variable not set", because when foo.Add executes, foo's reference is Nothing, which means there's no valid object pointer to work with. Now consider this:

Public Sub Test()
    Dim foo As New Collection
    Set foo = Nothing
    foo.Add 42
    Debug.Print foo.Count
End Sub

This code outputs 1, because As New keeps the object alive in a weird, unintuitive and confusing way. Avoid As New where possible.

Upvotes: 1

gembird
gembird

Reputation: 14053

Declare the dictionary at the module level and fill it in button-1-click event handler. Then it can be simply re-used in button-2-click event handler. So there is no need to pass the dictionary to event handlers which is not possible either. HTH

Form module

Option Explicit

' Declare dictionary at the user form module level
Private ISINDict As Scripting.Dictionary

Private Sub CommandButton1_Click()
    FillDictionary
End Sub

Private Sub CommandButton2_Click()
    ' Use the dictionary filled in event handler of CommandButton-1
End Sub

Private Sub FillDictionary()
    With ISINDict
        .Add "Key-1", "Itm-1"
        .Add "Key-2", "Itm-2"
        .Add "Key-3", "Itm-3"
    End With
End Sub

Private Sub UserForm_Initialize()
    Set ISINDict = New Scripting.Dictionary
End Sub

Upvotes: 0

Related Questions