question on gosub / return
now, in conjunction with my other post about using a variable to reference a
userform, i can use this and not use a Return:
If Target.Count = 1 Then
If Not Intersect(Target, Columns("I")) Is Nothing Then
uForm = "UF1"
GoSub fltrRoutine
ElseIf Not Intersect(Target, Columns("N")) Is Nothing Then
uForm = "UF2"
GoSub fltrRoutine
..
..
fltrRoutine
..
..
With VBA.UserForms.Add(uForm)
.Show
.Top = 100
End With
--
Gary
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
ok, thanks bob and peter for the insight. i've never used it, but it seem to
come in handy for this procedure. right now i have right click events on a
sheet and i was checking for the existence of an autofilter, and turning it on
and setting the criteria. i had the code in each event and jut wanted to use
the gosub to eliminate all of the duplicate lines of code (16 lines for each
event).
here is a snippet of what i used and it seems fine.
If Target.Count = 1 Then
If Not Intersect(Target, Columns("I")) Is Nothing Then
GoSub fltrRoutine
With UF1 ' this is the actual name of the form
.Show
End With
GoTo Xit
ElseIf Not Intersect(Target, Columns("I")) Is Nothing Then
GoSub fltrRoutine
With UF2 ' this is the actual name of the form
.Show
End With
GoTo Xit
--
Gary
"Peter T" <peter_t@discussions wrote in message
...
Sorry Bob, I don't quite follow what the problem might be in mixing Gosub &
Call or even how they might be 'mixed'. But my perspective is often
'clouded' with lack of clarity !
The only times I use Gosub is to do something short, by which I mean net
execution time of doing stuff relative to the time it takes to Call a
procedure and return an argument. Eg manipulating Longs & Booleans runs
pretty fast even with several lines of code and might be a candidate for
Gosub.
There's a trade off between speed vs clarity & re-usability.
Regards,
Peter T
"Bob Phillips" wrote in message
...
One thing that does bother me though is mixing Gosub and Call, that does
seem as if it will not aid clarity. What is your perspective on that?
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,
FWIW, in some situations I find Gosub can be quicker than the overhead
of
calling another function. In this highly contrived example 2-3 times
faster
Public Declare Function GetTickCount _
Lib "kernel32.dll" () As Long
Sub test()
Dim i&, t&, tExcl&
Const c As Long = 1000000
t = GetTickCount
Do
i = i + 1
Loop Until i = c
tExcl = GetTickCount - t
Debug.Print "Exclude Loop and the +1 "; tExcl
t = GetTickCount
i = 0
Do
GoSub myGoSub
Loop Until i = c
t = GetTickCount - t
Debug.Print "myGosub", t - tExcl
i = 0
t = GetTickCount
Do
SubArg i
Loop Until i = c
t = GetTickCount - t
Debug.Print "SubArg", t - tExcl
i = 0
t = GetTickCount
Do
FunArgBool i
Loop Until i = c
t = GetTickCount - t
Debug.Print "FunArgBool", t - tExcl
i = 0
t = GetTickCount
Do
FunArgVar i
Loop Until i = c
t = GetTickCount - t
Debug.Print "FunArgVar", t - tExcl
i = 0
t = GetTickCount
Do
i = FunReturn(i)
Loop Until i = c
t = GetTickCount - t
Debug.Print "FunReturn", t - tExcl
Exit Sub
myGoSub:
i = i + 1
Return
End Sub
Sub SubArg(ByRef n As Long)
n = n + 1
End Sub
Function FunArgBool(n As Long) As Boolean
n = n + 1
End Function
' Variant function slowest
Function FunArgVar(n As Long)
n = n + 1
End Function
Function FunReturn(ByVal n As Long) As Long
FunReturn = n + 1
End Function
Regards,
Peter T
"Bob Phillips" wrote in message
...
Gary,
I can't say that I ever use it, but I don't see why not, IF used in
moderation. My view is that such code, even Goto is fine if not
overdone.
IMO it is no more unstructured than anything else, although I might
baulk
at
any code that does a Goto a previous line, that could be difficult to
debug.
--
---
HTH
Bob
(change the xxxx to gmail if mailing direct)
"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
just wondering, is this a bad technique that should be avoided?
--
Gary
|