Hi -
I could not get the app input box to either accept a range,
or let the user click cancel to exit the vba sub of which
the code below is a part, until I used the on error 'method'.

Tried different variations for dim'ing UserRng and diff
values for app box Type:= values. All did not work.

1) Should i be nervous about using on error ..... method ?

2) What is another way, Not using On Error Resume Next
to a) accept range or b) let user click cancel as a
trigger to exit the vba sub?

Thanks,
Neal Z.



Dim UserRng As Range

Get_Sub: 'notes, Sub here means subscriber,
' Tb and Cr2 set up as constants for vbTab and vbCr
' to save a little typing. RMi... vars are numeric constants, too.

On Error Resume Next

Set UserRng = Application.InputBox _
(Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
& Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
& "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)

If Err.Number <> 0 Then
Err.Number = 0
Exit Sub
Else
sCellAdr = UserRng.Address
End If
Return
--
Neal Z

#1. I wouldn't be nervous about that technique--well, unless you're a nervous
person to begin with <vbg>. But it's not something that should add to your
overall "nervousoscity" index.

#2. Not that I know. But instead of checking for an error, I find this easier
to write and understand:

dim rng as range
set rng = nothing
on error resume next
set rng = application.inputbox(Prompt:="...", type:=8)
on error goto 0

if rng is nothing then
'user hit cancel
else
'user supplied a range
end if



Neal Zimm wrote:
>
> Hi -
> I could not get the app input box to either accept a range,
> or let the user click cancel to exit the vba sub of which
> the code below is a part, until I used the on error 'method'.
>
> Tried different variations for dim'ing UserRng and diff
> values for app box Type:= values. All did not work.
>
> 1) Should i be nervous about using on error ..... method ?
>
> 2) What is another way, Not using On Error Resume Next
> to a) accept range or b) let user click cancel as a
> trigger to exit the vba sub?
>
> Thanks,
> Neal Z.
>
> Dim UserRng As Range
>
> Get_Sub: 'notes, Sub here means subscriber,
> ' Tb and Cr2 set up as constants for vbTab and vbCr
> ' to save a little typing. RMi... vars are numeric constants, too.
>
> On Error Resume Next
>
> Set UserRng = Application.InputBox _
> (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
>
> If Err.Number <> 0 Then
> Err.Number = 0
> Exit Sub
> Else
> sCellAdr = UserRng.Address
> End If
> Return
> --
> Neal Z

--

Dave Peterson

Dave -
Thanks. like it a lot
BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL).
Part of my concern is I know damn little about the error handler, and the
whole concept of on error go to 0 leaves me numb.

Two small follow ups, please
1) Whenever I use on error resume next, I always set the err back to zero.
So, do I then still need to "goto 0" ?

2) If an err is NOT set back to 0, and the sub in which it happened
is exited, what's the harm in NOT setting it back to zero?

Thanks again, nervous Neal

--
Neal Z


"Dave Peterson" wrote:

