1

In VBA for Excel, how do I assign an existing function or sub, say myFun(), in a module to a newly added ActiveX click button directly?

I understand I can do the following on the worksheet code page where the ActiveX command (click) button is located.

Private Sub myFun_Click()
myFun()
End Sub

By "directly" I mean naming the button "myFun" and point the button directly to the function myFun() with having to placing myFun() in another sub as shown above.

Hans
  • 1,269
  • 3
  • 19
  • 38
  • 2
    Not-so-nitpicky nitpick: a `Sub` is a *procedure*, not a *function*. A `Function` has a return value, a `Sub` doesn't. A `Function` in a standard code module (.bas) can be exposed to be used in worksheet formulas as a *UDF*, a `Sub` in a standard code module (.bas) can be exposed to be used as a *macro*. – Mathieu Guindon Jan 29 '16 at 22:26
  • @Mat'sMug: +1 The last two sentences are especially pertinent. See my comment below your answer. Thanks! – Hans Jan 29 '16 at 23:18

2 Answers2

1

VBA events bound to control are "local" to the worksheet by defintion, therefore if you put button "A" on a worksheet "w1" the resulting click event handler function will be named: 

Private Sub A_Click()

But if you put button "A" on worksheet "w2" the click event handler signature will be the same but actually trigger the click event of the button placed on "w2".

If you want to implement the same behavior when the user clicks on button "A" from different worksheets, then you need to do this by adding a Sub/Function on a VBA Module, which is shared among all the worksheets of the workbook object.

Private Sub A_Click() {
     CommonFunction()
}
Marco Z.
  • 61
  • 7
  • Thanks for your answer. However, your answer does not answer my question. The second code in your answer is exactly the example I give in my question which I explicitly says I do not want. I would like to call myFun() directly without using another layer of function nesting it. – Hans Jan 30 '16 at 00:08
1

There are 2 types of controls you can place on a worksheet:

  • ActiveX controls

    You handle their Click event in the code-behind, by double-clicking the control and generating a handler for it.

  • Form controls

    You assign a macro to these controls, exactly like you would assign a macro to any other shape. The macro can be an existing one (any parameterless Public Sub procedure in a non-private standard module).

If you want to assign a button to MyFun, assuming the signature looks like this:

Public Sub MyFun()

Then you can try using a form control instead of an ActiveX control.

Note: MyFun() has to be a Sub to be exposed as a Macro. A 'Sub' is a procedure, not a function. A 'Function' has a return value, a 'Sub' doesn't. A 'Function in a standard code module (.bas) can be exposed to be used in worksheet formulas as a UDF, a 'Sub' in a standard code module (.bas) can be exposed to be used as a macro.

Hans
  • 1,269
  • 3
  • 19
  • 38
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • You are saying I can not assign an existing sub myFun() to an ActiveX control button, right? I am trying to assign the sub to a Form control button, but it seems I am not able to do so. I can only "assign Macro" to the button, but I am not able to find the sub name in the table popped up for selection. What should I do? – Hans Jan 29 '16 at 22:04
  • If you can't find the sub name in the macros list, either the procedure isn't `Public`, has parameters, or it's not written in a standard code module (.bas), or the module has `Option Private Module` set. – Mathieu Guindon Jan 29 '16 at 22:06
  • Thank you. I see the name myFun now in the Macro list once I change "function" to "sub", thanks to your comment below my question. – Hans Jan 29 '16 at 23:06