The VBA Oct Function

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:


The 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.

VBA Oct Function Examples

Example 1 - Convert Decimals 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 )
' oct2 is now equal to the String "12".
oct3 = Oct( 1000 )
' 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.

Example 2 - Convert a Hexadecimal to Octal

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 String
oct1 = 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.

VBA Oct Function Error

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

VBA Run Time Error 13 Message Box