Code Example - VBA: Do Something With Your Strings

VBA is not the best language when it comes to String manipulation; it's not nearly as robust as, say, Python's Slicing ability. But that doesn't mean you can't do useful things with VBA. In fact, here are a few slightly more advanced techniques to get you started...

First off, while VBA doesn't have a ton of built-in functions to work with Strings, there are some you will have at your disposal - for example, those found on this site. Being that these are all outlined pretty well already, I'm going to skip over most of them, but that doesn't mean they're not useful.

---

Concatenating one or more Strings
Let's say you have a form prompting a user (let's call him Joe Smith) to enter their first and last name into two different fields (let's call them FName and LName). You want to store those two variables as one String, so you use the concatenation operator & to do this.

Dim sUserName As String, LName As String, FName As String
LName = "Smith"
FName = "Joe"
sUserName = FName & LName ' Stick 'em together with "&".

But that's not quite right, as we end up with "JoeSmith". We need to add a space (" ") between the two:

Dim sUserName As String, LName As String, FName As String
LName = "Smith"
FName = "Joe"
sUserName = FName & " " & LName ' Note the extra "&" surrounding the space.

If you wanted to save this as "Smith, Joe", you can do this just as easily.

Dim sUserName As String, LName As String, FName As String
LName = "Smith"
FName = "Joe"
sUserName = LName & ", " & FName ' Nothing too fancy required.

---

Inserting a character into a String
Perhaps you got ahead of yourself and didn't think to program in your comma and space from the example above, and the computer thinks it's working with some guy named "SmithJoe". That's ok! We can still adjust the String after it's been stuck together, and there are actually a few options available to do this.
First, we can use the Left() and Right() functions to help.

Dim sUserName As String
sUserName = "SmithJoe"
sUserName = Left(sUserName, 5) & ", " & Right(sUserName, 3)

What's happening here is the String, sUserName/"SmithJoe", is being separated into it's two parts, with the comma and space are being concatenated with those two parts. This leaves us with a nice, clean "Smith, Joe"
Compare this with essentially the same job in Python:

s = 'SmithJoe'
s = s[:5] + ', ' + s[5:]
print(s) # s = 'Smith, Joe'

---

Finding a String within a String
If we know this is our user's name, this works just fine, but this is usually not going to be the case. If John Doe was using our application, then the code above would return "DoeJo, ohn", and that's no good. So how do we work with this name, or any others?

First, let's change our code above to assign the parameters used for Right() and Left() to variables.

iLeftCount = 5
iRightCount = 3
sUserName = Left(sUserName, iLeftCount) & ", " & Right(sUserName, iRightCount)

As is, this code will still produce the same result, but now we are able to change those values as needed for other names. To get to meaningful numbers, we need to look at the String we're currently working with to determine those breaks. We'll need to get the length of the current String using Len() so we know the upper bound of the Right() and Left() functions.

The next thing we can do is loop through our String until we find where our names begin and end. Using this name as an example, we can identify this using some case checking (UCase()/LCase()) and the Mid() function. (Mind you, the real world is often not going to be as easy as this example, since so many people do not properly case when typing, but it's an idea to build on.)

Dim sUserName As String
Dim iStrLen As Integer, iLeftCount As Integer, iRightCount As Integer
Dim iLoopCount As Integer
Dim sCheckLetter As String
sUserName = "DoeJohn"
iStrLen = Len(sUserName)
iLeftCount = 0
iRightCount = 0
For iLoopCount = 2 To iStrLen
' Start at 2 to skip the first letter, since we already know this is the first letter of a name.
sCheckLetter = Mid(sUserName, iLoopCount, 1)
If sCheckLetter = UCase(sCheckLetter) Then
' We found a capital letter, which is one character past the name we want,
' so count back one space.
iLeftCount = iLoopCount - 1
' Using the same logic, count back from the end to get the other name.
iRightCount = iStrLen - (iLoopCount - 1)
' We've found the break, so we can skip looping through the rest.
Exit For
End If
Next iLoopCount
If iLeftCount <> 0 And iRightCount <> 0 Then
sUserName = Left(sUserName, iLeftCount) & ", " & Right(sUserName, iRightCount)
End If

---

Doing a bit more...
While using Mid() to break into the middle of a String at a particular point isn't all that difficult, it's also not that straight-forward. That's not to say VBA isn't good for doing things with strings, however.
Let's say you wanted to insert a character between each character of a String, like a new line (vbCr, vbLf, or vbCrLf, etc.). The looping method above can be tweaked to do this:

Example 1:

Dim sString As String
Dim sNewString As String
Dim iStrLen As Integer
Dim iLoopCount As Integer
sString = "Thisismystring"
sNewString = ""
iStrLen = Len(sString)
For iLoopCount = 1 To iStrLen
' Add the current character from the original String to the end of the new String.
sNewString = sNewString & Mid(sString, iLoopCount, 1)
' We will only add the new character IN the String, not at the end.
If iLoopCount < iStrLen Then
sNewString = sNewString & vbCr
End If
Next iLoopCount

Example 2:

Dim sString As String
Dim iStrLen As Integer
Dim iLoopCount As Integer
sString = "Thisismystring"
iStrLen = Len(sString)
' We will only add the new character IN the String, not at the end, so start on back from the end.
For iLoopCount = iStrLen - 1 To 1 Step -1
' Insert the new character into the original String
sString = Left(sString, iLoopCount) & vbCr & Mid(sString, iLoopCount + 1)
Next iLoopCount

Example 3 (using an array trick):

Dim sString As String
Dim iStrLen As Integer
Dim iLoopCount As Integer
sString = "Thisismystring"
iStrLen = Len(sString)
' We will only add the new character IN the String, not at the end, so start on back from the end.
For iLoopCount = iStrLen - 1 To 1 Step -1
' Insert the new character into the original String
sString = Left(sString, iLoopCount) & vbCr & Mid(sString, iLoopCount + 1)
Next iLoopCount

---

That's not by any means all you can do with Strings in VBA, but this should be helpful to some. If you are just getting started with VBA, I do recommend having a look at the link I mentioned above.