# Hex encoding and decoding of ASCII strings in VBA

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

• Are you Dim hexcharing in a For Next loop? – Raystafarian Sep 7 '16 at 20:38
• Yep, declaring close to usage. VBA only runs that statement once. – ThunderFrame Sep 7 '16 at 21:01

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")  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. • 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. – ThunderFrame Sep 7 '16 at 21:34 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.

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

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

• clicking on the page must have resulted in an inadvertent down vote from me that shouldn't have happened. My apologies. – PeterT Sep 7 '16 at 16:28
• Downvote offset. – FreeMan Sep 7 '16 at 17:08
• @petert if the answer gets edited you can remove your downvote... ;) – Raystafarian Sep 7 '16 at 20:34
• 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. – ThunderFrame Sep 7 '16 at 21:40
• FWIW I like HexEncode/HexDecode. It's not any worse than HtmlEncode/HtmlDecode seen in .net. – Mathieu Guindon Sep 7 '16 at 22:23

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