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
numHex

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.

StackExchange Code Review Q#140703, answer score: 5