ByRef vs ByVal

[fumei]

Fo those who are someqwhat confused by this, I came across a good simple example of ByRef and ByVal on Chip Pearson's site.
Sub CallingProcedure()
Dim A As Long
Dim B As Long
A = 123
B = 456
Debug.Print "BEFORE CALL = A: " & CStr(A), "B: " & CStr(B)
CalledProcedure A, B
Debug.Print "AFTER CALL = A: " & CStr(A), "B: " & CStr(B)
End Sub

Sub CalledProcedure(ByRef X As Long, ByVal Y As Long)
X = 321
Y = 654
End Sub

To give it away, you get:

BEFORE CALL = A: 123 B: 456
AFTER CALL = A: 321 B: 456

CalledProcedure gets the parameters A(ByRef) and B (ByVal).

A at this point = 123, and B at this point = 456

THEN X is declared = 321, and Y is declared = 654.

Wait for it...this is the point.

At THIS point, because A is passed in ByRef, the original A is CHANGED, it = 321.

At this point, because B is passed in ByVal, the original B is NOT changed.

For the full article, see:


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

BTW: this also works for strings.
Sub CallingProcedure()
Dim A As String
Dim B As String
A = "yadda"
B = "blah"
Debug.Print "BEFORE CALL = A: " & A, "B: " & B
CalledProcedure A, B
Debug.Print "AFTER CALL = A: " & CStr(A), "B: " & CStr(B)
End Sub

Sub CalledProcedure(ByRef X As String, ByVal Y As String)
X = "was yadda"
Y = "was blah"
End Sub

Result?
BEFORE CALL = A: yadda B: blah
AFTER CALL = A: was yadda B: blah

Now, what happens if you do NOT declare ByRef/ByVal? The default is ByRef. THAT means anything you pass as an argument will have the original changed by anything you do to it in the called procedure.

And THAT means, if you pass an argument ByVal you can manipulate it - i.e. temporarily change it - and send the changed result someplace else, without changing the original.

For those who are familiar with this, please, this is a simple example.

[fumei]

I want to add, when you are dealing text, particularly paragraphs, do some testing first. You can get unexpected results because of forgotten paragraph marks.