Double truncation with VBA in excel

Question

I need to truncate the number of decimal places of my double value to display in the text box. How can this be achieved with vba?

Answer 1

You can 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 . Therefore, if you are looking for 1.56 , you can save the Dval in a string and then do it

 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 

Answer 2

If you want to round a value, you can use the Round function (but remember that the VBA Round function uses Banker rounding, also known as round-to-even, where it will be around 5 up or down; a round using traditional rounding, use "Format ").

If you want to truncate a value without rounding, then there is no need to use strings as in the accepted answer - just use the 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 gives "2.34".

Answer 3

You can use the 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

Answer 4

So much fun. I was messing around with a fast VB conversion function. I just want to truncate double to integer.

 value = Int(83.768) value == 83 

Surprisingly, something in VB really worked.

Oh I forgot that this does not work with negative numbers

 value = Int(-83.768) value == -84 

... yes, it just happened. VB uses Banker rounding.

 Public Function Trunc(ByVal value As Double) As Integer ' Truncate by calling Int on the Absolute value then multiply by the sign of the value. ' Int cannot truncate doubles that are negative Trunc = (Abs(value) / value) * Int(Abs(value)) End Function 

If you want certain decimal places to do what Mac did only with abs around the value, so Int can truncate correctly.

 Public Function Trunc2(ByVal value As Double, Optional ByVal num As Integer = 1) As Double ' Truncate by calling Int on the Absolute value then multiply by the sign of the value. ' Int cannot truncate doubles that are negative Dim sign As Integer sign = Abs(value) / value Trunc2 = sign * (Int(Abs(value) * (10 ^ num)) / (10 ^ num)) End Function