Weird flow with error handling with VBA Input Box?

Question

I am trying to understand why the following code is not giving me the expected behaviour.

I would like to ask the user for a decimal number (double). I have an error handler below but the way this program behaves, it will still throw errors when I input either a word or number. It will catch the error when I don't put anything though (empty input).

When I remove the if condition, it will work as expected but I do not know how to catch empty user inputs.

Any ideas would be greatly appreciated!

Sub MainTask()

Dim userInput As Double

TryAgain:
    On Error GoTo ErrorHandler
    userInput = InputBox("What is the amount purchased you would like to search for? ($)")
    If Len(userInput) = 0 Then
        Exit Sub
    End If
    MsgBox "You have entered a valid value!"
Exit Sub

ErrorHandler:
MsgBox "Please enter a valid value."
GoTo TryAgain

End Sub

When you GoTo TryAgain within the error handler you are still within the error handler for the current error so subsequent errors are unhandled. Replacing GoTo TryAgain with Resume would fix this but its not a very good way of doing things, for example the user will get an error on the cancel button, you should use a string and test it against "" and IsNumeric() — Alex K.
– Alex K., Commented May 30, 2017 at 17:31
InputBox returns a String. You cannot assign the return value to a Double. If it's not a number it will throw an error. — Kostas K.
– Kostas K., Commented May 30, 2017 at 17:35
For your specific case, use userInput = application.InputBox("What is the amount purchased you would like to search for? ($)", type:=1). For general case of handling inputs, refer to Scott Craner's answer. — GSerg
– GSerg, Commented May 30, 2017 at 17:35

Scott Craner · Accepted Answer · 2017-05-30 17:30:21Z

6

I do not like error handling as it will cover other problems:

Sub MainTask()

Dim userInput As Variant
Dim output As Double

Do
    userInput = InputBox("What is the amount purchased you would like to search for? ($)")
    If Len(userInput) = 0 Then Exit Sub
    If Not IsNumeric(userInput) Then MsgBox "Please enter a valid value."        
Loop Until IsNumeric(userInput)

output = userInput
MsgBox "You have entered a valid value! " & output   

End Sub

answered May 30, 2017 at 17:30

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Mathieu Guindon Over a year ago

Best error handling == no error handling. +1 for avoiding error conditions.

Mathieu Guindon Over a year ago

Indeed, indeed! Late-bound penalty cannot be overstated - especially in loops. I guess, as with many things, #ItDepends =)

Mathieu Guindon Over a year ago

@Rory the Excel.Application COM interface is marked as extensible. This means Application.IDontExist will happily compile, and it's at run-time that VBA will raise error 438 when it can't find that IDontExist member on the run-time type; when a member call is resolved at run-time, it's a late-bound call that the compiler cannot determine as valid: hence validation (for lack of a better word?) of late-bound calls is deferred to run-time. Early-bound calls are validated at compile-time, e.g. Application.WorksheetFunction.IDontExist won't compile.

Rory Over a year ago

@Mat'sMug I'm confused as to how that would be possible. If Application.blah will compile, so should Application.WorksheetFunction.blah by the same logic - and indeed it does. So effectively any application method/property should be late bound by implication, but it isn't. How come?

ThunderFrame Over a year ago

@Rory FWIW - The Application.FunctionName approach is late-bound and a throw-back to Excel 95. The chief differences being: Late-bound performance, no Intellisense, and silent errors. And while Application.WorksheetFunction would seem to be extensible, it does have early bound references to the worksheet functions, so you get early-bound performance, you get Intellisense, and you get compilation of any early-bound members that are misused or are missing arguments. For example, you can't use x = Application.WorksheetFunction.Sum without getting a Compile Error - Argument not optional.

|

Collectives™ on Stack Overflow

Weird flow with error handling with VBA Input Box?

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related