RoundDown function not working in code

[Don G]

I'm using Excel 2003 VBA. In the immediate window I enter

? application.rounddown(-0.775,2) and it gives the result -0.77

In the following code

'Deal with SHEETCREDIT
SheetCredit = CashIn - CashDue
'dealing with negative sheetcredit
If SheetCredit < 0 Then SheetCredit =
Application.RoundDown(SheetCredit, 2)
'display this on the form
SheetsForm.DriverCreditBox.Value = Format(SheetCredit, "currency")

SheetCredit is -0.775 and the result is -0.78 Any idea why?

 

[Harald Staff]

-0.775 to -0-780 is down, isn't it?

Best wishes Harald

 

[Harald Staff]

-0.775 to -0-780 is down, isn't it?

Best wishes Harald

 

[Harald Staff]

Sorry newsreader playing tricks while I doodle.

 

[Don G]

-0.775 to -0-780 is down, isn't it?

Best wishes Harald










- Show quoted text -

No it isn't Harald, down is towards zero. But anyhow my concern is
why the same function on the same numbers give different results.
Thanks for thinking about it.

 

[Don G]

I cannot reproduce your results.  In the immediate window (MS VB 6.5):

?FORMAT(application.RoundDown(-0.775,2),"currency")
($0.77)

What are the actual values for CashIn and CashDue

Ron, in the immediate window I get the same results you do, which is
what I expect and want. My concern is that running from code to put
values onto a user form the result is -0.78 not what the immediate
window gives.
In my procedure the value of CashIn is 53.4 and CashDue is 54.175 both
are declared as currency.

I appreciate your thoughts on my problem, Thanks

 

[Ron Rosenfeld]

Ron, in the immediate window I get the same results you do, which is
what I expect and want. My concern is that running from code to put
values onto a user form the result is -0.78 not what the immediate
window gives.
In my procedure the value of CashIn is 53.4 and CashDue is 54.175 both
are declared as currency.

I appreciate your thoughts on my problem, Thanks

Is SheetCredit also declared as Currency?

I'm not sure exactly where the problem lies or why the following seems
to apply. But RoundDown is technically NOT a member of the
Application Class. Rather it is a member of the WorksheetFunction
Class.

Now I know that worksheetfunctions frequently can be called as a
member of the application class, and that seems to work. But
apparently not in this case.

If you call RoundDown as a member of the Worksheetfunction class, your
formula seems to work.

It also seems to work if you declare SheetCredit as Double; but I
would use the WorksheetFunction.RoundDown instead.

e.g:

====================================
Option Explicit
Sub foo()
Const CashIn As Currency = 53.4
Const CashDue As Currency = 54.175
Dim SheetCredit As Currency

'Deal with SHEETCREDIT
SheetCredit = CashIn - CashDue
'dealing with negative sheetcredit
Debug.Print "SheetCredit not Rounded", SheetCredit
If SheetCredit < 0 Then SheetCredit =
WorksheetFunction.RoundDown(SheetCredit, 2)
'display this on the form
' SheetsForm.DriverCreditBox.Value = Format(SheetCredit,
"currency")
Debug.Print "SheetCredit", SheetCredit
End Sub
==================================

 

[Ron Rosenfeld]

Ron, in the immediate window I get the same results you do, which is
what I expect and want. My concern is that running from code to put
values onto a user form the result is -0.78 not what the immediate
window gives.
In my procedure the value of CashIn is 53.4 and CashDue is 54.175 both
are declared as currency.

I appreciate your thoughts on my problem, Thanks

I've also posted a message in a few places to see if others can
explain this issue.

 

[Don G]

Is SheetCredit also declared as Currency?

I'm not sure exactly where the problem lies or why the following seems
to apply.  But RoundDown is technically NOT a member of the
Application Class.  Rather it is a member of the WorksheetFunction
Class.

Now I know that worksheetfunctions frequently can be called as a
member of the application class, and that seems to work.  But
apparently not in this case.

If you call RoundDown as a member of the Worksheetfunction class, your
formula seems to work.

It also seems to work if you declare SheetCredit as Double; but I
would use the WorksheetFunction.RoundDown instead.

e.g:

====================================
Option Explicit
Sub foo()
Const CashIn As Currency = 53.4
Const CashDue As Currency = 54.175
Dim SheetCredit As Currency

'Deal with SHEETCREDIT
    SheetCredit = CashIn - CashDue
    'dealing with negative sheetcredit
    Debug.Print "SheetCredit not Rounded", SheetCredit
    If SheetCredit < 0 Then SheetCredit =
WorksheetFunction.RoundDown(SheetCredit, 2)
    'display this on the form
   ' SheetsForm.DriverCreditBox.Value = Format(SheetCredit,
"currency")
   Debug.Print "SheetCredit", SheetCredit
End Sub
==================================- Hide quoted text -

- Show quoted text -

Thanks so much Ron I really appreciate you taking on my problem and
finding the solution.

Many Thanks
Don G

 

[Ron Rosenfeld]

Thanks so much Ron I really appreciate you taking on my problem and
finding the solution.

Many Thanks
Don G

Well, it's an unusual problem. And I'd like to try to understand why
it is happening. Chip Pearson has written that there isn't any
difference between calling the worksheetfunction from the Application
Object, vs the WorksheetFunction class, except for error handling. But
there does seem to be a difference here. I've posted asking this
question in a few forums.

Thanks for the feedback.

 

[Harald Staff]

-0.775 to -0-780 is down, isn't it?

Best wishes Harald

No it isn't Harald, down is towards zero. But anyhow my concern is
why the same function on the same numbers give different results.
Thanks for thinking about it.

Sorry it was posted unintended.
The result of a calculation may be slightly off an exact decimal, like
0.774999989, so they're not necessaqrily the same numbers even if they
display the same.

Best wishes Harald