Truncating Double with VBA in excel
Solution 1
You can either use ROUND
for FORMAT
in VBA
For example to show 2 decimal places
Dval = 1.56789
Debug.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 String
dVal = 1.56789
strVal = dVal
If InStr(1, strVal, ".") Then
Debug.Print Split(strVal, ".")(0) & "." & Left(Split(strVal, ".")(1), 2)
Else
Debug.Print dVal
End If
Solution 2
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 = 2
Dim dblValue As Double: dblValue = 2.345
Dim lScale = 10 ^ lDecimalPlaces
Dim dblTruncated As Double: dblTruncated = Fix(dblValue * lScale) / lScale
This yields "2.34".
Solution 3
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
Ehudz
Updated on December 19, 2021Comments
-
Ehudz over 2 years
I need to truncate the amount of decimal places of my double value for display in a textbox. How would one achieve this with vba?
-
GlennFromIowa about 7 yearsI had forgotten about the Banker's rounding. +1 despite the condescending "just use math" jab. For just displaying the number as the OP requested, if you don't care much about rounding, Format() is the tool for the job, and is simpler than Fix() (or Int(), which is equivalent to Fix() for positive numbers). However, there are cases where Banker's rounding will cause user questions and require a more complex transformation.
-
vpprof over 3 yearsThere is a sign function that achieves
sign = Abs(value) / value
in one step:Sgn(value)
. -
Gustav over 3 yearsNever handle numbers as text.