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