The VBA Hex Function

Hexadecimals and Octals in VBA

If you want to refer to Hexadecimals or Octals directly in VBA, this can be done by preceding a hexadecimal with &H and an octal with &O.

For example:

&H10=Hexadecimal 10  (=decimal 16)
&O10=Octal 10  (=decimal 8)

These Hexadecimal and Octal representations can be used in VBA functions that require numeric arguments.

Related Function:


The VBA Hex function converts a supplied number into hexadecimal notation and returns the result as a string.

The syntax of the function is:

Hex( Number )

Where the Number argument is the numeric value that you want to convert to hexadecimal.

Note that, if the supplied Number is a decimal, it is rounded to the nearest integer before being converted to hexadecimal.

VBA Hex Function Examples

Example 1 - Convert Decimals to Hexadecimal

' Convert three different decimals into hexadecimal notation.
Dim hex1 As String
Dim hex2 As String
Dim hex3 As String
hex1 = Hex( 15 )
' hex1 is now equal to the String "F".
hex2 = Hex( 16 )
' hex2 is now equal to the String "10".
hex3 = Hex( 1000 )
' hex3 is now equal to the String "3E8".

After running the above vba code the variables hex1, hex2 and hex3 are equal to the Strings "F", "10" and "3E8" respectively.

Example 2 - Convert an Octal to Hexadecimal

The VBA Hex function can also be used to convert octal values to hexadecimal, if the octal is preceded by &O.

This is illustrated in the example below, which uses the representation &O1750 for the octal number 1750.

' Convert the octal 1750 into hexadecimal notation.
Dim hex1 As String
hex1 = Hex( &O1750 )
' hex1 is now equal to the String "3E8".

The octal value 1750 is equal to the decimal value 1000. Therefore, after running the above vba code the variable hex1 is equal to the String "3E8" (the hexadecimal notation for the decimal value 1000).

Warning: Text representations of hexadecimals or octals (as returned by the Hex and Oct functions) are not recognised as hexadecimals or octals when supplied to VBA functions.

VBA Hex Function Error

If the Hex function is supplied with a text string that cannot be interpreted as a numeric value, it will return the error:

Run-time error '13': Type mismatch

VBA Run Time Error 13 Message Box