Passing parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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
Click to expand...
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
Click to expand...
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?
 
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 said:
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
Click to expand...
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
Click to expand...
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
 
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.
 
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
 
Back
Top