VBA Mid Statement

In VBA, the Mid statement can be used to modify a portion of a string variable. This is different from the Mid function, which simply retrieves a substring without modifying the original string.

VBA Mid Statement

Syntax

The syntax for using the Mid statement for replacement is:

Mid(stringVar, start[, length]) = replacementString

Where:

• stringVar is the string variable you want to modify.
• start is the position in the string where you want to start the replacement.
• length (optional) is the number of characters you want to replace. If omitted, it replaces characters from the start position to the end of the string.
• replacementString is the string that will replace the specified portion of stringVar.

VBA Mid Function with Examples

Example 1: Replacing a substring from a given position

Dim MyString As String
MyString = "The dog jumps" ' Initialize string.
Mid(MyString, 5, 3) = "fox" ' MyString becomes: "The fox jumps".

In this example, we start with the string “The dog jumps”. We want to replace the substring “dog” with “fox”.

• Mid(MyString, 5, 3) targets the substring starting at the 5th character of MyString and spanning 3 characters. This means we’re targeting the substring “dog”.
• By setting this targeted substring equal to “fox”, we replace “dog” with “fox”.

The result is that MyString is modified to “The fox jumps”.

Example 2: Replacing a substring without specifying length

Dim MyString As String
MyString = "The dog jumps" ' Initialize string.
Mid(MyString, 5) = "cow" ' MyString becomes: "The cow jumps".

Here, we again start with the string “The dog jumps”. We want to replace the substring starting from the 5th character onwards with “cow”.

• Mid(MyString, 5) targets the substring starting at the 5th character and going until the end of the string, which is “dog jumps”.
• By setting this targeted substring equal to “cow”, we replace “dog jumps” with “cow”.

The result is that MyString is modified to “The cow jumps”.

Example 3: Replacing with a longer substring than the original

Dim MyString As String
MyString = "The dog jumps" ' Initialize string.
Mid(MyString, 5) = "cow jumped over" ' MyString becomes: "The cow jumpe".

Starting with “The dog jumps”, we aim to replace the substring from the 5th character onwards with “cow jumped over”.

• Mid(MyString, 5) again targets the substring from the 5th character to the end, which is “dog jumps”.
• Setting this to “cow jumped over” would exceed the original string’s length. Hence, the replacement string “cow jumped over” is truncated to fit the remaining space in MyString.

The result is that MyString becomes “The cow jumpe”.

Example 4: Replacing with a substring that’s shorter than the specified length

Dim MyString As String
MyString = "The dog jumps" ' Initialize string.
Mid(MyString, 5, 3) = "duck" ' MyString becomes: "The duc jumpe".

With the initial string “The dog jumps”, we want to replace the substring “dog” with “duck”.

• Mid(MyString, 5, 3) targets the substring “dog”, starting at the 5th character and spanning 3 characters.
• Setting this substring to “duck” would mean trying to fit a 4-character string (“duck”) into a space of 3 characters (“dog”). As a result, the replacement string “duck” is truncated to “duc”.

The final result is that MyString is modified to “The duc jumpe”.

In all these examples, the Mid statement showcases its ability to modify strings in place, either replacing, truncating, or shortening the original string based on the provided parameters.

Points to Remember

• If the replacement string is longer than the specified length, the replacement string will be truncated to fit the specified length.
• If the length is omitted, the replacement string will replace all characters from the start position to the end of the string.
• If the replacement string is shorter than the specified length or the remaining length of the original string (when length is omitted), the original string will be shortened.

Conclusion

The Mid statement in VBA offers a versatile way to modify strings in place. Whether you’re performing data transformations, cleaning up text, or other string manipulations, the Mid statement can be a valuable tool in your VBA toolkit. Always remember to test your code to ensure the desired modifications are achieved.