Programming Excel with VBA and .NET

Change Strings

A lot of programming tasks involve getting or changing parts of a string. For simple replacement tasks, use the Replace method as shown here:

    Sub DemoSearchAndReplace( )
        Dim str As String
        str = "this is some text and some more text"
        str = Replace(str, "some", "different")
        Debug.Print str
    End Sub

The preceding code replaces all instances of some with different in a case-sensitive way. Replace also provides option for replacing a certain number of occurrences, starting at a specific position within the string and doing case-insensitive searches. Replace also replaces one string with another regardless of their length. If the strings are the same length, you could use the Mid statement to change the source string, instead:

    Mid(str, InStr(1, str, "text")) = "word"
    Debug.Print str
    ' Displays: this is different word and different more text

The Mid statement is unusual in that it receives an assignment—in this case the replacement string "word". Since Mid can’t make strings longer or shorter, it is mainly useful for modifying string data that is in a fixed-width format or for replacing single characters, such as punctuation.

Visual Basic also provides a set of functions to remove leading, trailing, or leading and trailing whitespace characters from a string: LTrim, RTrim, and Trim. Excel does Visual Basic one better by adding the Trim worksheet function, which removes repeated internal spaces as well. The following code demonstrates each of the different trim functions:

 Sub ...