Truncating Double with VBA in excel

Question

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?

Answer 1

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".

Answer 2

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