Padding Strings

As mentioned in the post regarding ZIP Codes, sometimes I need to add characters at the beginning or end of a character string.  It's quite easy in Excel to use the REPT function:
=REPT("xo",5) & " Ma cherie" sends your true love hugs and kisses

The String function is fine for repeating the same character, but sometimes the need is to repeat the same string multiple times.  Will Robinson knew this as the "DANGER! DANGER! " function

Ucase(String(2, "DANGER! ") & "Will Robinson!") makes the robot stutter, "D D WILL ROBINSON!"

For those cases when you want to pad the front, back, or both sides with a character string you can use the following:

Public Function Pad(StartWith As String, _

                `              Pad_String As String, _

                              Optional Pad_Repeat As Double = 1, _

                              Optional SpacePad As Boolean = TRUE, _

                              Optional FrontBackBoth As String = "BOTH") As String

' StartWith is the basic string to have characters added.  In the above, "Will Robinson"

' Pad_String is what will be added, i.e. "Danger!" in the above

' Pad_Repeat determines how many times Pad_String will occur.

' Space_Pad determines whether a space is used between each Pad_String and eliminates the need to make certain there is a space at the end of each field or included in a concatenated string

'   * TRUE (the default) would result in "DANGER! DANGER! " 

'   * FALSE would result in "Danger!Danger!"

' FrontBackBoth controls whether Pad_String will be added to the left of StartWith(Front), the right (Back), or surrounding (Both).  

    Dim i As Double

    FrontBackBoth = Trim(UCase(FrontBackBoth))

    Pad_Repeat = Round(Pad_Repeat, 0)

    If (FrontBackBoth <> "BOTH" And FrontBackBoth <> "FRONT" And FrontBackBoth <> "BACK") _

        Or Pad_Repeat < 0 Then

        Pad = "#ERROR"

        Exit Function

    End If

    Pad = Trim(StartWith)

    If FrontBackBoth = "BOTH" Or FrontBackBoth = "FRONT" Then

        For j = 1 To Pad_Repeat

            If SpacePad Then Pad = " " & Pad

            Pad = Pad_String & Pad

        Next j

    End If

    If FrontBackBoth = "BOTH" Or FrontBackBoth = "BACK" Then

        For j = 1 To Pad_Repeat

            If SpacePad Then Pad = Pad & " "

            Pad = Pad & Pad_String

        Next j

    End If

End Function

To perform the same thing, with the exception of the optional no-space between the element, using an Excel formula it looks like the following
=IF(OR(UPPER(TRIM(D24))="BOTH",UPPER(TRIM(D24)) ="FRONT"),REPT(TRIM(B24)& " ",ROUND(C24,0)),"")
&TRIM(A24)
&IF(OR(UPPER(TRIM(D24))="BOTH",UPPER(TRIM(D24)) ="BACK"),REPT( " "&TRIM(B24),ROUND(C24,0)),"")

This assumes, referring to the variables used in the VBA Function

• Column A: StartWith ("Will Robinson")
• Column B: Pad_With ("Danger!)
• Column C: Pad_Repeat
• Column D: FrontBackBoth