Determining if Cancel Button pressed where InputBox variable declared as Double

Question

I have my variable declared as a double so I can perform mathematical operations on it.

I am trying to determine when the cancel button is pressed.

Dim thckmax As Double

thckmax = InputBox("What is the maximum nominal thickness?", "Enter Max Nominal Thickness Measurement")
If thckmax = 0 Then
     GoTo Line3
End If

thckmin = InputBox("What is the minimum nominal thickness?", "Enter Min Nominal Thickness Measurement")
If thckmin = 0 Then
     GoTo Line3
End If
thcknom = (thckmax + thckmin) / 2
Worksheets(1).Range("C" & cols + 2).Value = thcknom
.
.
.
Line3: ...

I know I used GoTo. It was a quick and easy fix to get the code up and running.

I get Run-Time error 13 type mismatch. I have also tried CDbl(...),StrPtr(...),IsEmpty(...) and instead of setting them equal to zero I have also tried

If thckmax = "" Then
    GoTo Line3
End If`

I cannot get anything to work as far as determining if cancel was pressed and going to Line3:.

All posts I found indicate the declared variable as a string, mine is a double.

Possible duplicate of [How to detect if user select cancel InputBox VBA Excel](https://stackoverflow.com/questions/26264814/how-to-detect-if-user-select-cancel-inputbox-vba-excel) — sous2817, Mar 07 '19 at 18:26
@sous2817 Unfortunately that one declares the variable as a string. Like I said above mine is declared a double so I can perform mathematical operations on it. — A. Jones, Mar 07 '19 at 18:28
when you debug the procedure, what is the value of `thckmax` ? — Damian, Mar 07 '19 at 18:43
@A.Jones perhaps see [this answer](https://stackoverflow.com/a/16061740/9245853) for how to use `InputBox` and validate that the input is numeric before attempting to use it. Entering non-numeric values in the code you have will throw the type mismatch error. — BigBen, Mar 07 '19 at 18:43
Possible duplicate of [Trouble with InputBoxes](https://stackoverflow.com/questions/16061562/trouble-with-inputboxes) — BigBen, Mar 07 '19 at 18:46
@Damian It does not return a value when I hit the cancel button. When i `Debug.Print thckmax` the immediate window remains blank and it throws the runtime error if I break it before it enters the if statement. However when I enter a value it shows up as the value I entered in the immediate window. — A. Jones, Mar 07 '19 at 18:50
@BigBen Unfortunately that has no examples with variables set up as doubles. I have thoroughly searched this site for a good 3 hours and have found only ones that have the variables set up as strings, and that is why I asked this question. — A. Jones, Mar 07 '19 at 18:52
As I said [earlier](https://stackoverflow.com/questions/55027629/inputbox-confusing-decimal-entries-with-strings/55027742?noredirect=1#comment96840110_55027742), the function returns a `String`. You need to capture the result into a `String`. Coercing it into a `Double` without any validation is literally asking for a type mismatch error. — Mathieu Guindon, Mar 07 '19 at 18:54
@MathieuGuindon I tried what you suggested earlier however as a VBA beginner I did not quite understand what you were meaning. — A. Jones, Mar 07 '19 at 18:57
Yeah, well, it's hard to explain these things in a little comment box. See the answer below, and note how the `InputBox` result is captured into a `String` that is then validated to be a number, and the `Double` only gets assigned if the `String` is confirmed to be a numeric value — Mathieu Guindon, Mar 07 '19 at 19:01

score 5 · Accepted Answer · answered Mar 07 '19 at 18:45

You could try something like this to test whether or not the input box was canceled and if it is numeric.

Dim thckmaxTest As String
Dim thckmax As Double

thckmaxTest = InputBox("What is the maximum nominal thickness?", "Enter Max Nominal Thickness Measurement")
If thckmaxTest <> vbNullString and IsNumeric(thckmaxTest) then thckmax = CDbl(thckmaxTest)
If thckmax = 0 Then
     GoTo Line3
End If

Dim thckminTest As String
Dim thckmin As Double 

thckminTest = InputBox("What is the minimum nominal thickness?", "Enter Min Nominal Thickness Measurement")
If thckminTest <> vbNullString and IsNumeric(thckmibTest) then thckmin = CDbl(thckminTest)
If thckmin = 0 Then
     GoTo Line3
End If
thcknom = (thckmax + thckmin) / 2
Worksheets(1).Range("C" & cols + 2).Value = thcknom

score 4 · Answer 2 · answered Mar 07 '19 at 19:01

As is noted in Microsoft's documentation, the InputBox function returns a string.

How then, you may ask, can you sometimes store the return value from an InputBox to an integer value? Because of implicit coercion. Basically, if you try to store a value in an incompatible variable, VBA attempts to coerce the value to the right data type. If you try to store a string value into a Double variable, VBA attempts to coerce the string into the right data type. This occurs whether you use InputBox or a hard-coded string. For example, the following snippets are equivalent:

Dim x as Double
x = "5"
''''''''''''''''''''''''''''''''''''''
Dim x As Double
x = InputBox("Enter a number")
' user enters 5

When using InputBox, you of course have no control over whether the user enters valid input. Which is why (as @Dude_Scott mentions), you should store user input into a string, and then make sure you have the right value.

If the user clicks Cancel in the inputbox, the empty string is returned (as per documentation above). Since the empty string can't be implicitly coerced to a double, an error is generated. It's the same thing that would happen if the user entered "apples" in the InputBox.

As @Dude_Scott notes, you should use IsNumeric (or something similar) to make sure that the user input is what you need. However, you don't need to include a check for a null or empty string (since IsNumeric returns False for those values). So you really just need something like this:

Public Sub foo()
    Dim xDouble As Double, xString As String

    xString = InputBox("Enter a number")

    If IsNumeric(xString) Then
        xDouble = CDbl(xString)
        MsgBox xDouble
    Else
        MsgBox "Invalid number"
    End If
End Sub

For more information about coercion, see the following Microsoft articles:

This is an accidental and undocumented quirk, but if you need to be able to tell a legit empty string from a cancelled inputbox, then `StrPtr(xString)` would return `0` if the inputbox was cancelled, and a non-zero value for an okayed inputbox with an empty string input. — Mathieu Guindon, Mar 07 '19 at 19:03
This was extremely helpful in my understanding of what @MathieuGuindon was trying to explain to me earlier. Thank you! — A. Jones, Mar 07 '19 at 19:06
See [this answer](https://stackoverflow.com/a/42016288/9990662) for a good description of `StrPtr`. — Zack, Mar 07 '19 at 19:14
I like the irony of `MsgBox xDouble`... .which is implicitly `MsgBox CStr(xDouble)` ;-) — Mathieu Guindon, Mar 07 '19 at 19:47

Mathieu Guindon · Answer 3 · 2019-03-07T23:17:46.030

All others I have found indicate the declared variable as a string, mine is a DOUBLE

You can't have a Double. The function returns a String, and you can't change that. Using a Double to capture the result will cause problems sooner or later.

What I was trying to explain in a comment box earlier, is that there's a possibility that 0 might be a valid input, so immediately converting the input into a Double is preventing you from being able to tell cancellation from a legit 0 - regardless of the type mismatch error that's guaranteed to happen whenever the resut isn't numeric.

As other answers show, this involves quite a bit of plumbing: enough to warrant being pulled into its own dedicated wrapper function.

The problem is that functions return one value, so you can return a Double and pick a specific "magic value" to mean "input was cancelled", but that's a poor practice.

A better way is to make the wrapper function return a Boolean, and leverage a ByRef parameter to return the result - a function like this returns False if the user cancels the prompt, True if the prompt was not cancelled, and outResult will be either 0 for a non-numeric input, or the input converted to a Double:

Public Function TryGetDoubleInput( _
    ByVal prompt As String, _
    ByVal title As String, _
    ByVal default As String, _
    ByRef outResult As Double) _
As Boolean
    Dim result As String
    result = VBA.Interaction.InputBox(prompt, title, default)
    TryGetDoubleInput = StrPtr(result) <> 0 'return false if cancelled
    If IsNumeric(result) Then outResult = CDbl(result)
End Function

Can be used like this:

Dim value As Double
If TryGetDoubleInput("Enter a numeric value:", "Prompt", "0.00", value) Then
    If value = 0 Then
        MsgBox "You entered either 0 or a non-numeric value"
    Else
        MsgBox "You entered " & CStr(value) ' note the irony
    End If
Else
    MsgBox "You cancelled the prompt"
End If

Now if you need to treat invalid values differently than 0 (i.e. if 0 is a legit input), consider throwing an error:

Public Function TryGetDoubleInput( _
    ByVal prompt As String, _
    ByVal title As String, _
    ByVal default As String, _
    ByRef outResult As Double) _
As Boolean
    Dim result As String
    result = VBA.Interaction.InputBox(prompt, title, default)
    If StrPtr(result) = 0 Then Exit Function 'return false if cancelled

    If IsNumeric(result) Then 
        outResult = CDbl(result)
        TryGetDoubleInput = True
    Else
        Err.Raise 555, "TryGetDoubleInput", "Non-numeric input is invalid."
    End If
End Function

And now you can use error handling to handle invalid inputs, and you can now tell a legit 0 from a cancelled inputbox from an arbitrary invalid input:

On Error GoTo ErrHandler
Dim value As Double
If TryGetDoubleInput("Enter a numeric value:", "Prompt", "0.00", value) Then
    MsgBox "You entered " & CStr(value) ' note the irony
Else
    MsgBox "You cancelled the prompt"
End If

Exit Sub
ErrHandler:
    MsgBox Err.Description ' "Non-numeric input is invalid."

I do wonder why nobody suggested `application.inputbox("What is the maximum nominal thickness?",Type:=1)`... — GSerg, Mar 07 '19 at 19:48
@GSerg `Application.InputBox` is tied to the Excel object model. `VBA.InputBox` works in any host. But yeah. — Mathieu Guindon, Mar 07 '19 at 19:49

Determining if Cancel Button pressed where InputBox variable declared as Double

3 Answers3