# IsHex Function

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

With help from this Mr. Excel thread. So where did &H come from? Are there more like it?

Posted in Uncategorized

## 10 thoughts on “IsHex Function”

1. Nope, no good. It fails on “00?.

Public Function IsHex(sInput As String) As Boolean

Dim lResult As Long

On Error Resume Next
lResult = CLng(“&H” & sInput)

IsHex = (Len(Replace(sInput, “0”, “”)) = 0) Or (lResult > 0)

End Function
2. Not as elegant, but more understandable perhaps:

Function IsHex(sInput As String) As Boolean
IsHex = Not (Application.IsError(Application.Hex2Dec(sInput)))
End Function

It won’t work with versions before Excel 2007.

3. dermotb says:

how about
IsHex = IsNumeric(“&H” & sInput)

4. Rick Rothstein (MVP - Excel) says:

Here is another IsHex function (a one-liner) to consider…

Function IsHex(S As String) As Boolean
IsHex = Len(S) > 0 And S Like Replace(String(Len(S), “X”), “X”, “[0-9A-Fa-f]”)
End Function
5. Rick Rothstein (MVP - Excel) says:

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.

6. Rick Rothstein (MVP - Excel) says:

@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).

7. Rick Rothstein (MVP - Excel) says:

@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 &).

8. 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.

9. Rick Rothstein (MVP - Excel) says:

@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.

10. AntoineLB says:

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

Posting code? Use <pre> tags for VBA and <code> tags for inline.