#1
|
|||
|
|||
Word and Excel ByVal and ByRef Observations
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 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 Last edited by gmaxey; 03-29-2023 at 09:09 AM. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel gives Normal.dotm: File format is not valid message (yes, Excel, not Word) | Windspeed | Excel | 10 | 08-03-2022 04:29 PM |
Word & Excel 2010 - Best Options To Auto Insert MySQL & Excel Data? | Hoser | Word | 1 | 03-17-2017 03:47 PM |
Embeding Excel Docs in Word - Receiving Memory Error Message if Excel is open | kdash | Word | 0 | 05-06-2015 09:38 AM |
Merge From Excel With Table in Word - Next Record If Excel Column Same? | misscrf | Mail Merge | 2 | 10-15-2014 11:51 PM |
Open Word w Excel & fill Word textboxes w info from Excel fields runtime error 4248 | Joe Patrick | Word VBA | 2 | 01-30-2012 07:23 AM |