AnsweredAssumed Answered

How to add trailing zeroes in VBA?

Question asked by Keaton Warn on Aug 23, 2018
Latest reply on Aug 23, 2018 by Josh Brady

I'm trying to get trailing zeros onto a Single decimal variable out to the 3rd decimal place (thousandths, 0.000). Everything I've found on Google is for formatting Excel cells, not actual VBA code.

 

Option Explicit
Sub main()

Dim i           As Long
Dim lgLimit     As Long
Dim flAdd       As Single
Dim flVal       As Single
Dim strVal      As String
Dim strOne      As String

lgLimit = 40
flAdd = 0.01
flVal = 0.01
strOne = "Me.ComboBox2.AddItem " & """"

Debug.Print "Iterations: " & (lgLimit)
Debug.Print "strOne: " & strOne
Debug.Print "Beginning flVal: " & flVal & vbNewLine

For i = 1 To lgLimit
    strVal = CStr(flVal)
    Debug.Print strOne & strVal & """"
    flVal = (flVal + flAdd)
    flVal = Round(flVal, 3)
Next i

Debug.Print vbNewLine
End Sub

 

I realize I could add a section to manipulate the string (case structure or series of if statements or a For loop) for strVal. If strVal doesn't have 5 chars (e.g. of five chars "1.345") to add the character 0 until it does have 5 chars (e.g. strVal is 1.1 and becomes 1.100), but I can't help but think that there might be a way to deal with this directly with syntax.

 

Here is the completed code with a For Loop that adds zeros to the output string in the immediate box:

Option Explicit
Sub main()

Dim j           As Integer
Dim intLen      As Integer
Dim i           As Long
Dim lgLimit     As Long
Dim flAdd       As Single
Dim flVal       As Single
Dim strVal      As String
Dim strOne      As String

lgLimit = 30
flAdd = 0.01
flVal = 0.01
strOne = "Me.ComboBox2.AddItem " & """"

Debug.Print "Iterations: " & (lgLimit)
Debug.Print "strOne: " & strOne
Debug.Print "Beginning flVal: " & flVal & vbNewLine

For i = 1 To lgLimit
    strVal = CStr(flVal)

    For j = 0 To 4
        intLen = Len(strVal)
        If intLen < 5 Then
            strVal = strVal & "0"
        End If
    Next j
    
    Debug.Print strOne & strVal & """"
    flVal = (flVal + flAdd)
    flVal = Round(flVal, 3)
Next i

Debug.Print vbNewLine

End Sub

 

 

Is there any way to add the trailing zeros with a syntax method or will I have to use some string manipulation code, like the For Loop shown above?

Outcomes