Tom,
Err... need to think about this for a while .

NickHK

"Tom Ogilvy" wrote in message
...
Yes, but in the routine where you pass the object reference, you when you
change it to point to a different object, you don't change the original -
just like with non object variables. The byval placeholder in the called
function does initially point to the object, but if it is set to another
object, then you see the Value of the pointer has been passed, not the
pointer itself. It certainly isn't totally synonymous, but protect the
original reference. For the event, this protects the original reference
to
the triggering range if you try to use Target to point to something else
in
the event.

to illustrate:

Sub ABC()
Dim rngVal As Range
Dim rngRef As Range
Set rngVal = Range("A1")
Set rngRef = Range("A1")
EFG rngVal, rngRef
Debug.Print rngVal.Address, rngRef.Address
End Sub

Sub EFG(ByVal r1 As Range, ByRef r2 As Range)
Set r1 = Range("B2")
Set r2 = Range("B2")

End Sub

returns:

$A$1 $B$2

--
Regards,
Tom Ogilvy



"NickHK" wrote in message
...
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