How do I keep the trailing zero (or zeros) when I round with Excel VBA?

Question

The title says it all. Consider the following example:

Option Explicit

Sub RoundToDecimalsDefinedByEndUser()
    Dim DecimNum As Integer
    DecimNum = InputBox("How many decimals do you want?", "Enter")
    Cells(1, 1).Value = Application.WorksheetFunction.Round(Cells(1, 1).Value, DecimNum)
End Sub

Consider a situation where the end user wants to have 2 decimals, and the number to be rounded is 0.10111. The result should be 0.10, but Excel forces the result to be 0.1. Any suggestions on how to keep the trailing zero(s)?

what is displayed by excel in a cell will be dependent on the formatting of the cells — whytheq, Feb 06 '16 at 12:25
If you want to keep trailing zero then it will converted to string not as value. — Harun24hr, Feb 06 '16 at 12:26

whytheq · Answer 1 · 2016-02-06T12:35:11.877

3

Below the line of code cells(1,1).value... you could add some more code based on:

cells(1,1).numberformat...

The number format you use would be #,0.0000000 - somehow you need to adjust the number of zeros to equal DecimNum

dim x as integer
dim numForm as string: numForm = "#,0."
for x = 0 to decimNum
   numForm = numForm & "0"
next x

(not tested as I do not have Excel on this machine)

edited Feb 06 '16 at 12:35

answered Feb 06 '16 at 12:28

whytheq

34,466
65
172
267

Gary's Student · Accepted Answer · 2016-02-06T15:14:40.767

3

Consider:

Option Explicit

Sub RoundToDecimalsDefinedByEndUser()
    Dim DecimNum As Double, wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    DecimNum = InputBox("How many decimals do you want?", "Enter")
    With Cells(1, 1)
      .Value = wf.Round(.Value, DecimNum)
      .NumberFormat = "0." & wf.Rept("0", DecimNum)
    End With
End Sub

This will leave A1 as a numeric value.

edited Feb 06 '16 at 15:14

answered Feb 06 '16 at 15:06

Gary's Student

95,722
10
59
99

`Rept`..very nice function: makes things a lot more concise. – whytheq Feb 06 '16 at 15:19
@whytheq my first thought was to use a *loop* – Gary's Student Feb 06 '16 at 15:42
@Gary'sStudent Thank you for your suggestion - it works perfectly. Several important lessons arise from your solution. One of them is how a couple of things are executed handily to the cell in question using With...End With Statement. Another one is of course the inventive use of the Rept function. Btw, I was surprised to find out that this topic had not been exhaustively discussed earlier in Stack Overflow. – jaggedjava Feb 06 '16 at 19:17
@jaggedjava *REPT()* is useful in string processing. – Gary's Student Feb 06 '16 at 19:52

How do I keep the trailing zero (or zeros) when I round with Excel VBA?

2 Answers2

Linked