I received some website feedback the other night concerning my Word Tips page on the fickleness of Word's Find method. After some discussion, the contributor has discovered some fundamental differences in how Excel and Word seem to handle Range arguments passed ByRef or ByVal.
My understanding of these two process is far from perfect, but I was under the impression that arguments passed ByVal were disconnected from the original value set in the calling procedure. Changes to arguments passed ByRef in the called procedure would be reflected in the calling procedure.
This is born out in the following Excel demonstration:
Code:
Sub Excel_Test()
Dim oRngPrimary As Range
Set oRngPrimary = Range("A1:B2")
Debug.Print "Before call, oRngPrimary Address = " & oRngPrimary.Address
'Pass range object to procedure ByVal
CalledProcedureByVal oRngPrimary
Debug.Print "After call, oRngPrimary Address = " & oRngPrimary.Address 'Note the range passed ByVal is preserved (same address)
'Pass same range object to procedure ByRef
CalledProcedureByRef oRngPrimary
Debug.Print "After call, oRngPrimary Address = " & oRngPrimary.Address 'Note the range passed ByRef has been altered by calling procedure (new address)
lbl_Exit:
Exit Sub
End Sub
Sub CalledProcedureByVal(ByVal oRng As Range)
'Alter address of passed range
Set oRng = oRng.Offset(2, 2)
Debug.Print "New oRng Address = " & oRng.Address
lbl_Exit:
Exit Sub
End Sub
Sub CalledProcedureByRef(ByRef oRng As Range)
'Alter address of passed range
Set oRng = oRng.Offset(2, 2)
Debug.Print "New oRng Address = " & oRng.Address
lbl_Exit:
Exit Sub
End Sub
It seems this does not hold true with Word. In testing with Word, the value passed is altered regardless of using ByVal or ByRef. The only way to preserve the original range value is to pass its "Duplicate" property to the called procedure.
Code:
Sub Word_Test()
Dim oRngPrimary As Range
Set oRngPrimary = ThisDocument.Range(1, 3)
Debug.Print "Before call, oRngPrimary Start = " & oRngPrimary.Start & " End = " & oRngPrimary.End
'Pass range object to procedure ByVal
CalledProcedureByVal oRngPrimary
Debug.Print "After call, oRngPrimary Start = " & oRngPrimary.Start & " End = " & oRngPrimary.End & "!!!" 'Note, unlike with Excel, the range passed ByVal is NOT preserved (different start and end values).
'Reset range location
Set oRngPrimary = ThisDocument.Range(1, 3)
'Pass same range object to procedure ByRef
CalledProcedureByRef oRngPrimary
Debug.Print "After call, oRngPrimary Start = " & oRngPrimary.Start & " End = " & oRngPrimary.End 'Note, like with Excel, the range passed ByRef has been altered by actions in the calling procedure (new start and end values)
'Reset range location
Set oRngPrimary = ThisDocument.Range(1, 3)
'Pass "Duplicate" of range object to procedure ByRef
CalledProcedureByRef oRngPrimary.Duplicate
Debug.Print "After call, oRngPrimary Start = " & oRngPrimary.Start & " End = " & oRngPrimary.End 'Note the location of the primary range object is preserved (same start and end values as before call).
lbl_Exit:
Exit Sub
End Sub
Sub CalledProcedureByVal(ByVal oRng As Range)
'Alter location of passed range
oRng.Move wdCharacter, 3
Debug.Print "oRng Start = " & oRng.Start & " End = " & oRng.End
lbl_Exit:
Exit Sub
End Sub
Sub CalledProcedureByRef(ByRef oRng As Range)
'Alter location of passed range
oRng.Move wdCharacter, 3
Debug.Print "oRng Start = " & oRng.Start & " End = " & oRng.End
lbl_Exit:
Exit Sub
End Sub
Don't really have a question. Just making an observation and of course invite comment and discussion.