OFF2000: New Round Function in Visual Basic for Applications 6.0

Article translations Article translations
Article ID: 225330 - View products that this article applies to.
This article was previously published under Q225330
Expand all | Collapse all

SUMMARY

Microsoft Visual Basic for Applications version 6.0 in Microsoft Office 2000 includes a new function named Round. This article describes how it rounds and the differences between it and the Microsoft Excel worksheet function named Round.

MORE INFORMATION

The Visual Basic Round function returns a number rounded to a specified number of decimal places and contains the following arguments:
  • expression - numeric expression being rounded.
  • numdecimalplaces - number indicating how many places to the right of the decimal are included in the rounding. If omitted, or set to zero integers are returned.
The Round function may return different results than the Excel Round worksheet function when both of the following conditions are true:
  • You set the numdecimalplaces argument to zero, or omit this argument.
    -and-
  • The expression contains an even numbered integer ending in the decimal .5.
When a number with an even integer ends in .5, Visual Basic rounds the number (down) to the nearest even whole number. This is different than how Excel's Round function works. Excel rounds all numbers ending in .5 to the next highest whole number. This difference is only for numbers ending in a .5 and is the same with other fractional numbers.
The table below illustrates these differences.

   Number     VBA     Excel
   ------------------------

   1.5        2       2
   
   2.5        2       3
   
   3.5        4       4
   
   4.5        4       5
   
   5.5        6       6
   
   6.5        6       7  
  
   7.5        8       8
   
   8.5        8       9
 
   9.5        10      10
   
   10.5       10      11
				


If you want to use a round function consistent with Excel's worksheet function, use the WorksheetFunction property as illustrated in the example below:
x = Application.WorksheetFunction.Round(y ,0)
				

In this example substituting 8.5 for y would return a 9.

REFERENCES

For more information about the Round function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type Round Function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 225330 - Last Review: June 24, 2004 - Revision: 3.0
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Visual Basic for Applications 6.0
Keywords: 
kbhowto kbprogramming kbdtacode kbdocerr KB225330

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com