2

I am trying to use an InputBox function to be able to have a user input data. However, the data mainly consists of decimal values. When I am testing my program and I go to subtract two values, both entered from InputBox, it does not perform a mathematical operation. 

thckmax = InputBox("What is the maximum nominal thickness?", "Enter Max Nominal Thickness Measurement")
thckmin = InputBox("What is the minimum nominal thickness?", "Enter Min Nominal Thickness Measurement")
thcknom = (thckmax + thckmin)

If I were to enter .05 and .04 respectively, it displays the preceding code as:

thcknom is .05.04

I am trying to perform the mathematical operation, addition, to the two variables; however, it is just "adding" the two strings side-by-side. How do I fix this issue?

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
A. Jones
  • 39
  • 8
  • 2
    Did you declared your variables as Double or Single? if you didn't declare them this could happen. Thought the user should input 0.05 and not .05 – Damian Mar 06 '19 at 16:19
  • @damian I had them declared as variants which should have allowed me to enter any data type, but I could see how it may have become confused especially if there was no preceding zero to the decimals. – A. Jones Mar 06 '19 at 16:31
  • I'm assuming with `Variant`, when using `InputBox()`, it will assume then that it's a `String`, which is why the `+` simply concatenates the two. Also, some surprisingly good discussion came from this question, nice job! Edit: Yep, `Dim tst as Variant // tst = InputBox("What?") // debug.print TypeName(tst)` returns `String` for an input of `Hi`, `3`, and `0.03`. – BruceWayne Mar 06 '19 at 18:33

2 Answers2

2

You need to declare the variables as either Double or Single, depending on the expected input:

Sub t()
Dim thckmax As Double, thckmin As Double, thcknom As Double
thckmax = InputBox("What is the maximum nominal thickness?", "Enter Max Nominal Thickness Measurement")
thckmin = InputBox("What is the minimum nominal thickness?", "Enter Min Nominal Thickness Measurement")
thcknom = (thckmax + thckmin)
Debug.Print thcknom
End Sub

See VBA's data types for more info.

Also, I suggest adding Option Explicit to the very top of your modules, which forces you to declare all variables.

BruceWayne
  • 22,923
  • 15
  • 65
  • 110
  • Do you add the Option Explicit before the Dim section? Sorry very new to VBA. TIA. – A. Jones Mar 06 '19 at 16:26
  • @A.Jones no worries, you add it literally at the very very top. Before you even put `Sub whatever()`. It's the first line in a module. – BruceWayne Mar 06 '19 at 16:32
  • Thank you! I will be using `Option Explicit` from now on in any of my codes. – A. Jones Mar 06 '19 at 16:37
  • 2
    @A.Jones - It's a good habit to get in to. FYI, you can have VBA automtically add it, see [this page](https://www.excel-easy.com/vba/examples/option-explicit.html). It also helps prevent any typos, so if later in your code you accidentally type `thickmax`, it'll alert you that it's not declared, instead of running the code, then throwing an error when it gets to that line. – BruceWayne Mar 06 '19 at 16:42
  • 2
    the problem with *not* specifying option explicit is that it's *not* going to throw an error on *that line*, rather somewhere else completely unrelated (if at all) - because an implicit `Variant` can hold ilterally anything, so code happily keeps running with invalid data, and the bug can pop up very far from its source, which makes the problem much harder to debug than it should be -- whereas with Option Explicit, instead of failing somewhere at runtime, it fails at compile-time, before a single statement gets to even begin to run. Failing early is always better! (CC @A.Jones) – Mathieu Guindon Mar 06 '19 at 16:50
  • 1
    Not to mention, how frustrating it can be to spend 3 hours scratching your head, only to realize "oh, it was a typo" =) – Mathieu Guindon Mar 06 '19 at 16:52
  • @MathieuGuindon - Haha, that's the exact scenario I found myself in when I then discovered `Option Explicit`. Using `F8` to walk through a bunch of lines was getting annoying... It's a huge timesaver at the very least. – BruceWayne Mar 06 '19 at 16:55
  • @A.Jones - Final comment from me - there's [this thread, "What do Option Strict and Option Explicit do?"](https://stackoverflow.com/questions/2454552/what-do-option-strict-and-option-explicit-do) too which has much more detail. (It's for .Net, but the Option Explicit info applies to VBA...not sure on the `Option Strict`) – BruceWayne Mar 06 '19 at 17:02
  • 1
    `Option Strict` is only in VB.NET; it prevents a number of narrowing implicit type conversions that VBA merrily allows (e.g. from `Double` to `Long`) -- I'll see myself out as well, cheers! – Mathieu Guindon Mar 06 '19 at 17:03
  • @Mathieu Guindon Say I wanted to check if the user did not enter a value or hit the cancel button. What value does the input box return in those cases, zero? I know it depends on if the variable is declared as a double or string/variant, but wasn't sure exactly what it returned. TIA. – A. Jones Mar 07 '19 at 12:36
  • 2
    @A.Jones If an empty value is legal input and you need to tell it from a cancellation, use `InputBox` from the VBA standard library (not Excel's), and the returned string will have a `StrPtr` value of 0 if cancelled. If you don't need to tell cancellation from empty input, then compare to the type's default initial value (""/0) – Mathieu Guindon Mar 07 '19 at 12:53
  • @Mathieu Guindon I tried using the default double value of 0, but it is giving me a run time 13 type mismatch error. Here is my current code: `thckmax = InputBox("What is the maximum nominal thickness?", "Enter Max Nominal Thickness Measurement") If thckmax = 0 Then GoTo Line3 End If` – A. Jones Mar 07 '19 at 13:01
  • 4
    @A.Jones you're trying to coerce the return value into a double, but the function returns a string that may or may not implicitly convert to a double; the type mismatch is on assignment, not on comparison. Wrap the inputbox into its own double-returning function to abstract away the plumbing of receiving a string, validating whether it's numeric, attempting cdbl conversion, ...also... I'll pretend I didn't see that `GoTo` =) – Mathieu Guindon Mar 07 '19 at 13:18
1

InputBox allows you to define the input type. Since you did not specify one, the default is used, which is TEXT.
Something like 

thckmax = Application.InputBox(Prompt:="What is the maximum nominal thickness?", Type:=1)

guarantees you get a number, eventually preventing decimal symbol confusion.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • 2
    Except OP is using `InputBox`, which resolves to the `VBA.Interaction.InputBox` function, which isn't `Excel.Application.InputBox`. If you want `Application.InputBox`, it must be explicitly qualified. – Mathieu Guindon Mar 06 '19 at 16:32