Parameter Pasing ?

[Eng Teng]

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

 

[Joel]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peter T]

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

 

[Peter T]

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

 

[Rick Rothstein \(MVP - VB\)]

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