Visual Basic Programming Code Examples

Search and Replace Text To search and replace text in a variable use the following (please read the notes for guidance on how to use this routine). 'Purpose : Searches and replaces all instances of a string within a variable. 'Inputs : sText The string to search. ' sReplaceThis The string to replace. ' sWithThis The replacement string. ' [lNumReplacements] Returns the number of times a replacement was made. 'Outputs : N/A 'Notes : This routines output parameters are passed in ByRef for speed and hence it should be called: ' Dim sOutputText as String 'Create a variable to hold the value in ' sOutputText = "aaaaaa" ' SearchAndReplace sOutputText, "a", "b" ' Debug.Print sOutputText Sub SearchAndReplace(ByRef sText As String, ByVal sReplaceThis As String, ByVal sWithThis As String, Optional ByRef lNumReplacements As Long) Dim lPos As Long, lLenOriginal As Long, lLenReplace As Long lLenOriginal = Len(sReplaceThis) lLenReplace = Len(sWithThis) If lLenOriginal Then lPos = InStr(1, sText, sReplaceThis) Do While lPos > 0 lNumReplacements = lNumReplacements + 1 If lLenOriginal = lLenReplace Then Mid$(sText, lPos, Len(sReplaceThis)) = sWithThis Else sText = Left$(sText, lPos - 1) & sWithThis & Mid$(sText, lPos + lLenOriginal) End If lPos = InStr(lPos + lLenOriginal, sText, sReplaceThis) Loop End If End Sub