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

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

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

Target is just a range, like ActiveCell or Selection. For this macro, Target
is the range (cell) that was double-clicked.


Like any range, you can get its associated information:

Target.Address is its address (as a string)
Target.Column is its column number
Target.Row is its row number
Target.Value is its value

etc.

--
Gary's Student


"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

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

Good explanation - thank you
--
Gary's Student


"Jim Thomlinson" wrote:

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

In this case, you are using the standard declartion for the beforedoubleclick
event. You should always select the event from the dropdowns at the top of
the appropriate class module (the one you are using) to get the correct
declaration.

Jim has given you an explanation of byval and byref, but in this case, it
really shouldn't be your decision. You should use the declarations as
designed - and if you select them out of the dropdown, you won't have a
problem with that.

"Amen"

Just to let you know I am through preaching.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote:

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

You are preaching to the choir... Halleluiah brother. (I didn't notice that
the declaration had been switched otherwise I would have mentioned it in my
reply during my rant on which method to choose. I assumed everyone used the
dropdown. Thanks for pointing that out.)
--
HTH...

Jim Thomlinson


"Tom Ogilvy" wrote:

In this case, you are using the standard declartion for the beforedoubleclick
event. You should always select the event from the dropdowns at the top of
the appropriate class module (the one you are using) to get the correct
declaration.

Jim has given you an explanation of byval and byref, but in this case, it
really shouldn't be your decision. You should use the declarations as
designed - and if you select them out of the dropdown, you won't have a
problem with that.

"Amen"

Just to let you know I am through preaching.

--
Regards,
Tom Ogilvy



"Jim Thomlinson" wrote:

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

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

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

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

Tom,
OK, that makes sense.
I was confusing what it means to pass an object (anyhow/anyway) by a pointer
and the effect of ByVal/ByRef.

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