How do you pass parameters from a calling sub to a function either by 1) reference or by 2)value
By reference I mean: when the value is changed in the called function it is also changed in the calling sub (once program flow resumes in the sub
By value I mean: when the value is changed in the called function it is not changed in the calling sub (the function returns it to the calling function.) This brings me to another question: I've tried using the Return statement in VBA and it does not recognize it. Is there another statement to return a value

Thanks
Warren R

This example shows the difference between ByVal and ByRef.
Change the ByRef below to ByVal to see the difference.

Note that if you don't specify ByVal or ByRef, the default is ByRef

Sub test()
Dim strParameter As String, strResult As String

strParameter = "My Parameter Text"
strResult = DoStuff(strParameter)

MsgBox "strResult = " & strResult
MsgBox "strParameter = " & strParameter
End Sub

Function DoStuff(ByRef strPassedParameter As String) As String
strPassedParameter = "Altered Text"
DoStuff = "Returning Stuff"
End Function


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"WarrenR" wrote in message
...
How do you pass parameters from a calling sub to a function either by 1)
reference or by 2)value.
By reference I mean: when the value is changed in the called function it
is also changed in the calling sub (once program flow resumes in the sub.
By value I mean: when the value is changed in the called function it is
not changed in the calling sub (the function returns it to the calling
function.) This brings me to another question: I've tried using the Return
statement in VBA and it does not recognize it. Is there another statement
to return a value?

Thanks,
Warren R.

Just thought of another related question... For the called routine(in the example below, it's a function), is there a general rule as to when this should be set up as a sub rather than a function? I'm still grasping the differnce between the two
Thanks
Warren R

----- Rob van Gelder wrote: ----

This example shows the difference between ByVal and ByRef
Change the ByRef below to ByVal to see the difference

Note that if you don't specify ByVal or ByRef, the default is ByRe

Sub test(
Dim strParameter As String, strResult As Strin

strParameter = "My Parameter Text
strResult = DoStuff(strParameter

MsgBox "strResult = " & strResul
MsgBox "strParameter = " & strParamete
End Su

Function DoStuff(ByRef strPassedParameter As String) As Strin
strPassedParameter = "Altered Text
DoStuff = "Returning Stuff
End Functio


--
Rob van Gelder - http://www.vangelder.co.nz/exce


"WarrenR" wrote in messag
..
How do you pass parameters from a calling sub to a function either by 1
reference or by 2)value
By reference I mean: when the value is changed in the called function i
is also changed in the calling sub (once program flow resumes in the sub
By value I mean: when the value is changed in the called function it i
not changed in the calling sub (the function returns it to the callin
function.) This brings me to another question: I've tried using the Retur
statement in VBA and it does not recognize it. Is there another statemen
to return a value
Thanks
Warren R

For calling from code, the only difference is whether the procedure
returns a result or not: Subs don't; Functions, do.

Only Functions can be called from a worksheet.

In article ,
"WarrenR" wrote:

Just thought of another related question... For the called routine(in the
example below, it's a function), is there a general rule as to when this
should be set up as a sub rather than a function? I'm still grasping the
differnce between the two.

Perfect! Thank you JE
-Warren

----- JE McGimpsey wrote: ----

For calling from code, the only difference is whether the procedure
returns a result or not: Subs don't; Functions, do

Only Functions can be called from a worksheet

In article
"WarrenR" wrote

Just thought of another related question... For the called routine(in the
example below, it's a function), is there a general rule as to when this
should be set up as a sub rather than a function? I'm still grasping the
differnce between the two