I’ve just added some VBA functions to the DBL to HEX conversion library. There are two important new functions: xNum2DblHex as well as HEX2DBL.

The VBA function xNum2DblHex yields the hex representation of the very IEEE754DP floating-point number nearest to an extended number. The rounding convention used in this function is to round up always if the 54th mantissa bit, which is the most significant bit not being encoded in the IEEE754DP standard, would take a value of 1. Extended numbers do not only arise from multi-precision tools like xNumbers, they also occur when importing numbers with 18 significant digits from a FORTRAN output file into a spreadsheet, e.g. As I originally wrote the DBL2HEX function in FORTRAN (this language has a standard ‘equivalence’ statement), it is now possible to check whether Fortran DP quantities (declared by real*8 statements, e.g.) have the same binary representation after being imported into XLS as they originally had in FORTRAN. This is an important prerequisite for comparing the performance of an identical algorithm that is executed in different environments like XLS, VBA, PY, FOR, … in a spreadsheet environment.

The VBA function HEX2DBL converts any valid hex representation of an IEEE754DP number into a VBA double. In order to accomplish that task it harnesses the same trick as its inverse function DBL2HEX does, i.e., calling the VBA Win32/64 API function RtlMoveMemory.

You may download the complete VBA conversion library if you obey its GPL V3 license.

One of the next posts will be concerned with answering the question of how to ‘optimally’ calculate a quantity like y = 1 – x*x when x is close to 1.