Wednesday, 23 March 2011

Using VBA to Truncate Decimal Values

Recently I have needed to truncate a decimal value to a set number of decimal places. 

For example; If you wanted the number 55.446 to two decimal places rounding the number would return 55.45.  Truncating the number to two decimal places returns 55.44. 

To do this I created the function below.  Pass in a decimal value and the number of decimal places you want the number truncated too and the function returns the value as a ‘Double’.

Public Function TruncTo(dblValue As Double, lngPlaces As Long) As Double
' Procedure : TruncTo
' Author    : Matthew Sims
' Date      : 08-Oct-2010
' Purpose   : Truncate a decimal value to the requested number of decimal places
    On Error GoTo TruncTo_Error

    If IsNumeric(dblValue) Then
        TruncTo = Int(dblValue * 10 ^ lngPlaces) / 10 ^ lngPlaces
        TruncTo = 0
    End If

    On Error GoTo 0
    Exit Function

    ' Add some error handling code here
End Function

1 comment:

  1. Hi,

    I was looking into the truncate problem in VBA as well. In VBA the function to truncate decimals is called FIX().

    BTW, your function would give an "overflow error" in case dblValue is longer than the max. int value.


Please, no purely anonymous comments, they will be deleted; always use a name for ease of reference by other commenters.