Forcing an Argument to be Passed by Value

Posted: May 20, 2013 by Transformer in Excel, VBA
Tags: , , , ,

While creating a subroutine we can define whether arguments will be passed ByVal  or ByRef however this behavior can be overridden as discussed below.

Let’s say we have a procedure Append which accepts an argument by reference.However at the time of calling the procedure, if argument is enclosed in parentheses then it is treated as if passed by Value.

Sub Append(ByRef strArgument As String)
    strArgument = Replace(strArgument , "by Value", "by Reference")
End Sub
'-----------------------------------------------------------------
Sub Caller()

    Dim strTemp     As String

    strTemp = "Argument passed by Value"
    Append (strTemp) ' copy of the variable is passed
    Debug.Print strTemp ' prints Argument passed by Value
    Call Append((strTemp)) 'copy of the variable is passed
    Debug.Print strTemp 'prints "Argument passed by Value"
    Append strTemp ' reference of the variable is passed
    Debug.Print strTemp 'prints "Argument passed by Reference"

End Sub 
Advertisements
Comments

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s