just wondering, is this a bad technique that should be avoided?

--


Gary

It wouldn't surprise me if some say it's 'bad technique' but I use it
occasionally and not aware of ever encountering problems with it. Reminds me
of my routes :-)

Regards,
Peter T

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
just wondering, is this a bad technique that should be avoided?

--


Gary

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

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

Hi Peter,

I will certainly bear this in mind fir future use. As I said I don't use it,
but as I also said, I have no emotional bias to it <G.

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

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

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

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

the elseif line should reference a different column, not "I".

--


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

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

I'm confused as to whether fltrRoutine is a label or a named procedure. If
it's a label followed by code that won't otherwise be reached it should work
fine (though I wouldn't do it that way in your specific example). But you
say you have many events that use the same code, in which case surely you
would 'call' a procedure named fltrRoutine.

Regards,
Peter T


I take it 'fltrRoutine' is a label.
"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

peter:

it's a label.

once i determine the column for the right click event, it goes to the label and
turns on the filter if off and then loads the appropriate userform and it's
done.

the routine is at the bottom of the code structure.


--


Gary


"Peter T" <peter_t@discussions wrote in message
...
I'm confused as to whether fltrRoutine is a label or a named procedure. If
it's a label followed by code that won't otherwise be reached it should work
fine (though I wouldn't do it that way in your specific example). But you
say you have many events that use the same code, in which case surely you
would 'call' a procedure named fltrRoutine.

Regards,
Peter T


I take it 'fltrRoutine' is a label.
"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