Loop Through a String - VBA Code Examples

VBA – Loop Through a String

In this Article

Loop Through Each Character in a String
Loop Through Each Character in a String – Alternative
Read Every Word in a String
VBA Coding Made Easy

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)

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)

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)

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!