Here are a couple ways of looping through a string, or reading a string from a Macro.

To use either example, simply replace the text that the variable named LookInHere is equal to. There’s a comment in the code to give you a hint.

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.

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.

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