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

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")

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.

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.

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.

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