VBA – Loop Through a String
In this Article
This tutorial will demonstrate how to loop through a string using VBA.
You can perform logic on, or return individual characters from a string in VBA by looping through the string.
Loop Through Each Character in a String
The following is an example of looping through a string using a For…Next Loop, and returning each character in a msgbox.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub LoopThroughString() Dim Counter As Integer Dim MyString As String MyString = "AutomateExcel" 'define string For Counter = 1 To Len(MyString) 'do something to each character in string 'here we'll msgbox each character MsgBox Mid(MyString, Counter, 1) Next End Sub |
The Len Function counts the total number of characters in the string. So the expression
|
1 |
For Counter = 1 to Len(MyString |
will loop through each letter in the string.
Loop Through Each Character in a String – Alternative
Read Every Character in a String
This example reads every character in a string from left to right and returns the result in a message box. It makes use of the Mid Function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub LoopThroughString() Dim LookInHere As String Dim Counter As Integer 'Use your own text here LookInHere = "AutomateExcel.com" For Counter = 1 To Len(LookInHere) MsgBox Mid(LookInHere, Counter, 1) Next End Sub |
Read Every Word in a String
This example reads every word in a string from left to right and returns the result in a message box. It makes use of the Split Function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub LoopThroughString2() Dim LookInHere As String Dim Counter As Integer Dim SplitCatcher As Variant 'Use your own text here LookInHere = "I Heart AutomateExcel.com" SplitCatcher = Split(LookInHere, " ") For Counter = 0 To UBound(SplitCatcher) MsgBox SplitCatcher(Counter) Next End Sub |
VBA Coding Made Easy
Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
