0

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.

enter image description here

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

brb
  • 668
  • 7
  • 24
  • 1
    I can't reproduce it. I get `25.30364372` as output using your UDF code. – Pᴇʜ Jul 30 '19 at 10:29
  • Do you have event-triggered macros in your workbook that are doing some rounding on column E? – Ron Rosenfeld Jul 30 '19 at 10:32
  • Hi Ron. No, I do not. – brb Jul 30 '19 at 10:37
  • 1
    @brb What is the result of `Debug.Print [C6], [E6]` just to ensure there is no odd number format. – Pᴇʜ Jul 30 '19 at 11:13
  • Highlight the numbers in column D and copy. Highlight the numbers in column F and Paste Format. What happens? – freeflow Jul 30 '19 at 11:25
  • 2
    If your calculation involves any VBA variables with data type Currency then those can only have a maximum of 4 digits to the right of the decimal point - see [here](https://docs.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/currency-data-type) That would seem to match the behaviour that you are seeing – barrowc Jul 30 '19 at 11:35
  • Can you put a break in the UDF (on the `Exit Sub` line) and see what the value of `Wage` is? Is it simply a matter of formatting on the cell? –  Jul 30 '19 at 12:03
  • 1
    Thank you all help, particularly @barrowc - it is an issue with the currency format. I will write up the answer to help others, but if you wanted to write the answer or repaste mine, I will accept your version. – brb Jul 30 '19 at 23:59

1 Answers1

2

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://docs.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.

enter image description here

This can be verified using Debug.Print

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

enter image description here

brb
  • 668
  • 7
  • 24