How to assign a macro to an activeX box (Used Function instead of Sub)

[Amit Punia]

I inserted an activeX Box and went inside that to see the coding window(Double click on the box). It showed me this coding -

Private Sub CommandButton1_Click()

End Sub

I changed the coding and wrote Function instead of Private Sub -

Function CommandButton1_Click(L As Double, W As Double) As Double

L = InputBox("Enter Length", "Enter A Value")
W = InputBox("Enter Width", "Enter A Value")
Area = L * W
ActiveSheet.Range("F8").Value = Area

End Function

Now when I'm trying to click on the ActiveX Button, it's not showing any output. How to assign(is the wrong term and only used in Sub Procedure) a macros to this button?

 

[Hui]

what is wrong with

Private Sub CommandButton1_Click()
L = InputBox("Enter Length", "Enter A Value")
W = InputBox("Enter Width", "Enter A Value")
ActiveSheet.Range("F8").Value = L * W
End Sub

You will need to save the file as an Excel Macro Enabled *.xlsm file type

See attached file:

 

[Amit Punia]

Thanks Hui for the help. However I wanted to know whether this is possible by mentioning "Function" in the start of the code instead of writing "Private Sub"?
As you can see in the above codes which I shared, by double clicking on the box it's showing Private Sub and then I changed the coding from Private Sub to Function. Just wanted to know whether it's possible or not.

 

[Hui]

That's not really how to use functions

This is more how to use a Function

Private Sub CommandButton1_Click()
  L = InputBox("Enter Length", "Enter A Value")
  W = InputBox("Enter Width", "Enter A Value")

  ActiveSheet.Range("A8").Value = Area(W, L) 'Get area
  ActiveSheet.Range("A9").Value = Perimiter(W, L) 'Get Perimiter

End Sub

Function Area(ByVal width As Double, ByVal length As Double) As Double
  Area = length * width
End Function

Function Perimiter(ByVal width As Double, ByVal length As Double) As Double
  Perimiter = 2 * (length + width)
End Function

 

[Chihiro]

In short. You can't. Only Subs can be executed directly. Functions need to be executed in some other code (or used as function in a cell). Functions should really be used, when you want value/values returned when it's called.

Have a read of the link.
http://analystcave.com/vba-function-vs-vba-sub-procedures/

 

[SirJB7]

Hi, Amit Punia!
If you right click on any ActiveX control, and select View Code, you'll always get a code like this:

Private Sub <control_name>_<event>(<parameters list>)

End Sub

That's what's called code for the main event (usually Click) or that control.
From VBA editor you can add new procedures to respond to many other events, and each of them has a fixed name structure and parameters... which you can't alter in any way. And they all are Sub, you'll never find a Function, since basically Sub do things (actions in response to events) and Function return values.
Regards!

 

[Amit Punia]

Thanks a lot Mr. @Hui , @Chihiro & @SirJB7 . I didn't know that. Thanks for enlightening me

 

[Debaser]

Although you can't assign a function to an ActiveX control directly, it's not strictly true to say that you can't execute a function directly. You can assign one to a shape or Form control, or call one from the macro dialog by typing its name in. I'm not suggesting it makes a lot of sense to do so, but it can be done.