Hex encoding and decoding of ASCII strings in VBA

Question

I needed to convert some Ascii text to binary in Hex format 0x00FF....

I've written an EncodeHex and a DecodeHex function to do the conversion.

I've avoided using concatenation in favour of performance.

I'm assuming that assigning the results of CByte("&h80") to a byte array, and then using StrConv to convert the array to a Unicode string, is more efficient than assigning the results of Chr$("&h80") to a string array, and then using Join to concatenate the strings.

I need to use this in Excel, so I'm using CVErr(xlErrValue) to return errors, but this could maybe benefit from being more generic.

Sample usage

?HexEncode("FooBar")
0x466F6F426172
?HexDecode("0x466F6F426172")
FooBar

VBA Code

Option Explicit
Const HEX_STRING_PREFIX As String = "0x"
Const VBA_HEX_PREFIX As String = "&h"
Public Function HexEncode(AsciiText As String, Optional HexPrefix As String = HEX_STRING_PREFIX) As String
 If AsciiText = vbNullString Then
 HexEncode = AsciiText
 Else
 Dim asciiChars() As Byte
 asciiChars = StrConv(AsciiText, vbFromUnicode)
 ReDim hexChars(LBound(asciiChars) To UBound(asciiChars)) As String
 Dim char As Long
 For char = LBound(asciiChars) To UBound(asciiChars)
 hexChars(char) = Right$("00" & Hex$(asciiChars(char)), 2)
 Next char
 HexEncode = HexPrefix & Join(hexChars, "")
 End If
End Function
Public Function HexDecode(HexString As String, Optional HexPrefix As String = HEX_STRING_PREFIX)
 'Check if there's anything to decode
 If HexString = vbNullString Then
 HexDecode = vbNullString
 Exit Function
 Else
 If Not StrComp(Left$(HexString, Len(HexPrefix)), HexPrefix, vbTextCompare) = 0 Then
 'Unexpected string format
 GoTo DecodeError
 End If
 Dim hexRaw As String
 hexRaw = Mid$(HexString, 1 + Len(HexPrefix))
 'Check if the string is valid for decoding
 If Len(hexRaw) Mod 2 = 1 Then
 GoTo DecodeError
 End If
 Dim numHexChars As Long
 numHexChars = Len(hexRaw) / 2
 ReDim hexChars(0 To numHexChars - 1) As Byte
 Dim char As Long
 For char = 0 To numHexChars - 1
 Dim hexchar As String
 hexchar = VBA_HEX_PREFIX & Mid$(hexRaw, 1 + char * 2, 2)
 'Check if the hex-pair is actually hex
 If Not IsNumeric(hexchar) Then
 GoTo DecodeError
 End If
 hexChars(char) = CByte(hexchar)
 Next char
 'Return the concatenated bytes as a string
 HexDecode = StrConv(hexChars, vbUnicode)
 End If
SafeExit:
 Exit Function
DecodeError:
 HexDecode = CVErr(xlErrValue)
End Function

• Raystafarian: Are you Dim hexcharing in a For Next loop?
• ThunderFrame: Yep, declaring close to usage. VBA only runs that statement once.

Answer 1

I think you have an opportunity for using Application.Caller in that error-handling subroutine, and make the function behave differently when it's used as a UDF vs. when it's called from VBA code.

