Visual Basic > Strings

Replacing a section of string (useful for connection strings)

Replacing a section of string (useful for connection strings)

The following code replaces a section of a string with another string. This

'Purpose   :    Replaces a portion of a delimated string with a different string
'Inputs    :    sReplace                The string to replace a section from
'               sStartDelim             The delimeter which denotes the start of the string to replace.
'               sEndDelim               The delimeter which denotes the end of the string to replace.
'               sReplaceWith            The string to replace the value with
'Outputs   :    Returns the string with the section replaced, or the orignal string if the search string was not found.
'Notes     :    Only replaces the first instance, call recursively to replace all
'Example   :    Debug.Print ReplaceDelimString("UID=OldUID;Database=test;PWD=Hello", "UID=", ";", "NewUID")
'               Returns "UID=NewUID;Database=test;PWD=Hello"


Function ReplaceDelimString(sReplace As String, sStartDelim As String, sEndDelim As String, sReplaceWith As String) As String
    Dim lPosStart As Long, lPosEnd As Long, lLen As Long
    Dim lLenStartDelim As Long, lLenEndDelim As Long
    
    On Error GoTo ErrFailed
    'Determine string properties
    lLen = Len(sReplace)
    lLenStartDelim = Len(sStartDelim)
    lLenEndDelim = Len(sEndDelim)
    'Find start delimeter
    lPosStart = InStr(1, sReplace, sStartDelim, vbTextCompare)
    
    If lPosStart Then
        lPosStart = lPosStart + lLenStartDelim
        'Find end delimeter
        If Len(sEndDelim) Then
            lPosEnd = InStr(lPosStart, sReplace, sEndDelim, vbTextCompare)
        End If
        If lPosEnd = 0 Then
            'Did not find end delimeter, use the end of the string
            lPosEnd = lLen + 1
        End If
        'Replace the string
        ReplaceDelimString = Left$(sReplace, lPosStart - 1) & sReplaceWith & Mid$(sReplace, lPosEnd)
    Else
        'Did not find item
        ReplaceDelimString = sReplace
    End If
    
    Exit Function

ErrFailed:
    Debug.Assert False
    Debug.Print "ReplaceDelimString Error: " & Err.Description
    On Error GoTo 0
End Function