Strange Results From A Prompt

[Cyberdude]

I was writing a prompter which essentially looks like:

Sub TestPrompt()
Dim Ans As Variant, Msg As String
Msg = "Input the range (letters only ... no rows)" & vbCr & _
          "Ex:   G:S"
Ans = Application.InputBox(Msg, "Test", Type:=6)   'A string or a logical value (2 + 4)
MsgBox "Ans = " & Ans
End Sub

When I input the value A:M, then everything works just fine. But when I started testing for errors, I input the range 1:2, and got a fraction (0.04395...). THAT was a surprise. Moreover, when I tested it with:
Application.ISNUMBER(UCase(Trim(Ans)))
I got FALSE. So I changed it to:
Application.ISNUMBER(CDbl(UCase(Trim(ANS))))
I got TRUE.
What's going on here?? Why did I get a fraction? Does it maybe think that 1:2 is a time value perhaps?

[MWE]

I was writing a prompter which essentially looks like:

Sub TestPrompt()
Dim Ans As Variant, Msg As String
Msg = "Input the range (letters only ... no rows)" & vbCr & _
          "Ex:   G:S"
Ans = Application.InputBox(Msg, "Test", Type:=6)   'A string or a logical value (2 + 4)
MsgBox "Ans = " & Ans
End Sub

When I input the value A:M, then everything works just fine. But when I started testing for errors, I input the range 1:2, and got a fraction (0.04395...). THAT was a surprise. Moreover, when I tested it with:
Application.ISNUMBER(UCase(Trim(Ans)))
I got FALSE. So I changed it to:
Application.ISNUMBER(CDbl(UCase(Trim(ANS))))
I got TRUE.
What's going on here?? Why did I get a fraction? Does it maybe think that 1:2 is a time value perhaps?

It appears that you are getting fractions of 24 hours. Note that if you enter 24: the value of Ans is 1.0 and if you enter 12: the value of Ans is 0.50 Similarly 1: yields 0.04166 which is 1/24.

Given those results, one would expect that 1:2 would be interpreted as 1 hr and 2 min or 1.0333 hours. And 1.0333/24 = 0.043055 which is what Ans is when 1:2 is entered.

Now why you got 0.04395 is a mystery.

If you want a REALLY interesting result, try something like -24:

InputBox does not normally interpret math or format symbols as anything other than characters. But in this case, the ":" is clearly being interpreted otherwise.

[Cyberdude]

Thanx for the reply, MWE. After I made the post it occurred to me that it has to be interpreting 1:2 as time of some sort. Your discussion just confirms it. Strange . . . very strange.

[geekgirlau]

Unless you really enjoy checking for all the possible "interesting" user entries, why not create a small user form with two combo boxes each listing "A" to "IV"?

[Cyberdude]

geekgirlau, no I don't enjoy checking for all the possible wrong entries a user can enter, but I do my error checking mostly in another macro that is called. This is the one test I'm doing in the "mother" macro. I find that creating a userform is just too time consuming unless I REALLY have an application that requires it. In fact I haven't created a single userform since I started using Win XP. In Win 98 SE it was my experience that userforms used a LOT of resources in addition to being a pain to create. So I usually don't create them.