Jim,
While I agree with your comments here, ByVal is slightly misleading with
objects. Please correct me if I'm wrong, but the way I understand it ;
Objects are always passed ByRef. If you declare an object ByVal, it is
passed ByRef regardless. (You possibly get a pointer to a pointer to an
object, but that is not the immediate concern)
If Target really was ByVal (in the sense of a copy), its .Value would not
change. But the concept of having a copy of, say, cell(2,3) is illogical,
anyway.
Taking the declaration ;
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Value = "Changed"
End Sub
In a related example of how you do not get a copy of an object (without some
specialised "clone" method), but a pointer (reference) to the original
object, compared with standard data types :
Private Sub Obj_Vars()
Dim Str1 As String
Dim Str2 As String
Dim Obj1 As Range
Dim Obj2 As Range
Str1 = "Initial Value"
Str2 = Str1
Str2 = "New Value"
Debug.Print Str1
Set Obj1 = Range("A1")
Obj1.Value = "Initial Value"
Set Obj2 = Obj1
Obj2.Value = "New Value"
Debug.Print Obj1.Value
End Sub
Although not strictly applicable to the above, for the entry "ByVal
References in Microsoft Forms", VBA Help states :
".... Because you can change the values of the object's members, events
produce results consistent with ByRef behavior, even though the event syntax
says the parameter is ByVal."
With all this, what is the reason for these events with object arguments
being declared as ByVal ?
NickHK
"Jim Thomlinson" wrote in message
...
When you pass arguments into a sub there are two ways to do so.
The first is ByRef (by reference) which passes the actual variable into
the
sub. Anything you do to that variable is a permanent change to the value
passed in. That is to say that now the variable in the calling procedure
has
been changed.
The other is ByVal (by value) which passes a copy of the variable into the
sub. Any changes made to the variable are temporary and the variable in
the
calling procedure will not see the changes.
Try running this code to see what I mean...
Sub MyMain()
dim R as Long
dim V as Long
R = 100
V = 200
msgBox "R is " & R " & vbcrlf & "V is " & V
Call ChangeStuff(R, V)
msgBox "R is " & R " & vbcrlf & "V is " & V
End Sub
Sub ChangeStuff( byref R as long, byval V as long)
R = R + 50
V = V + 50
msgBox "R is " & R " & vbcrlf & "V is " & V
end subSub MyMain()
Dim R As Long
Dim V As Long
R = 100
V = 200
MsgBox "R is " & R & vbCrLf & "V is " & V
Call ChangeStuff(R, V)
MsgBox "R is " & R & vbCrLf & "V is " & V
End Sub
Sub ChangeStuff(ByRef R As Long, ByVal V As Long)
R = R + 50
V = V + 50
MsgBox "R is " & R & vbCrLf & "V is " & V
End Sub
If you do not specify then varaibles are passed by reference. IMO it is
good
practice to always specify ByVal or ByRef and to pass by reference only
where
necessary. The reason is that if your variable gets messed up somewhere
along
the way if it was passed by val then you can narrow down who had the last
chance to premanently modify the value. That makes debugging easier.
--
HTH...
Jim Thomlinson
"DaveyJones" wrote:
Excellent, cheers. I don't suppose you could explain the whole (ByVal
Target
As Range, Cancel As Boolean) thing could you? I understand defining the
variable as boolean/range/integer etc, but the byVal and having to place
them
in the brackets in a sub I don't understand.
--
Dave
"Gary''s Student" wrote:
How about:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
Boolean)
If Target.Column = 1 Then
Cancel = True
Call dble(Target.Value)
End If
End Sub
Where dble is in a standard module.
--
Gary''s Student
"DaveyJones" wrote:
Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim a As String, b, c
Cancel = True 'Get out of edit mode
a = ActiveCell
b = ActiveCell.Address
c = Range(b).Column
If b < "1" Then Exit Sub
Call dble(a)
End Sub
I looked on http://www.mvps.org/dmcritchie/excel/event.htm#change
for ideas
and I copied some code and edited it to the above. Basically, I want
to
contents of the cell that was double clicked on to be taken to sub
dble. BUt
this should only happen if the cell clicked on was in column A. You
can see
my attempt at doing this. For some reason it does not work though.
When I
double click nothin happens. But when I get rid of my code it works
fine. Any
suggestions...
--
Dave