Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Pasing ?
How do I get x value from Function Test() in CommandButton1_Click() function
? Function Test(ByVal x As Integer) x = 12 End Function Private Sub CommandButton1_Click() para_ret = Test(x) MsgBox "x is " & para_ret End Sub Regards, Tee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Pasing ?
You can't have a MAIN subroutine with paramters. A main subroutine is the
first subroutine called from a workbook. You have to use ActiveCell to get the current cell data in you are using Command button on a worksheet. If you ae using a userform then read one of the Oleojects (textbox, listbox, combobox) to get you parameter. "Eng Teng" wrote: How do I get x value from Function Test() in CommandButton1_Click() function ? Function Test(ByVal x As Integer) x = 12 End Function Private Sub CommandButton1_Click() para_ret = Test(x) MsgBox "x is " & para_ret End Sub Regards, Tee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Pasing ?
In VB/VBA, functions pass their values back through the function's name. Try
your code this way... Private Sub CommandButton1_Click() para_ret = Test() MsgBox "x is " & para_ret End Sub Function Test() Test = 12 End Function Notice that this simple Test function did not need any arguments; however, you could have provided them if there were calculation within the function that relied on non-global values outside of it. Rick "Eng Teng" wrote in message ... How do I get x value from Function Test() in CommandButton1_Click() function ? Function Test(ByVal x As Integer) x = 12 End Function Private Sub CommandButton1_Click() para_ret = Test(x) MsgBox "x is " & para_ret End Sub Regards, Tee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Pasing ?
Rick has answered your question but another possible way would be to change
the ByVal to ByRef, but ONLY if it's OK to have the variable return to the calling function changed, eg Function Test(ByRef x As Long) x = 12 End Function Private Sub CommandButton1_Click() Dim x As Long Call Test(x) ' or simply Test x MsgBox "x is " & x End Sub Note the Dim x As Long to match the declaration of the argument. Regards, Peter T "Eng Teng" wrote in message ... How do I get x value from Function Test() in CommandButton1_Click() function ? Function Test(ByVal x As Integer) x = 12 End Function Private Sub CommandButton1_Click() para_ret = Test(x) MsgBox "x is " & para_ret End Sub Regards, Tee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Pasing ?
Rick has answered your question but another possible way would be to change
the ByVal to ByRef, but ONLY if it's OK to have the variable return to the calling function changed, eg Function Test(ByRef x As Long) x = 12 End Function Private Sub CommandButton1_Click() Dim x As Long Call Test(x) ' or simply Test x MsgBox "x is " & x End Sub Note the Dim x As Long to match the declaration of the argument. Regards, Peter T "Eng Teng" wrote in message ... How do I get x value from Function Test() in CommandButton1_Click() function ? Function Test(ByVal x As Integer) x = 12 End Function Private Sub CommandButton1_Click() para_ret = Test(x) MsgBox "x is " & para_ret End Sub Regards, Tee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Parameter Pasing ?
I forgot to mention... one of the advantages of using functions is that you
can use them directly within an expression. Using your example, there is no need to assign the result passed back from your function to the para_ret variable before you combine it with your text in the MsgBox statement... Function Test(ByVal x As Integer) x = 12 End Function Private Sub CommandButton1_Click() MsgBox "x is " & Test(x) End Sub Now, with that said, IF you need to use the (same) returned variable several times within the same calling procedure (your CommandButton Click event for example), then it would be more efficient to assign the function's return value to a variable (as you did in your posted code) and use that variable over and over again instead of making repeated calls to the same function in order to get the same value back each time. Rick "Rick Rothstein (MVP - VB)" wrote in message ... In VB/VBA, functions pass their values back through the function's name. Try your code this way... Private Sub CommandButton1_Click() para_ret = Test() MsgBox "x is " & para_ret End Sub Function Test() Test = 12 End Function Notice that this simple Test function did not need any arguments; however, you could have provided them if there were calculation within the function that relied on non-global values outside of it. Rick "Eng Teng" wrote in message ... How do I get x value from Function Test() in CommandButton1_Click() function ? Function Test(ByVal x As Integer) x = 12 End Function Private Sub CommandButton1_Click() para_ret = Test(x) MsgBox "x is " & para_ret End Sub Regards, Tee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
parameter | Excel Programming | |||
How to choose if I use a parameter or not in a parameter query | Excel Discussion (Misc queries) | |||
What does this parameter mean? | Excel Programming | |||
Working with Arrays, pasing from function to function | Excel Programming | |||
optional parameter | Excel Programming |