ExcelFunctions.net

Search Site:

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:

VBA OctThe 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.

' 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 ) hex3 = Hex( 1000 )' hex2 is now equal to the String "10". ' 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.

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 Stringhex1 = 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.

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