2

I'm using the worksheetfunction.roundup function in my VBA code and it seems to be spitting out a bogus answer. Here is an example code that recreates the problem:

Debug.Print WorksheetFunction.RoundUp(0.091, 2), WorksheetFunction.RoundUp(0.091, 2) = 0.1

The result in the immediate window is "0.1 False"

I would expect the result to be "0.1 True".

When I use the same function in a workbook "=ROUNDUP(0.091,2)=0.1" instead of with VBA code, I get the "True" as the result. Is this a known issue? Is there a VBA workaround?

user2357112
  • 260,549
  • 28
  • 431
  • 505
ish_K
  • 21
  • 1
  • Interestingly, `RoundUp(0.091, 1) = 0.1` is `True`. – BigBen Apr 22 '20 at 19:58
  • Seems like an issue of precision (?) between VBA and the sheet function, i.e. `? WorksheetFunction.Roundup(0.091, 2) - .1` returns `-1.38777878078145E-17`. – BigBen Apr 22 '20 at 20:38
  • Sadly this is rather important for what I'm trying to achieve. I found a bit of a messy workaround that gives the desired results:WorksheetFunction.RoundUp(WorksheetFunction.RoundUp(0.091, 2), 2) – ish_K Apr 22 '20 at 22:05
  • @BigBen it seems that any value entered the first argument between 0.0901 and 0.099999 in WorksheetFunction.Roundup(0.091, 2) - .1 returns -1.38777878078145E-17. I could also work some sort of check in the code for this. – ish_K Apr 22 '20 at 22:11
  • Just to add: I suspect the first workaround to be very slow if the equation is used thousands of times. – ish_K Apr 22 '20 at 22:17
  • See the discussion of `.1` and precision [here](https://learn.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result) about two thirds of the way down. – BigBen Apr 22 '20 at 23:30
  • Also perhaps of interest: `? Application.Evaluate("ROUNDUP(0.091,2)=.1")` returns `True`. – BigBen Apr 22 '20 at 23:33
  • 1
    At least for your example, you could use the **Currency** data type: `CCur(WorksheetFunction.RoundUp(0.091, 2)) = 0.1`--> `True` – Ron Rosenfeld Apr 23 '20 at 09:57
  • Application.evaluate and currency both seem like viable workarounds. Thanks! – ish_K Apr 23 '20 at 13:35

0 Answers0