ByRef ByVal

[austenr]

Can one of you tell me how these are used. Im looking for what ByVal actually does as well as ByRef. I've read on the net about them but hoping one of you would be able to give a better explaination (at least one I can understand). Thanks.

[Bob Phillips]

By reference (ByRef) and by value (ByVal) are ways of passing values to a procedure.

When you pass by reference, you are passing a reference to the value, or its memory location. This means that the called procedure can modify that variable and the change will be seen within the calling procedure



Public Sub TestByRef()
Dim myVar As Long

myVar = 20
MsgBox "myVar before call (in calling proc): " & myVar
Call ByRefProc(myVar)
MsgBox "myVar after call (in calling proc): " & myVar
End Sub

Public Sub ByRefProc(ByRef AVariable As Long)
MsgBox "myVar at start (in called proc): " & AVariable
AVariable = Int(Rnd() * 100)
MsgBox "myVar after change (in called proc): " & AVariable
End Sub


When a variable is passed by value, a copy of that variable is taken and used b y the called procedure, and cleared at the end. Thus, any changes to it in the called procedure will be lost on exit, so the calling procedure will see the same value that it passed to that called procedure.



Public Sub TestByRef()
Dim myVar As Long

myVar = 20
MsgBox "myVar before call (in calling proc): " & myVar
Call ByRefProc(myVar)
MsgBox "myVar after call (in calling proc): " & myVar
End Sub

Public Sub ByRefProc(ByVal AVariable As Long)
MsgBox "myVar at start (in called proc): " & AVariable
AVariable = Int(Rnd() * 100)
MsgBox "myVar after change (in called proc): " & AVariable
End Sub

[Paul_Hossler]

I believe that Objects, Strings, and Arrays are allways passed ByRef

Chip Pearson (my other source for all things VBA when XLD's on vacation) also has a nice write up and some further examples

http://www.cpearson.com/Excel/byrefbyval.aspx

Paul

[austenr]

Thanks guys