Unexpected String Results

Question

I have the following code to check values entered into two input boxes, if both values are zero then the MsgBox should display "Stop!" (I will change this later to exiting the sub but I am using a MsgBox for testing)

From testing I've seen these results:

A zero in both strings produces the expected message box.
A non zero in the first string followed by any non zero value in the second string does nothing (as expected).
A zero in the first string followed by a second string value equal to or greater than 10 produces the message box (unexpected).

I've also noticed that if the second string is 6-9 it is displayed as x.00000000000001%. I think this is a floating point issue and could be related? This behaviour occurs without the IF... InStr function too.

Option Explicit
Sub Models()
    Dim MinPer As String, MaxPer As String, Frmula As String
    Dim Data As Worksheet, Results As Worksheet
    Set Data = Sheets("Data")
    Set Results = Sheets("Results")

    Application.ScreenUpdating = False

    MinPer = 1 - InputBox("Enter Minimum Threshold Percentage, do not include the % symbol", _
    "Minimum?") / 100
    MaxPer = 1 + InputBox("Enter Maximum Threshold Percentage, do not include the % symbol", _
    "Maximum?") / 100


    If (InStr(MinPer, "0") = 0) And (InStr(MaxPer, "0") = 0) Then
    MsgBox "STOP!"
    End If

    ' Remainder of code...

This is the most interesting problem I've come across so far in VBA and welcome any discussion about it.

Edit: I use this code to display on screen the paramaters for the end-user to see. Hence how I noticed the .00000000001% issue:

    .Range("D2").Value = "Min is " & 100 - MinPer * 100 & "%"
    .Range("D3").Value = "Max is " & MaxPer * 100 - 100 & "%"

what are you trying to test for? also, converting a calculation to a string is going to produce some interesting results too — SeanC, Jul 10 '12 at 15:47

Siddharth Rout · Accepted Answer · 2012-07-10T18:17:34.747

4

Two things

1) Declare MinPer, MaxPer as Long or a Double and not a String as you are storing outputs from a calculation

2) Don't directly use the InputBox in the calculations. Store them in a variable and then if the input is valid then use them in the calculation

Dim MinPer As Double, MaxPer As Double, Frmula As String
Dim Data As Worksheet, Results As Worksheet
Dim n1 As Long, n2 As Long

Set Data = Sheets("Data")
Set Results = Sheets("Results")

Application.ScreenUpdating = False

On Error Resume Next
n1 = Application.InputBox(Prompt:="Enter Minimum Threshold Percentage, do not include the % symbol", _
Title:="Minimum?", Type:=1)
On Error GoTo 0

If n1 = False Then
    MsgBox "User cancelled"
    Exit Sub
End If

On Error Resume Next
n2 = Application.InputBox(Prompt:="Enter Maximum Threshold Percentage, do not include the % symbol", _
Title:="Maximum?", Type:=1)
On Error GoTo 0

If n2 = False Then
    MsgBox "User cancelled"
    Exit Sub
End If

If n1 = 0 And n2 = 0 Then
    MsgBox "STOP!"
End If

MinPer = 1 - (Val(n1) / 100)
MaxPer = 1 + (Val(n2) / 100)

edited Jul 10 '12 at 18:17

answered Jul 10 '12 at 15:48

Siddharth Rout

147,039
17
206
250

Entering non numeric values is producing type mismatch errors instead of the `not a valid input` msgbox. The value of MinPer and MaxPer are not as expected, I'm sure the arithmatic is right but not getting the correct values.. e.g N1 = 5 and N2 = 6 should produce MinPer = 0.95 and MaxPer = 1.06 but both equal 1. – Alistair Weir Jul 10 '12 at 16:03
Just leaving office, will come back to this when home. – Alistair Weir Jul 10 '12 at 16:06
Updated the post. Please test it now. – Siddharth Rout Jul 10 '12 at 16:11
Still producing MinPer and MaxPer = 1. Getting correct msgbox error now for non numeric inputs but no stop msgbox for 0 inputs. – Alistair Weir Jul 10 '12 at 17:38
Input Box for Min = 5, Input Box for Max = 6 produces 1. Just tried inputmin=50, inputmax=60 got Minper = 0 Maxper = 2 – Alistair Weir Jul 10 '12 at 17:40
What exactly do you want to do? Trap if user enters 0 in inputboxes? – Siddharth Rout Jul 10 '12 at 17:42
Do we need additional parenthesis for Min/Maxper? – Alistair Weir Jul 10 '12 at 17:42
Yes if double zero entered then Ill display a msgbox and exit sub – Alistair Weir Jul 10 '12 at 17:43
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/13686/discussion-between-siddharth-rout-and-alistair-weir) – Siddharth Rout Jul 10 '12 at 17:43
1

Use `Application.InputBox`, it has a `Type` parameter that lets you limit the input to numbers. You can skip the `IsNumeric` check, however you would need to check if the return value is `False` (user cancels InputBox) – JimmyPena Jul 10 '12 at 17:44
@JimmyPena: + 1 nice Suggestion. Incorporated in the code. :) – Siddharth Rout Jul 10 '12 at 18:17
@SiddharthRout My bad, the return value is actually zero, help docs say it returns `False` but testing shows otherwise. – JimmyPena Jul 10 '12 at 18:21
@JimmyPena: It returns false when user presses cancel. I just tested it. Which is same as 0 :) So both will work. – Siddharth Rout Jul 10 '12 at 18:24
@SiddharthRout Silly me, I just realized I was using a Long variable to test it. `CLng(False) = 0`! – JimmyPena Jul 10 '12 at 18:25
Yes it returns false when user presses cancel but also returns false when user enters zero. The user should be able to enter zeros. – Alistair Weir Jul 10 '12 at 19:43

score 0 · Answer 2 · answered Jul 10 '12 at 15:48

This is because the number "10" has a "0" in the string (second character) so both evaluate to true.

Try this instead:

If (MinPer = "0") And (MaxPer = "0") Then
    MsgBox "STOP!"
End If

For additional control save the user input (MinPer , MaxPer) and THEN text them for validity before performing nay mathematical operations on them.

score 0 · Answer 3 · edited May 23 '17 at 11:56

InStr(MinPer, "0") is just checking to see whether the string contains a zero character.

You need to convert the string value to an integer. Use the IsNumeric and CInt functions to do that. See this URL:

vba convert string to int if string is a number

Dim minPerINT as Integer
Dim maxPerINT as Integer

If IsNumeric(minPer) Then
    minPerINT = CInt(minPer)
Else
    minPerINT = 0
End If
If IsNumeric(maxPer) Then
    maxPerINT = CInt(maxPer)
Else
    maxPerINT = 0
End If

If minPerINT = 0 and maxPerINT=0 Then
    MsgBox "STOP!"
End If

Depending on what data can be entered It may also be a good idea to check if the length of the data is zero using the len() function.

Unexpected String Results

3 Answers3