Tony said:
When you pass it ByRef (by reference) you are passing the actual variable in your calling routine
Are you really? Are you not passing a
pointer to it? Is that not what is meant ByRef? Does not the result of the processing pass the new value back to the variable? Not only that, but it passes the new value immediately?
I thought that ByRef meant: THERE...do the following actions to THAT thing. Pointing to THAT, which changes its value according to the instructions.
As opposed to ByVal, which means: do the following actions to THIS value, without changing the thing the original (ByVal) value came from.
For others reading this, it may be important to note that VBA
assumes ByRef by default.
Code:
Sub MySub([b]ByRef[/b] MyString As String)
MyString = "Yippeee!"
End Sub
Sub Whatever()
Dim MyString As String
MyString = "Yadda"
MySub MyString
MsgBox MyString
End Sub
Messagebox result: "Yipeee!" The instructions were done and the value pointed back.
Code:
Sub MySub([b]ByVal[/b] MyString As String)
MyString = "Yippeee!"
End Sub
Sub Whatever()
Dim MyString As String
MyString = "Yadda"
MySub MyString
MsgBox MyString
End Sub
Messagebox result: "Yadda" The instructions were done - as you can see stepping through MySub (the string DOES change to "Yipeee!" - but the result were NOT pointed back.
Going back to the mention of VBA passing back a ByRef value immediately, if you step through the first example, if you check the value of MyString
in the Sub Whatever AFTER:
MyString = "Yippeee!"
in the Sub MySub, but BEFORE the End Sub, MyString is already changed.
Code:
Sub MySub(MyString As String)
[b][COLOR=red]' NOT defined as ByRef or ByVal[/color red][/b]
MyString = "Yippeee!"
End Sub
Sub Whatever()
Dim MyString As String
MyString = "Yadda"
MySub MyString
MsgBox MyString
End Sub
Messagebox result: "Yipeee!" The instructions were done and the value pointed back. Again, to those not fully familiar with this stuff, VBA
assumes it is ByRef, unless otherwise explicitly declared as ByVal.
Now...here is where I show MY ignorance.
Tony said:
A value passed by reference, however, can only affect an actual variable in your main routine if it is a variable. If it is an expression it is transient by definition and already in VBA-owned memory and then, however the subroutine defines it, it will be discarded at the end of the statement.
How do you pass an expression (but not as a variable
representing an expression) to a procedure?
Further, in one of your posts, you say: "(MyLong) is not your variable; it is an expressiion derived from your variable."
This in reference to: MySub (MyLong)
You also state: "The expression, stored somewhere by VBA, is indeed changed but you never get to see the result of it. "
However, stepping through, (MyLong) does NOT change. In the given example, it seems to me that it IS the variable. I think you meant MyVal in the othe rprocedure.
As for never get to see the result, this is incorrect. Stepping through, you can indeed see the result of the change, even though yes, it is destroyed by the End Sub.
It is that "invisible" storage that strongm mentioned. It is this could possibly be used for the "leverage" you mention. You could send the "temporary" value out to
another procedure before the termination of the procedure that is holding the "invisible" variable. Say...
Code:
Sub MyTonySub(ByVal MyVal As Long)
MyVal = MyVal ^ 2
UsingTempValue MyVal
End Sub
Sub MyTonyLong()
Dim MyLong As Long
MyLong = 10
MyTonySub (MyLong)
MsgBox MyLong
End Sub
Sub UsingTempValue(myInput As Long)
MsgBox "Here is the invisible value of MyVal. " & _
myInput
End Sub
So yes, MyLong remains at 10 (send as ByVal), but you can redirect the value of MyLong ^ 2 (100) someplace else, before that value is gone. I do not know if that could be real leverage, but you certainly can use the value in the new storage that strongm mentions.
Gerry
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my old subtlety...![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
