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?

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

Best wishes Harald


"Don G" wrote in message
...
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" wrote in message
...
-0.775 to -0-780 is down, isn't it?

Best wishes Harald


"Don G" wrote in message
...
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?

Sorry newsreader playing tricks while I doodle.


"Harald Staff" wrote in message
...
"Harald Staff" wrote in message
...
-0.775 to -0-780 is down, isn't it?

Best wishes Harald


"Don G" wrote in message
...
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?

On Sun, 15 Aug 2010 12:06:47 -0700 (PDT), Don G
wrote:

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?

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

On Aug 15, 12:14*pm, "Harald Staff" wrote:
-0.775 to -0-780 is down, isn't it?

Best wishes Harald

"Don G" wrote in message

...



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?- Hide quoted text -

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

On Aug 15, 1:44*pm, Ron Rosenfeld wrote:
On Sun, 15 Aug 2010 12:06:47 -0700 (PDT), Don G
wrote:

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?

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

On Sun, 15 Aug 2010 15:25:43 -0700 (PDT), Don G
wrote:

On Aug 15, 1:44*pm, Ron Rosenfeld wrote:
On Sun, 15 Aug 2010 12:06:47 -0700 (PDT), Don G
wrote:

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?

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

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

On Sun, 15 Aug 2010 15:25:43 -0700 (PDT), Don G
wrote:

On Aug 15, 1:44*pm, Ron Rosenfeld wrote:
On Sun, 15 Aug 2010 12:06:47 -0700 (PDT), Don G
wrote:

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?

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

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

On Aug 15, 6:05*pm, Ron Rosenfeld wrote:
On Sun, 15 Aug 2010 15:25:43 -0700 (PDT), Don G
wrote:





On Aug 15, 1:44*pm, Ron Rosenfeld wrote:
On Sun, 15 Aug 2010 12:06:47 -0700 (PDT), Don G
wrote:

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?

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

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

On Sun, 15 Aug 2010 19:25:53 -0700 (PDT), Don G
wrote:

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.

"Don G" wrote in message
...
On Aug 15, 12:14 pm, "Harald Staff" wrote:
-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