Precision issues between Excel + VBA with currency formats

Question

I have a user defined function (UDF) which takes two inputs. Both inputs have been declared as type Double. However, while the input has a large number of decimal places, once passed to the UDF, the number of decimal places gets truncated.

Curiously, I cannot seem to reproduce this behaviour in a clean workbook (ie, I wonder if there is something going on with this particular workbook...).

The (simplied) UDF is below.

Function Wage(HourlyWage As Double, HoursWorked As Double) As Double

    Wage = HourlyWage

End Function

as well as a screenshot of what is happening.

The formula bar shows the UDF call. It is taking columns C and D as inputs. HourlyWage is 25.30364372+ in column C, but once passed to the UDF Wage, the HourlyWage is truncated to 25.30360000 despite being declared as type Double.

I understand general points about floating point precision, but this surely applies equally to the input in column C as it does to the UDF (indeed, column C is in actual fact the output of another UDF which has been declared as type Double).

Why can't decimal numbers be represented exactly in binary?

Why are floating point numbers inaccurate?

Additional information about the EXCEL workbook.

• I am using Windows 7 Professional and Microsoft Office 365 ProPlus 64-bit version.
• The workbook is an .xlsb format. I have also saved as .xlsm and it does not fix the issue.
• I cannot reproduce in a clean workbook.
• The workbook contains a large number of interrelated user defined functions (maybe 40)
• The workbook contains 11 sheets. 5 of these are datasheets with a number of parameters, but I wouldn't have said large in the grand scheme of things (around 100 rows and 30 columns). 3 sheets have a largeish number of UDF and calculations (again, 100 rows by maybe 40-50 columns)
• I wouldn't have said the workbook is large. It is 369Kb (.xlsb) and 525KB (.xlsm)
• However, the workbook is taking an unexpectedly long time to recalculated when input parameters are changed (say around 10 seconds which I find surprising as it doesn't seem a lot of calculations in the grand scheme of things)

Any ideas on how to fix this? The loss of precision is causing me issues...

Answer 1

This answer is based on comments from barrowc and Peh above. Thank you both for your help.

Currency data types have a maximum of 4 decimal points (https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/currency-data-type).

Column C has been formatted with a custom currency format. When fed to the VBA UDF function, decimal points beyond the 4th decimal place are truncated despite the variable being declared as type Double inside the UDF.

Changing the number format solves the problem as per screenshot below.

This can be verified using Debug.Print

Sub brb()
    Debug.Print [C6], [E6]
    Debug.Print [C8], [E8]
End Sub