Application inputbox, Range versus Cancel

[Neal Zimm]

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

 

[Dave Peterson]

#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]

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

 

[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

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 Zimm]

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.

 

[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???

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 Zimm]

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.

 

[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.

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

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]

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

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.

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.