1 Reply Latest reply on Aug 23, 2018 12:49 PM by Josh Brady

    How to add trailing zeroes in VBA?

    Keaton Warn

      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?