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

ID: Q124107

The information in this article applies to:

Microsoft Excel for Window, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel 97 for Windows
Microsoft Excel 98 Macintosh Edition

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

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:

   ARTICLE-ID: Q120802
   TITLE     : 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

Additional query words: 5.00 7.00 XL98 XL97 XL7 XL5 worksheet

Keywords          : kbprg kbdta kbdtacode PgmOthr PgmHowto KbVBA 
Version           : WINDOWS:5.0,5.0c,7.0,97; MACINTOSH:5.0,5.0a,98
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999