VBA – Trailing Character

It is common to have to ensure the formatting of certain string to be consumed by other procedures.

A common example of this can be when trying to building a path from a string entered by the user. In some cases, they include the trailing slash and in other it may be omitted. So how do you code for these types of scenarios where you never know exactly what you are going to be handed as data?

Now if you are only ever working with paths and trailing slashes, then Allen Browne had created the following function:

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

But I wanted a slightly more versatile function that could be used in other cases as well and didn’t want to create a new function everytime the trailing character(s) was different. As such, several years ago I created the following function for exactly these cases!

'---------------------------------------------------------------------------------------
' Procedure : CheckTrailingChars
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Ensure the ending character(s) match the string supplied.
'             Primary purpose is to ensure paths include the trailing \
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sString   : The string to check the last characters
' sChar     : The last characters to look for
'
' Usage:
' ~~~~~~
' CheckTrailingChars("C:\Users\Daniel\Desktop\XLS Testing", "\")
'             Returns C:\Users\Daniel\Desktop\XLS Testing\
' CheckTrailingChars("C:\Users\Daniel\Desktop\XLS Testing\ ", "\")
'             Returns C:\Users\Daniel\Desktop\XLS Testing\
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-05-13              Initial Release
'---------------------------------------------------------------------------------------
Public Function CheckTrailingChars(sString As String, sChar As String) As String
    On Error GoTo Error_Handler

    If Right(sString, Len(sChar)) <> sChar Then sString = sString & sChar
    CheckTrailingChars = sString

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: CheckTrailingChars" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function