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("&amp;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$("&amp;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 = "&amp;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" &amp; Hex$(asciiChars(char)), 2)
    Next char

    HexEncode = HexPrefix &amp; 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 &amp; 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
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 '" &amp; HexString &amp; "' is not in the expected format."
End If




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




  If Not IsNumeric(hexchar) Then
    OnHexDecodeError "Hex character '" &amp; hexchar &amp; "' 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 &lt;&gt; 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.