Instead of GoTo-jumping when there's an invalid argument to early-return an Excel error value that's only really useful when the function is used as a UDF (and confusing when it's called from VBA code), you could be raising an actual meaningful error (which would still be jumping to the DecodeError label).

First, because we're raising an error in several places, I'd make a little utility procedure:

Private Sub OnHexDecodeError(ByVal message As String)
 Err.Raise 5, "HexDecode", message
End Sub

If Not StrComp(Left$(HexString, Len(HexPrefix)), HexPrefix, vbTextCompare) = 0 Then
 OnHexDecodeError "Parameter value '" & HexString & "' is not in the expected format."
End If

If Len(hexRaw) Mod 2 = 1 Then
 OnHexDecodeError "Parameter value '" & HexString & "' is invalid."
End If

 If Not IsNumeric(hexchar) Then
 OnHexDecodeError "Hex character '" & hexchar & "' is not a valid hexadecimal digit."
 End If

And then the error-handling subroutine could do this:

DecodeError:
 If TypeName(Application.Caller) = "Range" Then
 'function is used as a UDF
 HexDecode = CVErr(xlErrValue)
 Else
 'function is called by other VBA code
 Err.Raise Err.Number 'rethrow
 End If

That way the calling code can handle a runtime error 5 (aka invalid procedure call or argument) with a useful description:

?HexCode("test")

Parameter value 'test' is not in the expected format.

And now you have a function that's just as friendly to use as a UDF as it is in plain VBA code.

---

This doesn't strike me as intuitive:

If Len(hexRaw) Mod 2 = 1 Then

"Is value a multiple of X" is usually written as:

If value Mod x <> 0 Then

That way you're not assuming what the remainder might be - of course here it's either 1 or 0, but the point is that the way the comparison is written in foo Mod bar {comparison} never has to change, regardless of what the value of bar is.

• ThunderFrame: Nice idea on making it usable as a UDF or VBA call. TBH, the DecodeError label was there to allow me to change the behavior to support one or the other, but not both. If I'm making it more general, I should probably make it work in all hosts, but to get Application.Caller to compile in other hosts, I'll need to late-bind the call to Caller.

Answer 2

I'll second @Zak 's suggestion on clearer function names, although my naming preference is for AsciiToHexString and HexStringToAscii.

A couple other things I noticed:

I prefer variables to begin with lowercase letters and a Sub or Function to start with an uppercase letter. I've seen this convention used consistently on SO, and (for me) it makes for clearer reading.

It's a personal preference thing (IMHO) on how to handle function returns. One possible alternative would be to set the function return value for the failure case at the beginning of the function. Successfully completing the function will change the return value to an expected result. Any potential processing errors along the way will guarantee return of the failure case.

Public Function AsciiToHexString(ByVal asciiText As String, _
 Optional ByVal hexPrefix As String = HEX_STRING_PREFIX) As String
 AsciiToHexString = asciiText 'default failure return value
 If Not (asciiText = vbNullString) Then
 Dim asciiChars() As Byte
 asciiChars = StrConv(asciiText, vbFromUnicode)
 ReDim hexChars(LBound(asciiChars) To UBound(asciiChars)) As String
 Dim char As Long
 For char = LBound(asciiChars) To UBound(asciiChars)
 hexChars(char) = Right$("00" & Hex$(asciiChars(char)), 2)
 Next char
 AsciiToHexString = hexPrefix & Join(hexChars, "")
 End If
End Function

In the other function, your error handling is not consistent. At the very least you should add a GoTo SafeExit at the end of your DecodeError: section. This is good practice to force a single return point from your function. Alternatively, you can skip that error handling altogether with If statements:

Public Function HexStringToAscii(ByVal hexString As String, _
 Optional ByVal hexPrefix As String = HEX_STRING_PREFIX) As Variant
 HexStringToAscii = CVErr(xlErrValue) 'default failure return value
 If Left$(hexString, Len(hexPrefix)) = hexPrefix Then
 Dim hexRaw As String
 hexRaw = Right$(hexString, Len(hexString) - Len(hexPrefix))
 '--- a valid hex string must be an even number of chars
 If Len(hexRaw) Mod 2 = 0 Then
 Dim numAsciiChars As Integer
 numAsciiChars = Len(hexRaw) / 2
 Dim returnString As String
 Dim i As Integer
 For i = 1 To numAsciiChars
 returnString = returnString & Chr(Val(VBA_HEX_PREFIX & Mid$(hexRaw, (i * 2) - 1, 2)))
 Next i
 HexStringToAscii = returnString
 End If
 End If
End Function

Your second function should explicitly state its return value. In your case, it should return Variant since the value could be CVErr or a valid string.

• Mathieu Guindon: That would be Resume SafeExit, not GoTo ;-)
• PeterT: Heh... that's what I get for typing that from memory :)
• Raystafarian: Standard VBA naming conventions have camelCase for local variables and PascalCase for other variables and names. So yes.

Answer 3

Naming

HexEncode and HexDecode aren't bad, but they could be more intuitive.

I see them, I know that they're working with hex encoding, but I'm still going to have to track these functions down to find out exactly how they're structured.

IMO, these would be even better function names:

StringFromHex()
HexFromString()

Short, Simple, Descriptive and completely unambiguous.
It means we can then write code like this:

hexString = HexFromString(someString)
someString = StringFromHex(hexString)

Which is just so much easier to read and understand.

• PeterT: clicking on the page must have resulted in an inadvertent down vote from me that shouldn't have happened. My apologies.
• FreeMan: Downvote offset.
• Raystafarian: @petert if the answer gets edited you can remove your downvote... ;)
• ThunderFrame: Oddly enough, the function names started off as AsciiStringToHexString and HexStringToAsciiString, but I didn't want to lock myself into Ascii, so went for the more generic approach. Right now Ascii works for my needs, and I'm limited to a maximum of 128 encoded bytes (so Ascii/single-byte) is appealing), but supporting multi-byte character encodings would be more generic. Maybe the code-page could be an argument.
• Mathieu Guindon: FWIW I like HexEncode/HexDecode. It's not any worse than HtmlEncode/HtmlDecode seen in .net.

Answer 4

Hex to String - Preserving the leading 0

Hmm, I think I can use a bit trick to make the Hex conversion more efficient.

Right$("00" & Hex$(61), 2)

becomes

Right$(Hex$(256 Or char),2)

Options on Hex Format

While I have implemented a hexPrefix to allow for various hex syntax, I'm forcing the hex string to have adjacent characters, but some consumers of the function might want some more flexibility. If I added an optional parameter Optional HexCharDelimiter As String = vbNullString, then I could do things like:

HexEncode("FooBar")
0x466F6F426172
HexEncode("FooBar",vbNullString," ")
46 6F 6F 42 61 72

Providing flexibility in the return type

But what about return-type flexibility and/or longer strings that need encoding? The function forces a string on the consumer of the function, but returning an array of hex encoded characters might be more useful, and if a user does want a string, then a string-returning function just joins the results of the array returning function.

That way I'd have something like:

Public Function AsciiStringAsHexStringArray(AsciiText As String) As String()
 '....
 AsciiStringAsHexStringArray = hexchars
End Function
Public Function AsciiStringAsHexString(AsciiText As String, Optional hexPrefix As String, Optional charDelimiter As String = vbNullString) As String
 '....
 chars = AsciiStringAsHexStringArray(AsciiText)
 If IsArray(chars) Then
 'Check the array is initialized
 If UBound(chars) >= LBound(chars)
 AsciiStringAsHexString = hexPrefix & Join(hexchars,charDelimiter)
 Else
 '...
 End If
 End If
End Function

That would allow me to use the array to build multi-line results. For example, like a hex editor view:

54 68 65 20 6D 65 61 6E 69 6E 67 20 6F 66 20 6C
69 66 65 20 69 73 20 34 32