Can't Specify Number of Digits with Fix() or Int() (124107)

SUMMARY

In Microsoft Excel, if you want to truncate a number in a Microsoft Visual Basic for Applications macro or procedure, you can use the Int() function or the Fix() function to remove the fractional part of the number and return the resulting integer value.

However, if you want to pass an argument to specify a certain number of decimal places that you want to remain, as you can with the Microsoft Excel version 4.0 TRUNC() function, which has a num_digits argument, you must create a custom function. An example of such a function is in the "More Information" section of this article.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Custom Function to Simulate TRUNC() Function with num_digits Argument

   ' Name of the procedure with parameter definitions.
 
   Function VBATrunc(Number As Double, DecPlaces As Integer) As Double
       VBATrunc = Fix(Number * (10 ^ DecPlaces)) / (10 ^ DecPlaces)
   End Function

REFERENCES

Microsoft Excel 97

For more information about the Fix function, click the Office Assistant in the Visual Basic Editor, type fix click Search, and then click to view "Int, Fix Functions."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Microsoft Excel Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:

120802 Office: How to Add/Remove a Single Office Program or Component

Microsoft Excel 7.0

For more information about truncating values in a Visual Basic for applications macro, click the Index tab in Microsoft Excel 7.0 Help, type the following text

truncating

and then double-click the selected text to go to the "Int Function, Fix Function" topic.

For more information about the TRUNC() function, choose the Search button in Help and type:

trunc