Application inputbox, Range versus Cancel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neal Zimm - 22 Sep 2007 11:34 GMT

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

Signature

Neal Z

Reply to this Message

Dave Peterson - 22 Sep 2007 13:59 GMT

#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

> Hi -
> I could not get the app input box to either accept a range,
[quoted text clipped - 36 lines]
> --
> Neal Z

Signature

Dave Peterson

Reply to this Message

Neal Zimm - 23 Sep 2007 00:08 GMT

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

Signature

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
[quoted text clipped - 55 lines]
> > --
> > Neal Z

Reply to this Message

Dave Peterson - 23 Sep 2007 00:21 GMT

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
[quoted text clipped - 77 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Neal Zimm - 23 Sep 2007 21:36 GMT

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.

Signature

Neal Z

> 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
[quoted text clipped - 97 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 23 Sep 2007 23:07 GMT

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.
[quoted text clipped - 140 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Neal Zimm - 25 Sep 2007 14:40 GMT

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.

Signature

Neal Z

> Just a couple more alternatives...
>
[quoted text clipped - 169 lines]
> > >
> > > Dave Peterson

Reply to this Message

Dave Peterson - 25 Sep 2007 17:09 GMT

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
[quoted text clipped - 198 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Neal Zimm - 25 Sep 2007 17:52 GMT

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

Signature

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
[quoted text clipped - 206 lines]
> > >
> > > Dave Peterson

Reply to this Message