Rounding Module (Function)

[ericgon34]

Hello Everyone,

I am having a problem with my Access database in which I have implemented a Rounding function (VBA Code) that I was allowed to use and implement into my database, labeled BankersRounding. I have kept the function in its original state, unchanged for recognition, respect and copyright application.

This is a great function to use and it has really helped me a great deal.

Information: This database calls upon the use of data entry from different departments, and when one set of data points don't line up, this allows the BankersRounding function to elicit the Err.Raise 5 Invalid Procedure dialog box. This is due to the fact that when it evaluates the records it expects a number, instead it says error, due to a record from one record missing that is suppose to be linked to another.

Problem: When the error applies I am looking to implement some code that when the error does occur it sends an e-mail letting them know that they have missed entering data. And lets the user of the database know that "There is missing information, and the appropriate steps have been taken to address this issue".

I have tried to implement the code myself for hours, but with no luck.:banghead:

Unfortunately I am not very good with VBA, and I would really appreciate some help from anyone willing to help.

I have pasted the Bankers Rounding Function for reference, as well as some pictures.





'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public Function Round( _
ByVal Number As Variant, NumDigits As Long, _
Optional UseBankersRounding As Boolean = False) As Double
'
' ---------------------------------------------------
' From "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
' ---------------------------------------------------
'
Dim dblPower As Double
Dim varTemp As Variant
Dim intSgn As Integer
Dim MsgBox As Label
If Not IsNumeric(Number) Then
' Raise an error indicating that
' you've supplied an invalid parameter.
Err.Raise 5

End If


dblPower = 10 ^ NumDigits
' Is this a negative number, or not?
' intSgn will contain -1, 0, or 1.
intSgn = Sgn(Number)
Number = Abs(Number)

' Do the major calculation.
varTemp = CDec(Number) * dblPower + 0.5

' Now round to nearest even, if necessary.
If UseBankersRounding Then
If Int(varTemp) = varTemp Then
' You could also use:
' varTemp = varTemp + (varTemp Mod 2 = 1)
' instead of the next If ...Then statement,
' but I hate counting on TRue == -1 in code.
If varTemp Mod 2 = 1 Then
varTemp = varTemp - 1
End If
End If
End If
' Finish the calculation.
Round = intSgn * Int(varTemp) / dblPower
End Function

 

[gemma-the-husky]

a) what was wrong with simply round(somenumber, decimalplaces)

b)
Public Function Round(ByVal Number As Variant, NumDigits As Long)

this will only fail if you do not pass a number to the function, so before calling the function, test that the user has a numeric value. I can't see why you need to have so much complex error-handling within the function


what are you trying to achieve with the rounding function?

 

[spikepl]

As Dave asked, what was wrong with the Access built-in Round function, which actually provides banker's rounding?

 

[CedarTree]

As long as I keep the reference to Ken Getz and Mike Gilbert in the coding, I can use the coding above, right?

 

[Cronk]

Yes you can use the code but the point being made in the responses so far is why use a function with 20 lines of code when you can do the same thing in one line with a built in (faster) function.

? round(22.5879,2)
 22.59

 

[Deleted member 147267]

sometimes built in function alone is not accurate enough, especially where $ is concerned.
http://allenbrowne.com/round.html

 

[Galaxiom]

sometimes built in function alone is not accurate enough, especially where $ is concerned.
http://allenbrowne.com/round.html

Which part of that link were you referring to as the Round function being "not accurate"?

You might get problems with rounding the results of floating point operations on Single or Double, but that is nothing to do with Round() being inaccurate.

If floating point discrepancies matter then use one of the scaled integer datatypes, Currency or Decimal, especially where $ are concerned.

 

[Deleted member 147267]

Data stored in table can be different (actual value) from what you see. I didn't say it wasn't "accurate" although I suppose one could because rounding completely negates accuracy. In fact, I might add "accurate rounding" to my list of oxymorons.