Code Not Recognizing Decimal

[Ron]

Hello all,

Anyone have any ideas why this code does not recognize the decimal.
I'm trying to test a column to make sure it equals zero. However for
some reason this code does not recognize the trailing decimals. When
I change IF SUMRNG <> 0 then to IF SUMRNG <> 0.00 VBA alters it to
0#. If the column is out of balance .49 cents it does not activate
the MSGBOX but if it's out .50 cents then the MSGBOX is activated.
Appreciate your assistance, Ron

With Selection
SumRng = Application.WorksheetFunction.Sum(Selection)
If SumRng <> 0 Then
MsgBox "Out of Balance, Please review and make the necessary
corrections. Balance should equal ZERO. " & SumRng

End If
End With

 

[Peter T]

Did you declare SumRng As Long. Try -
Dim SumRng as Double

The # after a value tells the compiler to treat it as a Double, rather than
as an Integer in the case of a non decimal.

Regards,
Peter T

 

[Ron]

Hi Peter, You were spot on with the SumRng as Double. But I'm having
a little problem getting my code to work properly. For some reason
there are trailing decimals that I am told are binary values. How do
I deal with them? I've tried Rounding with no results. My goal is to
have the column equal Zero or, at least within a penny, but I'm
getting this instead -0.00000000006984990. After changing SumRng to
double I get something like this in the MSGBOX-6.984990. I think my
problem is the binary values. Thanks for your assistance, Ron

 

[JoeU2004]

If the column is out of balance .49 cents it does not activate
the MSGBOX but if it's out .50 cents then the MSGBOX is activated.

It does not matter whether you do SumRng<>0 or SumRng<>0.0.

But what is the type of SumRng?

I suspect it is Long or Integer. It should be Variant, Currency or Double.
(I prefer Double.)

FYI, I think it would be more prudent to compute:

SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection),2)

which could be written:

With WorksheetFunction
SumRng = .Round(.Sum(Selection),2)
End With

That ameliorates anomalies that arise due to the internal binary arithmetic,
which often causes small decimal fraction differences from what you see when
a cell is formatted with 2 decimal places.

Of course, the use of ROUND() will also mask more significant differences as
well. If that is a concern, then compute SumRng as you did, but do a
comparison like:

If Abs(SumRng) < 0.00001 Then

selecting an appropriate value for 0.00001 that satisfies your degree of
tolerance.

BTW, I use WorksheetFunction.Round (Excel ROUND) instead of VBA Round()
because the latter rounds differently than Excel does. See the description
of the CInt and CLng functions for details.


----- original message -----

 

[JoeU2004]

I've tried Rounding with no results.
My goal is to have the column equal Zero or,
at least within a penny, but I'm getting this
instead -0.00000000006984990.

My response to your original inquiry anticipated this. Have a look at it.

But you say that rounding did not remedy the problem. It certainly should
have.

If my previous posting does not answer your question, please post your
rounding code, including all relevant declarations.


----- original message -----

Hi Peter, You were spot on with the SumRng as Double. But I'm having
a little problem getting my code to work properly. For some reason
there are trailing decimals that I am told are binary values. How do
I deal with them? I've tried Rounding with no results. My goal is to
have the column equal Zero or, at least within a penny, but I'm
getting this instead -0.00000000006984990. After changing SumRng to
double I get something like this in the MSGBOX-6.984990. I think my
problem is the binary values. Thanks for your assistance, Ron

 

[JoeU2004]

PS....

My goal is to have the column equal Zero or,
at least within a penny, but I'm getting this
instead -0.00000000006984990.

Although simply rounding the sum might mask this particular abberation, it
would be prudent to use ROUND liberally in most financial calculations in
the Excel spreadsheet as well. That will minimize propagating such
numerical abberations into other spreadsheet calculations. Even formulas as
innocuous as =A1-A2 can introduce annoying numerical abberations.


----- original message -----

 

[Ron]

Hi JoeU2004, thanks to you I think I have it working properly. I
really like your approach to the rounding issue and thanks for the
heads up on Rounding. How does this look?


Sub JrnlValidationCode()
Dim mybook As Workbook
Dim ValBook As Workbook
Dim SumRng As Double
Set mybook = ActiveWorkbook

Application.ScreenUpdating = False
ActiveSheet.Unprotect

Range("E12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 11).Select

With Selection
SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)

With WorksheetFunction
SumRng = .Round(.Sum(Selection), 2)
End With

If SumRng <> 0# Then
MsgBox prompt:="Out of Balance, Please make the necessary corrections"
_
& vbNewLine & _
"and re-run Validation to complete the validation process." _
& vbNewLine & vbNewLine & _
"Balance should equal ZERO." _
& vbNewLine & vbNewLine & _
"Amount column is out of Balance! " & "$" & SumRng, _
Title:="Amount Column Status"
Exit Sub
End If
End With

'validates columns to meet PeopleSoft requirements.
Range("E12").Select
ValDataI
ValDataK
ValDataN
ValDataW
ValDataAA
ValDataAB
ValDataAI
FindRedCell
Validate
LockCells

ActiveSheet.Protect

End Sub

 

[JoeU2004]