> #1. I wouldn't be nervous about that technique--well, unless you're a nervous
> person to begin with <vbg>. But it's not something that should add to your
> overall "nervousoscity" index.
>
> #2. Not that I know. But instead of checking for an error, I find this easier
> to write and understand:
>
> dim rng as range
> set rng = nothing
> on error resume next
> set rng = application.inputbox(Prompt:="...", type:=8)
> on error goto 0
>
> if rng is nothing then
> 'user hit cancel
> else
> 'user supplied a range
> end if
>
>
>
> Neal Zimm wrote:
> >
> > Hi -
> > I could not get the app input box to either accept a range,
> > or let the user click cancel to exit the vba sub of which
> > the code below is a part, until I used the on error 'method'.
> >
> > Tried different variations for dim'ing UserRng and diff
> > values for app box Type:= values. All did not work.
> >
> > 1) Should i be nervous about using on error ..... method ?
> >
> > 2) What is another way, Not using On Error Resume Next
> > to a) accept range or b) let user click cancel as a
> > trigger to exit the vba sub?
> >
> > Thanks,
> > Neal Z.
> >
> > Dim UserRng As Range
> >
> > Get_Sub: 'notes, Sub here means subscriber,
> > ' Tb and Cr2 set up as constants for vbTab and vbCr
> > ' to save a little typing. RMi... vars are numeric constants, too.
> >
> > On Error Resume Next
> >
> > Set UserRng = Application.InputBox _
> > (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> > & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> > & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> > Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
> >
> > If Err.Number <> 0 Then
> > Err.Number = 0
> > Exit Sub
> > Else
> > sCellAdr = UserRng.Address
> > End If
> > Return
> > --
> > Neal Z
>
> --
>
> Dave Peterson
>

1. If I use "on error resume next", I never clear the error. But you'd want to
turn error handling back on. I only use that sequence between things I know
could cause errors. As soon as I expect no errors, then I want excel/VBA to
handle the error the way I want (goto 0 or goto errHandler).

2. If you don't handle the error in the subroutine, then the error handler will
come from the calling sub:

Option Explicit
Sub test01()
On Error Resume Next
Call test02
Debug.Print Err.Number
End Sub
Sub test02()
'run it yourself and you'll get an error
'but call it from test01 and test01 will handle the error
MsgBox 1 / 0
End Sub





Neal Zimm wrote:
>
> Dave -
> Thanks. like it a lot
> BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL).
> Part of my concern is I know damn little about the error handler, and the
> whole concept of on error go to 0 leaves me numb.
>
> Two small follow ups, please
> 1) Whenever I use on error resume next, I always set the err back to zero.
> So, do I then still need to "goto 0" ?
>
> 2) If an err is NOT set back to 0, and the sub in which it happened
> is exited, what's the harm in NOT setting it back to zero?
>
> Thanks again, nervous Neal
>
> --
> Neal Z
>
> "Dave Peterson" wrote:
>
> > #1. I wouldn't be nervous about that technique--well, unless you're a nervous
> > person to begin with <vbg>. But it's not something that should add to your
> > overall "nervousoscity" index.
> >
> > #2. Not that I know. But instead of checking for an error, I find this easier
> > to write and understand:
> >
> > dim rng as range
> > set rng = nothing
> > on error resume next
> > set rng = application.inputbox(Prompt:="...", type:=8)
> > on error goto 0
> >
> > if rng is nothing then
> > 'user hit cancel
> > else
> > 'user supplied a range
> > end if
> >
> >
> >
> > Neal Zimm wrote:
> > >
> > > Hi -
> > > I could not get the app input box to either accept a range,
> > > or let the user click cancel to exit the vba sub of which
> > > the code below is a part, until I used the on error 'method'.
> > >
> > > Tried different variations for dim'ing UserRng and diff
> > > values for app box Type:= values. All did not work.
> > >
> > > 1) Should i be nervous about using on error ..... method ?
> > >
> > > 2) What is another way, Not using On Error Resume Next
> > > to a) accept range or b) let user click cancel as a
> > > trigger to exit the vba sub?
> > >
> > > Thanks,
> > > Neal Z.
> > >
> > > Dim UserRng As Range
> > >
> > > Get_Sub: 'notes, Sub here means subscriber,
> > > ' Tb and Cr2 set up as constants for vbTab and vbCr
> > > ' to save a little typing. RMi... vars are numeric constants, too.
> > >
> > > On Error Resume Next
> > >
> > > Set UserRng = Application.InputBox _
> > > (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> > > & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> > > & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> > > Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
> > >
> > > If Err.Number <> 0 Then
> > > Err.Number = 0
> > > Exit Sub
> > > Else
> > > sCellAdr = UserRng.Address
> > > End If
> > > Return
> > > --
> > > Neal Z
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave -
Thanks so much, the fog is beginning to lift.
I'll test your example soon.
I've got some gray hair, and since I started to write this pretty big
addin without any formal VBA training, I think I'm kinda doing with my code,
(not relating to this thread)
what the VBA error handler does. It stems from some modular assembler
language
coding I did in the dark ages. (circa early 1970's)

What's below, really does not 'require' a response from you, unless you're
so inclined.

I use many levels of called subs. Each and every one has something like:
call macname(arg,arg,arg,Status)

When the sub that's running encounters an APPLICATION error, I set the
Status string var to 1 of several standard values in my app. If it's a 'bad'
one, it ripples up all the back to the sub that the User is going execute.
e.g.

call macnameA(arg,arg,arg,Status)
if instr(status,'my code value') > 0 then
'lots of various code here
exit sub
end if

this repeats all the way 'to the top' by the mac that called macnameA,
and so on.

Probably should do some boning up on the error handler. sounds like I
could have saved myself a ton of time.

Again Thanks,
Neal Z.





--
Neal Z


"Dave Peterson" wrote:

> 1. If I use "on error resume next", I never clear the error. But you'd want to
> turn error handling back on. I only use that sequence between things I know
> could cause errors. As soon as I expect no errors, then I want excel/VBA to
> handle the error the way I want (goto 0 or goto errHandler).
>
> 2. If you don't handle the error in the subroutine, then the error handler will
> come from the calling sub:
>
> Option Explicit
> Sub test01()
> On Error Resume Next
> Call test02
> Debug.Print Err.Number
> End Sub
> Sub test02()
> 'run it yourself and you'll get an error
> 'but call it from test01 and test01 will handle the error
> MsgBox 1 / 0
> End Sub
>
>
>
>
>
> Neal Zimm wrote:
> >
> > Dave -
> > Thanks. like it a lot
> > BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL).
> > Part of my concern is I know damn little about the error handler, and the
> > whole concept of on error go to 0 leaves me numb.
> >
> > Two small follow ups, please
> > 1) Whenever I use on error resume next, I always set the err back to zero.
> > So, do I then still need to "goto 0" ?
> >
> > 2) If an err is NOT set back to 0, and the sub in which it happened
> > is exited, what's the harm in NOT setting it back to zero?
> >
> > Thanks again, nervous Neal
> >
> > --
> > Neal Z
> >
> > "Dave Peterson" wrote:
> >
> > > #1. I wouldn't be nervous about that technique--well, unless you're a nervous
> > > person to begin with <vbg>. But it's not something that should add to your
> > > overall "nervousoscity" index.
> > >
> > > #2. Not that I know. But instead of checking for an error, I find this easier
> > > to write and understand:
> > >
> > > dim rng as range
> > > set rng = nothing
> > > on error resume next
> > > set rng = application.inputbox(Prompt:="...", type:=8)
> > > on error goto 0
> > >
> > > if rng is nothing then
> > > 'user hit cancel
> > > else
> > > 'user supplied a range
> > > end if
> > >
> > >
> > >
> > > Neal Zimm wrote:
> > > >
> > > > Hi -
> > > > I could not get the app input box to either accept a range,
> > > > or let the user click cancel to exit the vba sub of which
> > > > the code below is a part, until I used the on error 'method'.
> > > >
> > > > Tried different variations for dim'ing UserRng and diff
> > > > values for app box Type:= values. All did not work.
> > > >
> > > > 1) Should i be nervous about using on error ..... method ?
> > > >
> > > > 2) What is another way, Not using On Error Resume Next
> > > > to a) accept range or b) let user click cancel as a
> > > > trigger to exit the vba sub?
> > > >
> > > > Thanks,
> > > > Neal Z.
> > > >
> > > > Dim UserRng As Range
> > > >
> > > > Get_Sub: 'notes, Sub here means subscriber,
> > > > ' Tb and Cr2 set up as constants for vbTab and vbCr
> > > > ' to save a little typing. RMi... vars are numeric constants, too.
> > > >
> > > > On Error Resume Next
> > > >
> > > > Set UserRng = Application.InputBox _
> > > > (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> > > > & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> > > > & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> > > > Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
> > > >
> > > > If Err.Number <> 0 Then
> > > > Err.Number = 0
> > > > Exit Sub
> > > > Else
> > > > sCellAdr = UserRng.Address
> > > > End If
> > > > Return
> > > > --
> > > > Neal Z
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Just a couple more alternatives...

You may want to consider using functions instead of Subroutines. Then you can
pass a value (boolean) back to the calling procedure.

dim OkToContinue as boolean
oktocontinue = call macname(arg1, arg2, arg3)

if oktocontinue then ....

....

function macname(somearg1 as..., somearg2 as ...) as boolean

if somejunkhere then
macname = true
else
macname = false
end if

====
Or you could declare a public variable that you can use that's visible to all
the procedures you need.

===
If your sub/function can return lots of different results, you could change it
to:
function macname(somearg1 as..., somearg2 as ...) as Variant 'String???



Neal Zimm wrote:
>
> Dave -
> Thanks so much, the fog is beginning to lift.
> I'll test your example soon.
> I've got some gray hair, and since I started to write this pretty big
> addin without any formal VBA training, I think I'm kinda doing with my code,
> (not relating to this thread)
> what the VBA error handler does. It stems from some modular assembler
> language
> coding I did in the dark ages. (circa early 1970's)
>
> What's below, really does not 'require' a response from you, unless you're
> so inclined.
>
> I use many levels of called subs. Each and every one has something like:
> call macname(arg,arg,arg,Status)
>
> When the sub that's running encounters an APPLICATION error, I set the
> Status string var to 1 of several standard values in my app. If it's a 'bad'
> one, it ripples up all the back to the sub that the User is going execute.
> e.g.
>
> call macnameA(arg,arg,arg,Status)
> if instr(status,'my code value') > 0 then
> 'lots of various code here
> exit sub
> end if
>
> this repeats all the way 'to the top' by the mac that called macnameA,
> and so on.
>
> Probably should do some boning up on the error handler. sounds like I
> could have saved myself a ton of time.
>
> Again Thanks,
> Neal Z.
>
> --
> Neal Z
>
> "Dave Peterson" wrote:
>
> > 1. If I use "on error resume next", I never clear the error. But you'd want to
> > turn error handling back on. I only use that sequence between things I know
> > could cause errors. As soon as I expect no errors, then I want excel/VBA to
> > handle the error the way I want (goto 0 or goto errHandler).
> >
> > 2. If you don't handle the error in the subroutine, then the error handler will
> > come from the calling sub:
> >
> > Option Explicit
> > Sub test01()
> > On Error Resume Next
> > Call test02
> > Debug.Print Err.Number
> > End Sub
> > Sub test02()
> > 'run it yourself and you'll get an error
> > 'but call it from test01 and test01 will handle the error
> > MsgBox 1 / 0
> > End Sub
> >
> >
> >
> >
> >
> > Neal Zimm wrote:
> > >
> > > Dave -
> > > Thanks. like it a lot
> > > BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL).
> > > Part of my concern is I know damn little about the error handler, and the
> > > whole concept of on error go to 0 leaves me numb.
> > >
> > > Two small follow ups, please
> > > 1) Whenever I use on error resume next, I always set the err back to zero.
> > > So, do I then still need to "goto 0" ?
> > >
> > > 2) If an err is NOT set back to 0, and the sub in which it happened
> > > is exited, what's the harm in NOT setting it back to zero?
> > >
> > > Thanks again, nervous Neal
> > >
> > > --
> > > Neal Z
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > #1. I wouldn't be nervous about that technique--well, unless you're a nervous
> > > > person to begin with <vbg>. But it's not something that should add to your
> > > > overall "nervousoscity" index.
> > > >
> > > > #2. Not that I know. But instead of checking for an error, I find this easier
> > > > to write and understand:
> > > >
> > > > dim rng as range
> > > > set rng = nothing
> > > > on error resume next
> > > > set rng = application.inputbox(Prompt:="...", type:=8)
> > > > on error goto 0
> > > >
> > > > if rng is nothing then
> > > > 'user hit cancel
> > > > else
> > > > 'user supplied a range
> > > > end if
> > > >
> > > >
> > > >
> > > > Neal Zimm wrote:
> > > > >
> > > > > Hi -
> > > > > I could not get the app input box to either accept a range,
> > > > > or let the user click cancel to exit the vba sub of which
> > > > > the code below is a part, until I used the on error 'method'.
> > > > >
> > > > > Tried different variations for dim'ing UserRng and diff
> > > > > values for app box Type:= values. All did not work.
> > > > >
> > > > > 1) Should i be nervous about using on error ..... method ?
> > > > >
> > > > > 2) What is another way, Not using On Error Resume Next
> > > > > to a) accept range or b) let user click cancel as a
> > > > > trigger to exit the vba sub?
> > > > >
> > > > > Thanks,
> > > > > Neal Z.
> > > > >
> > > > > Dim UserRng As Range
> > > > >
> > > > > Get_Sub: 'notes, Sub here means subscriber,
> > > > > ' Tb and Cr2 set up as constants for vbTab and vbCr
> > > > > ' to save a little typing. RMi... vars are numeric constants, too.
> > > > >
> > > > > On Error Resume Next
> > > > >
> > > > > Set UserRng = Application.InputBox _
> > > > > (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> > > > > & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> > > > > & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> > > > > Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
> > > > >
> > > > > If Err.Number <> 0 Then
> > > > > Err.Number = 0
> > > > > Exit Sub
> > > > > Else
> > > > > sCellAdr = UserRng.Address
> > > > > End If
> > > > > Return
> > > > > --
> > > > > Neal Z
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Dave -
Again thanks for the ideas. I do use functions as you suggest; they tend
to be 'smaller' than my called subs with a narrower scope of processing.
(just my preferance, I realize there's not much of a 'technical' difference
re; the vba inside a function vs. sub.)
e.g. If true = AccountEditF(ACnum, other vars) then ....

Re: functions as variants, somewhere I read in the MSo help that variant
processing is slower than 'other' stuff, so I've tended to stay away from it.
I've worked up a 'micro' timer routine to test how long diff versions of code
take, and in truth, I've not tested the difference variant vs. not for
function xxxxxxxxx As Variant.

The functions I've written to date, have not required variant output, tho'
I'll keep that thought in mind going forward.

Last question this thread, I hope, How much difference is there, in general,
between 'variant' processing and not-variant processing ?

You've been a big help, Thanks and Regards,
Neal Z.





--
Neal Z


"Dave Peterson" wrote:

> Just a couple more alternatives...
>
> You may want to consider using functions instead of Subroutines. Then you can
> pass a value (boolean) back to the calling procedure.
>
> dim OkToContinue as boolean
> oktocontinue = call macname(arg1, arg2, arg3)
>
> if oktocontinue then ....
>
> ....
>
> function macname(somearg1 as..., somearg2 as ...) as boolean
>
> if somejunkhere then
> macname = true
> else
> macname = false
> end if
>
> ====
> Or you could declare a public variable that you can use that's visible to all
> the procedures you need.
>
> ===
> If your sub/function can return lots of different results, you could change it
> to:
> function macname(somearg1 as..., somearg2 as ...) as Variant 'String???
>
>
>
> Neal Zimm wrote:
> >
> > Dave -
> > Thanks so much, the fog is beginning to lift.
> > I'll test your example soon.
> > I've got some gray hair, and since I started to write this pretty big
> > addin without any formal VBA training, I think I'm kinda doing with my code,
> > (not relating to this thread)
> > what the VBA error handler does. It stems from some modular assembler
> > language
> > coding I did in the dark ages. (circa early 1970's)
> >
> > What's below, really does not 'require' a response from you, unless you're
> > so inclined.
> >
> > I use many levels of called subs. Each and every one has something like:
> > call macname(arg,arg,arg,Status)
> >
> > When the sub that's running encounters an APPLICATION error, I set the
> > Status string var to 1 of several standard values in my app. If it's a 'bad'
> > one, it ripples up all the back to the sub that the User is going execute.
> > e.g.
> >
> > call macnameA(arg,arg,arg,Status)
> > if instr(status,'my code value') > 0 then
> > 'lots of various code here
> > exit sub
> > end if
> >
> > this repeats all the way 'to the top' by the mac that called macnameA,
> > and so on.
> >
> > Probably should do some boning up on the error handler. sounds like I
> > could have saved myself a ton of time.
> >
> > Again Thanks,
> > Neal Z.
> >
> > --
> > Neal Z
> >
> > "Dave Peterson" wrote:
> >
> > > 1. If I use "on error resume next", I never clear the error. But you'd want to
> > > turn error handling back on. I only use that sequence between things I know
> > > could cause errors. As soon as I expect no errors, then I want excel/VBA to
> > > handle the error the way I want (goto 0 or goto errHandler).
> > >
> > > 2. If you don't handle the error in the subroutine, then the error handler will
> > > come from the calling sub:
> > >
> > > Option Explicit
> > > Sub test01()
> > > On Error Resume Next
> > > Call test02
> > > Debug.Print Err.Number
> > > End Sub
> > > Sub test02()
> > > 'run it yourself and you'll get an error
> > > 'but call it from test01 and test01 will handle the error
> > > MsgBox 1 / 0
> > > End Sub
> > >
> > >
> > >
> > >
> > >
> > > Neal Zimm wrote:
> > > >
> > > > Dave -
> > > > Thanks. like it a lot
> > > > BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL).
> > > > Part of my concern is I know damn little about the error handler, and the
> > > > whole concept of on error go to 0 leaves me numb.
> > > >
> > > > Two small follow ups, please
> > > > 1) Whenever I use on error resume next, I always set the err back to zero.
> > > > So, do I then still need to "goto 0" ?
> > > >
> > > > 2) If an err is NOT set back to 0, and the sub in which it happened
> > > > is exited, what's the harm in NOT setting it back to zero?
> > > >
> > > > Thanks again, nervous Neal
> > > >
> > > > --
> > > > Neal Z
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > #1. I wouldn't be nervous about that technique--well, unless you're a nervous
> > > > > person to begin with <vbg>. But it's not something that should add to your
> > > > > overall "nervousoscity" index.
> > > > >
> > > > > #2. Not that I know. But instead of checking for an error, I find this easier
> > > > > to write and understand:
> > > > >
> > > > > dim rng as range
> > > > > set rng = nothing
> > > > > on error resume next
> > > > > set rng = application.inputbox(Prompt:="...", type:=8)
> > > > > on error goto 0
> > > > >
> > > > > if rng is nothing then
> > > > > 'user hit cancel
> > > > > else
> > > > > 'user supplied a range
> > > > > end if
> > > > >
> > > > >
> > > > >
> > > > > Neal Zimm wrote:
> > > > > >
> > > > > > Hi -
> > > > > > I could not get the app input box to either accept a range,
> > > > > > or let the user click cancel to exit the vba sub of which
> > > > > > the code below is a part, until I used the on error 'method'.
> > > > > >
> > > > > > Tried different variations for dim'ing UserRng and diff
> > > > > > values for app box Type:= values. All did not work.
> > > > > >
> > > > > > 1) Should i be nervous about using on error ..... method ?
> > > > > >
> > > > > > 2) What is another way, Not using On Error Resume Next
> > > > > > to a) accept range or b) let user click cancel as a
> > > > > > trigger to exit the vba sub?
> > > > > >
> > > > > > Thanks,
> > > > > > Neal Z.
> > > > > >
> > > > > > Dim UserRng As Range
> > > > > >
> > > > > > Get_Sub: 'notes, Sub here means subscriber,
> > > > > > ' Tb and Cr2 set up as constants for vbTab and vbCr
> > > > > > ' to save a little typing. RMi... vars are numeric constants, too.
> > > > > >
> > > > > > On Error Resume Next
> > > > > >
> > > > > > Set UserRng = Application.InputBox _
> > > > > > (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> > > > > > & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> > > > > > & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> > > > > > Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
> > > > > >
> > > > > > If Err.Number <> 0 Then
> > > > > > Err.Number = 0
> > > > > > Exit Sub
> > > > > > Else
> > > > > > sCellAdr = UserRng.Address
> > > > > > End If
> > > > > > Return
> > > > > > --
> > > > > > Neal Z
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

I've read the same sort of things that you've read. But if you want the
function to return a string, a number, an error, an object, or whatever, then
using a Variant seems like the way to go.

On the other hand, if you're declaring variables that you know will be used to
count things, you'd use Long instead of Variant. If you're dealing with
strings, then use String.



Neal Zimm wrote:
>
> Dave -
> Again thanks for the ideas. I do use functions as you suggest; they tend
> to be 'smaller' than my called subs with a narrower scope of processing.
> (just my preferance, I realize there's not much of a 'technical' difference
> re; the vba inside a function vs. sub.)
> e.g. If true = AccountEditF(ACnum, other vars) then ....
>
> Re: functions as variants, somewhere I read in the MSo help that variant
> processing is slower than 'other' stuff, so I've tended to stay away from it.
> I've worked up a 'micro' timer routine to test how long diff versions of code
> take, and in truth, I've not tested the difference variant vs. not for
> function xxxxxxxxx As Variant.
>
> The functions I've written to date, have not required variant output, tho'
> I'll keep that thought in mind going forward.
>
> Last question this thread, I hope, How much difference is there, in general,
> between 'variant' processing and not-variant processing ?
>
> You've been a big help, Thanks and Regards,
> Neal Z.
>
>
>
> --
> Neal Z
>
> "Dave Peterson" wrote:
>
> > Just a couple more alternatives...
> >
> > You may want to consider using functions instead of Subroutines. Then you can
> > pass a value (boolean) back to the calling procedure.
> >
> > dim OkToContinue as boolean
> > oktocontinue = call macname(arg1, arg2, arg3)
> >
> > if oktocontinue then ....
> >
> > ....
> >
> > function macname(somearg1 as..., somearg2 as ...) as boolean
> >
> > if somejunkhere then
> > macname = true
> > else
> > macname = false
> > end if
> >
> > ====
> > Or you could declare a public variable that you can use that's visible to all
> > the procedures you need.
> >
> > ===
> > If your sub/function can return lots of different results, you could change it
> > to:
> > function macname(somearg1 as..., somearg2 as ...) as Variant 'String???
> >
> >
> >
> > Neal Zimm wrote:
> > >
> > > Dave -
> > > Thanks so much, the fog is beginning to lift.
> > > I'll test your example soon.
> > > I've got some gray hair, and since I started to write this pretty big
> > > addin without any formal VBA training, I think I'm kinda doing with my code,
> > > (not relating to this thread)
> > > what the VBA error handler does. It stems from some modular assembler
> > > language
> > > coding I did in the dark ages. (circa early 1970's)
> > >
> > > What's below, really does not 'require' a response from you, unless you're
> > > so inclined.
> > >
> > > I use many levels of called subs. Each and every one has something like:
> > > call macname(arg,arg,arg,Status)
> > >
> > > When the sub that's running encounters an APPLICATION error, I set the
> > > Status string var to 1 of several standard values in my app. If it's a 'bad'
> > > one, it ripples up all the back to the sub that the User is going execute.
> > > e.g.
> > >
> > > call macnameA(arg,arg,arg,Status)
> > > if instr(status,'my code value') > 0 then
> > > 'lots of various code here
> > > exit sub
> > > end if
> > >
> > > this repeats all the way 'to the top' by the mac that called macnameA,
> > > and so on.
> > >
> > > Probably should do some boning up on the error handler. sounds like I
> > > could have saved myself a ton of time.
> > >
> > > Again Thanks,
> > > Neal Z.
> > >
> > > --
> > > Neal Z
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 1. If I use "on error resume next", I never clear the error. But you'd want to
> > > > turn error handling back on. I only use that sequence between things I know
> > > > could cause errors. As soon as I expect no errors, then I want excel/VBA to
> > > > handle the error the way I want (goto 0 or goto errHandler).
> > > >
> > > > 2. If you don't handle the error in the subroutine, then the error handler will
> > > > come from the calling sub:
> > > >
> > > > Option Explicit
> > > > Sub test01()
> > > > On Error Resume Next
> > > > Call test02
> > > > Debug.Print Err.Number
> > > > End Sub
> > > > Sub test02()
> > > > 'run it yourself and you'll get an error
> > > > 'but call it from test01 and test01 will handle the error
> > > > MsgBox 1 / 0
> > > > End Sub
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Neal Zimm wrote:
> > > > >
> > > > > Dave -
> > > > > Thanks. like it a lot
> > > > > BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL).
> > > > > Part of my concern is I know damn little about the error handler, and the
> > > > > whole concept of on error go to 0 leaves me numb.
> > > > >
> > > > > Two small follow ups, please
> > > > > 1) Whenever I use on error resume next, I always set the err back to zero.
> > > > > So, do I then still need to "goto 0" ?
> > > > >
> > > > > 2) If an err is NOT set back to 0, and the sub in which it happened
> > > > > is exited, what's the harm in NOT setting it back to zero?
> > > > >
> > > > > Thanks again, nervous Neal
> > > > >
> > > > > --
> > > > > Neal Z
> > > > >
> > > > > "Dave Peterson" wrote:
> > > > >
> > > > > > #1. I wouldn't be nervous about that technique--well, unless you're a nervous
> > > > > > person to begin with <vbg>. But it's not something that should add to your
> > > > > > overall "nervousoscity" index.
> > > > > >
> > > > > > #2. Not that I know. But instead of checking for an error, I find this easier
> > > > > > to write and understand:
> > > > > >
> > > > > > dim rng as range
> > > > > > set rng = nothing
> > > > > > on error resume next
> > > > > > set rng = application.inputbox(Prompt:="...", type:=8)
> > > > > > on error goto 0
> > > > > >
> > > > > > if rng is nothing then
> > > > > > 'user hit cancel
> > > > > > else
> > > > > > 'user supplied a range
> > > > > > end if
> > > > > >
> > > > > >
> > > > > >
> > > > > > Neal Zimm wrote:
> > > > > > >
> > > > > > > Hi -
> > > > > > > I could not get the app input box to either accept a range,
> > > > > > > or let the user click cancel to exit the vba sub of which
> > > > > > > the code below is a part, until I used the on error 'method'.
> > > > > > >
> > > > > > > Tried different variations for dim'ing UserRng and diff
> > > > > > > values for app box Type:= values. All did not work.
> > > > > > >
> > > > > > > 1) Should i be nervous about using on error ..... method ?
> > > > > > >
> > > > > > > 2) What is another way, Not using On Error Resume Next
> > > > > > > to a) accept range or b) let user click cancel as a
> > > > > > > trigger to exit the vba sub?
> > > > > > >
> > > > > > > Thanks,
> > > > > > > Neal Z.
> > > > > > >
> > > > > > > Dim UserRng As Range
> > > > > > >
> > > > > > > Get_Sub: 'notes, Sub here means subscriber,
> > > > > > > ' Tb and Cr2 set up as constants for vbTab and vbCr
> > > > > > > ' to save a little typing. RMi... vars are numeric constants, too.
> > > > > > >
> > > > > > > On Error Resume Next
> > > > > > >
> > > > > > > Set UserRng = Application.InputBox _
> > > > > > > (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> > > > > > > & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> > > > > > > & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> > > > > > > Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
> > > > > > >
> > > > > > > If Err.Number <> 0 Then
> > > > > > > Err.Number = 0
> > > > > > > Exit Sub
> > > > > > > Else
> > > > > > > sCellAdr = UserRng.Address
> > > > > > > End If
> > > > > > > Return
> > > > > > > --
> > > > > > > Neal Z
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Dave Peterson
> > > > > >
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

D -
Good words, thanks, over and out for now.
--
Neal Z


"Dave Peterson" wrote:

> I've read the same sort of things that you've read. But if you want the
> function to return a string, a number, an error, an object, or whatever, then
> using a Variant seems like the way to go.
>
> On the other hand, if you're declaring variables that you know will be used to
> count things, you'd use Long instead of Variant. If you're dealing with
> strings, then use String.
>
>
>
> Neal Zimm wrote:
> >
> > Dave -
> > Again thanks for the ideas. I do use functions as you suggest; they tend
> > to be 'smaller' than my called subs with a narrower scope of processing.
> > (just my preferance, I realize there's not much of a 'technical' difference
> > re; the vba inside a function vs. sub.)
> > e.g. If true = AccountEditF(ACnum, other vars) then ....
> >
> > Re: functions as variants, somewhere I read in the MSo help that variant
> > processing is slower than 'other' stuff, so I've tended to stay away from it.
> > I've worked up a 'micro' timer routine to test how long diff versions of code
> > take, and in truth, I've not tested the difference variant vs. not for
> > function xxxxxxxxx As Variant.
> >
> > The functions I've written to date, have not required variant output, tho'
> > I'll keep that thought in mind going forward.
> >
> > Last question this thread, I hope, How much difference is there, in general,
> > between 'variant' processing and not-variant processing ?
> >
> > You've been a big help, Thanks and Regards,
> > Neal Z.
> >
> >
> >
> > --
> > Neal Z
> >
> > "Dave Peterson" wrote:
> >
> > > Just a couple more alternatives...
> > >
> > > You may want to consider using functions instead of Subroutines. Then you can
> > > pass a value (boolean) back to the calling procedure.
> > >
> > > dim OkToContinue as boolean
> > > oktocontinue = call macname(arg1, arg2, arg3)
> > >
> > > if oktocontinue then ....
> > >
> > > ....
> > >
> > > function macname(somearg1 as..., somearg2 as ...) as boolean
> > >
> > > if somejunkhere then
> > > macname = true
> > > else
> > > macname = false
> > > end if
> > >
> > > ====
> > > Or you could declare a public variable that you can use that's visible to all
> > > the procedures you need.
> > >
> > > ===
> > > If your sub/function can return lots of different results, you could change it
> > > to:
> > > function macname(somearg1 as..., somearg2 as ...) as Variant 'String???
> > >
> > >
> > >
> > > Neal Zimm wrote:
> > > >
> > > > Dave -
> > > > Thanks so much, the fog is beginning to lift.
> > > > I'll test your example soon.
> > > > I've got some gray hair, and since I started to write this pretty big
> > > > addin without any formal VBA training, I think I'm kinda doing with my code,
> > > > (not relating to this thread)
> > > > what the VBA error handler does. It stems from some modular assembler
> > > > language
> > > > coding I did in the dark ages. (circa early 1970's)
> > > >
> > > > What's below, really does not 'require' a response from you, unless you're
> > > > so inclined.
> > > >
> > > > I use many levels of called subs. Each and every one has something like:
> > > > call macname(arg,arg,arg,Status)
> > > >
> > > > When the sub that's running encounters an APPLICATION error, I set the
> > > > Status string var to 1 of several standard values in my app. If it's a 'bad'
> > > > one, it ripples up all the back to the sub that the User is going execute.
> > > > e.g.
> > > >
> > > > call macnameA(arg,arg,arg,Status)
> > > > if instr(status,'my code value') > 0 then
> > > > 'lots of various code here
> > > > exit sub
> > > > end if
> > > >
> > > > this repeats all the way 'to the top' by the mac that called macnameA,
> > > > and so on.
> > > >
> > > > Probably should do some boning up on the error handler. sounds like I
> > > > could have saved myself a ton of time.
> > > >
> > > > Again Thanks,
> > > > Neal Z.
> > > >
> > > > --
> > > > Neal Z
> > > >
> > > > "Dave Peterson" wrote:
> > > >
> > > > > 1. If I use "on error resume next", I never clear the error. But you'd want to
> > > > > turn error handling back on. I only use that sequence between things I know
> > > > > could cause errors. As soon as I expect no errors, then I want excel/VBA to
> > > > > handle the error the way I want (goto 0 or goto errHandler).
> > > > >
> > > > > 2. If you don't handle the error in the subroutine, then the error handler will
> > > > > come from the calling sub:
> > > > >
> > > > > Option Explicit
> > > > > Sub test01()
> > > > > On Error Resume Next
> > > > > Call test02
> > > > > Debug.Print Err.Number
> > > > > End Sub
> > > > > Sub test02()
> > > > > 'run it yourself and you'll get an error
> > > > > 'but call it from test01 and test01 will handle the error
> > > > > MsgBox 1 / 0
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Neal Zimm wrote:
> > > > > >
> > > > > > Dave -
> > > > > > Thanks. like it a lot
> > > > > > BTW you left an 'i' out of nervousoscity, I think it's nervousosity (LOL).
> > > > > > Part of my concern is I know damn little about the error handler, and the
> > > > > > whole concept of on error go to 0 leaves me numb.
> > > > > >
> > > > > > Two small follow ups, please
> > > > > > 1) Whenever I use on error resume next, I always set the err back to zero.
> > > > > > So, do I then still need to "goto 0" ?
> > > > > >
> > > > > > 2) If an err is NOT set back to 0, and the sub in which it happened
> > > > > > is exited, what's the harm in NOT setting it back to zero?
> > > > > >
> > > > > > Thanks again, nervous Neal
> > > > > >
> > > > > > --
> > > > > > Neal Z
> > > > > >
> > > > > > "Dave Peterson" wrote:
> > > > > >
> > > > > > > #1. I wouldn't be nervous about that technique--well, unless you're a nervous
> > > > > > > person to begin with <vbg>. But it's not something that should add to your
> > > > > > > overall "nervousoscity" index.
> > > > > > >
> > > > > > > #2. Not that I know. But instead of checking for an error, I find this easier
> > > > > > > to write and understand:
> > > > > > >
> > > > > > > dim rng as range
> > > > > > > set rng = nothing
> > > > > > > on error resume next
> > > > > > > set rng = application.inputbox(Prompt:="...", type:=8)
> > > > > > > on error goto 0
> > > > > > >
> > > > > > > if rng is nothing then
> > > > > > > 'user hit cancel
> > > > > > > else
> > > > > > > 'user supplied a range
> > > > > > > end if
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Neal Zimm wrote:
> > > > > > > >
> > > > > > > > Hi -
> > > > > > > > I could not get the app input box to either accept a range,
> > > > > > > > or let the user click cancel to exit the vba sub of which
> > > > > > > > the code below is a part, until I used the on error 'method'.
> > > > > > > >
> > > > > > > > Tried different variations for dim'ing UserRng and diff
> > > > > > > > values for app box Type:= values. All did not work.
> > > > > > > >
> > > > > > > > 1) Should i be nervous about using on error ..... method ?
> > > > > > > >
> > > > > > > > 2) What is another way, Not using On Error Resume Next
> > > > > > > > to a) accept range or b) let user click cancel as a
> > > > > > > > trigger to exit the vba sub?
> > > > > > > >
> > > > > > > > Thanks,
> > > > > > > > Neal Z.
> > > > > > > >
> > > > > > > > Dim UserRng As Range
> > > > > > > >
> > > > > > > > Get_Sub: 'notes, Sub here means subscriber,
> > > > > > > > ' Tb and Cr2 set up as constants for vbTab and vbCr
> > > > > > > > ' to save a little typing. RMi... vars are numeric constants, too.
> > > > > > > >
> > > > > > > > On Error Resume Next
> > > > > > > >
> > > > > > > > Set UserRng = Application.InputBox _
> > > > > > > > (Prompt:="Click:" & Tb & "A cell in Address Row of Sub to View" _
> > > > > > > > & Cr2 & Tb & "Then Click OK" & Cr2 & Tb _
> > > > > > > > & "Cancel" & Tb & "To Stop Processing.", Title:=Title, _
> > > > > > > > Left:=RMiAIBupRx, Top:=RMiAIBupRy, Type:=8)
> > > > > > > >
> > > > > > > > If Err.Number <> 0 Then
> > > > > > > > Err.Number = 0
> > > > > > > > Exit Sub
> > > > > > > > Else
> > > > > > > > sCellAdr = UserRng.Address
> > > > > > > > End If
> > > > > > > > Return
> > > > > > > > --
> > > > > > > > Neal Z
> > > > > > >
> > > > > > > --
> > > > > > >
> > > > > > > Dave Peterson
> > > > > > >
> > > > >
> > > > > --
> > > > >
> > > > > Dave Peterson
> > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>