Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-29-2023, 07:07 AM
gmaxey gmaxey is offline Word and Excel ByVal and ByRef Observations Windows 10 Word and Excel ByVal and ByRef Observations Office 2019
Expert
Word and Excel ByVal and ByRef Observations
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,422
gmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the roughgmaxey is a jewel in the rough
Default 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
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.
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/

Last edited by gmaxey; 03-29-2023 at 09:09 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word and Excel ByVal and ByRef Observations 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
Word and Excel ByVal and ByRef Observations Merge From Excel With Table in Word - Next Record If Excel Column Same? misscrf Mail Merge 2 10-15-2014 11:51 PM
Word and Excel ByVal and ByRef Observations 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

Other Forums: Access Forums

All times are GMT -7. The time now is 02:17 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft