ExcelFunctions.net

Search Site:

Octals and Hexadecimals in VBA

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

For example:

&O10 | = | Octal 10 (=decimal 8) |

&H10 | = | Hexadecimal 10 (=decimal 16) |

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

Related Function:

VBA HexThe VBA Oct function converts a supplied number into octal notation and returns the result as a string.

The syntax of the function is:

Oct( Number )

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

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

' Convert three different decimals into octal notation. Dim oct1 As String Dim oct2 As String Dim oct3 As String oct1 = Oct( 8 ) ' oct1 is now equal to the String "10". oct2 = Oct( 10 ) oct3 = Oct( 1000 )' oct2 is now equal to the String "12". ' oct3 is now equal to the String "1750". |

After running the above vba code the variables oct1, oct2 and oct3 are equal to the Strings "10", "12" and "1750" respectively.

The VBA Oct function can also be used to convert hexadecimal values to octal notation, if the hexadecimal is preceded by **&H**.

This is illustrated in the example below, which uses the representation **&H3E8** for the hexadecimal number 3E8.

' Convert the hexadecimal 3E8 into octal notation. Dim oct1 As Stringoct1 = Oct( &H3E8 ) ' oct1 is now equal to the String "1750". |

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

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

If the Oct 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