How does this look?
[....]
SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)
With WorksheetFunction
SumRng = .Round(.Sum(Selection), 2)
End With

No need to sum and round twice. It doesn't get any better .

These are just two ways to do the same thing. "With WorksheetFunction"
saves you the trouble of typing "WorksheetFunction" twice in the same
statement. Since you've done it already, I would get rid of the "With
WorksheetFunction" form.


FYI, I see no reason to do "With Selection" in this code snippet.

Of course, if you have more references to Selection objects/methods that you
are not showing here (understandably), that's fine. Otherwise, you might
consider getting rid of the "With Selection".

And it appears that you are missing the statement
Application.ScreenUpdating=True at the end of macro.

Again, the "omission" might be because this code snippet is part of a larger
code, much of which you prudently omitted from your posting so as to focus
on the relevant issues. Just thought I'd bring it to your attention.

One omission seems to be a definite defect, though: you do not have
ActiveSheet.Protect and ScreenUpdating=True statements before the Exit Sub
within the If statement.


----- original message -----

Hi JoeU2004, thanks to you I think I have it working properly. I
really like your approach to the rounding issue and thanks for the
heads up on Rounding. How does this look?


Sub JrnlValidationCode()
Dim mybook As Workbook
Dim ValBook As Workbook
Dim SumRng As Double
Set mybook = ActiveWorkbook

Application.ScreenUpdating = False
ActiveSheet.Unprotect

Range("E12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 11).Select

With Selection
SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)

With WorksheetFunction
SumRng = .Round(.Sum(Selection), 2)
End With

If SumRng <> 0# Then
MsgBox prompt:="Out of Balance, Please make the necessary corrections"
_
& vbNewLine & _
"and re-run Validation to complete the validation process." _
& vbNewLine & vbNewLine & _
"Balance should equal ZERO." _
& vbNewLine & vbNewLine & _
"Amount column is out of Balance! " & "$" & SumRng, _
Title:="Amount Column Status"
Exit Sub
End If
End With

'validates columns to meet PeopleSoft requirements.
Range("E12").Select
ValDataI
ValDataK
ValDataN
ValDataW
ValDataAA
ValDataAB
ValDataAI
FindRedCell
Validate
LockCells

ActiveSheet.Protect

End Sub

 

[Ron]

Hi Joe, thank you for your assistance and sharing your expertise with
VBA. I made the changes you recommended and it runs great. Thanks,
Ron

How does this look?
[....]
SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)
With WorksheetFunction
SumRng = .Round(.Sum(Selection), 2)
End With

No need to sum and round twice.  It doesn't get any better .

These are just two ways to do the same thing.  "With WorksheetFunction"
saves you the trouble of typing "WorksheetFunction" twice in the same
statement.  Since you've done it already, I would get rid of the "With
WorksheetFunction" form.

FYI, I see no reason to do "With Selection" in this code snippet.

Of course, if you have more references to Selection objects/methods that you
are not showing here (understandably), that's fine.  Otherwise, you might
consider getting rid of the "With Selection".

And it appears that you are missing the statement
Application.ScreenUpdating=True at the end of macro.

Again, the "omission" might be because this code snippet is part of a larger
code, much of which you prudently omitted from your posting so as to focus
on the relevant issues.  Just thought I'd bring it to your attention.

One omission seems to be a definite defect, though:  you do not have
ActiveSheet.Protect and ScreenUpdating=True statements before the Exit Sub
within the If statement.

----- original message -----


Hi JoeU2004, thanks to you I think I have it working properly.  I
really like your approach to the rounding issue and thanks for the
heads up on Rounding.  How does this look?

Sub JrnlValidationCode()
Dim mybook As Workbook
Dim ValBook As Workbook
Dim SumRng As Double
Set mybook = ActiveWorkbook

Application.ScreenUpdating = False
ActiveSheet.Unprotect

Range("E12").Select
Range(Selection, Selection.End(xlDown)).Offset(0, 11).Select

With Selection
SumRng = WorksheetFunction.Round(WorksheetFunction.Sum(Selection), 2)

With WorksheetFunction
SumRng = .Round(.Sum(Selection), 2)
End With

If SumRng <> 0# Then
MsgBox prompt:="Out of Balance, Please make the necessary corrections"
_
& vbNewLine & _
"and re-run Validation to complete the validation process." _
& vbNewLine & vbNewLine & _
"Balance should equal ZERO." _
& vbNewLine & vbNewLine & _
"Amount column is out of Balance! " & "$" & SumRng, _
Title:="Amount Column Status"
Exit Sub
End If
End With

'validates columns to meet PeopleSoft requirements.
Range("E12").Select
ValDataI
ValDataK
ValDataN
ValDataW
ValDataAA
ValDataAB
ValDataAI
FindRedCell
Validate
LockCells

ActiveSheet.Protect

End Sub

PS....

Although simply rounding the sum might mask this particular abberation,it
would be prudent to use ROUND liberally in most financial calculations in
the Excel spreadsheet as well. That will minimize propagating such
numerical abberations into other spreadsheet calculations. Even formulas
as
innocuous as =A1-A2 can introduce annoying numerical abberations.

----- original message -----

- Show quoted text -- Hide quoted text -

- Show quoted text -