Truncating Double with VBA in excel
If you want to round the value, then you can use the Round function (but be aware that VBA's Round function uses Banker's rounding, also known as round-to-even, where it will round a 5 up or down; to round using traditional rounding, use Format).
If you want to truncate the value without rounding, then there's no need to use strings as in the accepted answer - just use math:
Dim lDecimalPlaces As Long: lDecimalPlaces = 2Dim dblValue As Double: dblValue = 2.345Dim lScale = 10 ^ lDecimalPlacesDim dblTruncated As Double: dblTruncated = Fix(dblValue * lScale) / lScale
This yields "2.34".
You can either use ROUND
for FORMAT
in VBA
For example to show 2 decimal places
Dval = 1.56789Debug.Print Round(dVal,2)Debug.Print Format(dVal,"0.00")
Note: The above will give you 1.57
. So if you are looking for 1.56
then you can store the Dval in a string and then do this
Dim strVal As StringdVal = 1.56789strVal = dValIf InStr(1, strVal, ".") Then Debug.Print Split(strVal, ".")(0) & "." & Left(Split(strVal, ".")(1), 2)Else Debug.Print dValEnd If
You can use Int() function. Debug.print Int(1.99543)
Or Better:
Public Function Trunc(ByVal value As Double, ByVal num As Integer) As Double Trunc = Int(value * (10 ^ num)) / (10 ^ num)End Function
So you can use Trunc(1.99543, 4)
==> result: 1.9954