I have a need to verify some user input is hexadecimal (0-9 and A-F).
Public Function IsHex(sInput As String) As Boolean
Dim lResult As Long
On Error Resume Next
lResult = CLng(“&H” & sInput)
IsHex = sInput = “0” Or lResult > 0
End Function
Dim lResult As Long
On Error Resume Next
lResult = CLng(“&H” & sInput)
IsHex = sInput = “0” Or lResult > 0
End Function
With help from this Mr. Excel thread. So where did &H come from? Are there more like it?
Nope, no good. It fails on “00?.
Dim lResult As Long
On Error Resume Next
lResult = CLng(“&H” & sInput)
IsHex = (Len(Replace(sInput, “0”, “”)) = 0) Or (lResult > 0)
End Function
Not as elegant, but more understandable perhaps:
IsHex = Not (Application.IsError(Application.Hex2Dec(sInput)))
End Function
It won’t work with versions before Excel 2007.
how about
IsHex = IsNumeric(“&H” & sInput)
Here is another IsHex function (a one-liner) to consider…
IsHex = Len(S) > 0 And S Like Replace(String(Len(S), “X”), “X”, “[0-9A-Fa-f]”)
End Function
I meant to point out that the reason I posted the function that I did is because it has virtually no limit to the number of hex-digits the argument can contain. Well, of course there is a limit… approximately 2 billion hex-digits… but that is, for all practicality, a virtually unlimited maximum number of hex-digits.
@dermotb… That may be one of the few places where IsNumeric works as one would want it to. Interesting, though, is that IsNumeric will verify a string as being a hexidecimal number if it contains up to 16 hex-digts (“&H” & “FFFFFFFFFFFFFFFF” is the maximum hex number IsNumeric will return True for) whereas the largest hex number you can form using &H is only 8 hex-digits long (&HFFFFFFFF is apparently the largest hex number that VB will recognize).
@Dick,
I almost missed your ending question… yes, there is one other that I am aware of… &O or just a leading & by itself… this is an apparent throw back to ancient times as it will interpret the digits following it as Octal (only the digits zero through 7 may follow the &O or leading &).
I think that the dollar sign once denoted strings, but there’s bno support for it now.
Likewise the hash symbol ‘#’ has no value as a prefic – but it remains the delimiter around a date:
?
On a PC with the locale set to ‘United Kingdom’, our local currency sign ‘£’ does… Something. Try evaluating these expressions in the immediate pane and see what you get:
?£123
[returns an empty string]
?£123.456
0.456
?£123.000
0
?£123.456 + £23.55
0.456 0.55
So it seems to me that the Pound sign is some kind of type suffix – although it’s just as likely to be an undocumented operator.
@Nile
Interesting… the “£” symbol attached to the front of a floating point number does the same thing on my “US Locale” computer as well; namely, return the decimal portion of the number. However, it seems to only work when physically attached to the number… I can’t find anyway to concatenate that symbol onto a floating point number (using CDbl, Val, Evaluate, etc.) in order to force it to return just the decimal part of the number.
Same behaviour for uro symbol here. Tested with ¥en too.
?123.456 + 23.55
0.456 0.55
My intuition was 123 and 23 were both valid parameter names. First char is neither numeric nor reserved prefix / suffix. To test this, in immediate window
123=”poiuyt”
?123.456 + 23.55 gives poiuyt{Space}0.456{Space}{Space}0.55
123=”poiuyt”
23 = “mlkjhg”
?123.456 + 23.55 yields an error but
123=”poiuyt”
23 = “mlkjhg”
?123.456 & 23.55 gives poiuyt0.456mlkjhg{Space}0.55
It doesn’t work with $ as US dollar symbol is a reserved suffix for string parameter.
Same behaviour for any non reserved chars (ANSI & UNICODE) map upto &hFF ie
?{Any non reserved char}123.456 + {Any non reserved char}23.55
0.456 0.55