Rounding in MS Access

by

Be careful, the VBA Round function uses Banker’s rounding, where it rounds .5 to an even number, like so: 

Round (12.55, 1) would return 12.6 (rounds up) 
Round (12.65, 1) would return 12.6 (rounds down) 
Round (12.75, 1) would return 12.8 (rounds up)

Whereas the Excel Worksheet Function Round, always rounds .5 up.

I’ve done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The ‘Precision as displayed’ flag doesn’t appear to do any rounding itself, it just uses the rounded result of the cell format.

I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:

Application.Round(58.55, 1)

This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.

More Related Contents:

  1. Non-web SQL Injection
  2. Trying to use variables as values vba to sql
  3. How to insert an embedded picture?
  4. Error: “Could Not Find Installable ISAM”
  5. Get length of array?
  6. Comparison of Dictionary, Collections and Arrays
  7. How to extract text within a string of text
  8. How to query a MS-Access Table from MS-Excel (2010) using VBA
  9. What is the different between IsNull, IsEmpty, =Empty, and an empty string ie “” and why might I use variants
  10. Sequence of numbers based on start and end value of 2 cells [closed]
  11. Function to convert column number to letter?
  12. Better way to find last used row
  13. 2 Dimensional array from range [closed]
  14. filter out multiple criteria using excel vba
  15. Using Google Chrome in Selenium VBA (Installation Steps)
  16. Excel formula to get cell color [duplicate]
  17. Dir() function not working in Mac Excel 2011 VBA
  18. How to use workbook.saveas with automatic Overwrite
  19. How to use FindWindow to find a visible or invisible window with a partial name in VBA
  20. How do you get a Range to return its Name?
  21. Passing variable from Form to Module in VBA
  22. Referencing Excel Userform Control Name Value From String (VBA)
  23. Fill formula down till last row in column
  24. Refreshing all the pivot tables in my excel workbook with a macro
  25. Countif With Multiple OR Criteria
  26. Vlookup to copy color of a cell
  27. VBA WinHTTP to download file from password proteced https website
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment