This works and is simple:
([A-Z])\w*
VBA requires these flag settings:
Global = True 'Match all occurrences not just first
IgnoreCase = False 'First word of each sentence starts with a capital letter
Here's some additional hard-earned info: since your regex has at least one parenthesis set, you can use Submatches to pull out only the values in the parenthesis and ignore the rest - very useful. Here is the debug output of a function I use to get Submatches, run on your string:
theMatches.Count=5
Match='He'
Submatch Count=1
Submatch='H'
Match='Then'
Submatch Count=1
Submatch='T'
Match='The'
Submatch Count=1
Submatch='T'
Match='How'
Submatch Count=1
Submatch='H'
Match='I'
Submatch Count=1
Submatch='I'
T
Here's the call to my function that returned the above:
sText = "He hit the ball. Then he ran. The crowd was cheering! How did he feel? I felt so energized!"
sRegEx = "([A-Z])\w*"
Debug.Print ExecuteRegexCapture(sText, sRegEx, 2, 0) '3rd match, 1st Submatch
And here's the function:
'Returns Submatch specified by the passed zero-based indices:
'iMatch is which match you want,
'iSubmatch is the index within the match of the parenthesis
'containing the desired results.
Function ExecuteRegexCapture(sStringToSearch, sRegEx, iMatch, iSubmatch)
Dim oRegex As Object
Set oRegex = New RegExp
oRegex.Pattern = sRegEx
oRegex.Global = True 'True = find all matches, not just first
oRegex.IgnoreCase = False
oRegex.Multiline = True 'True = [\r\n] matches across line breaks, e.g. "([\r\n].*)" will match next line + anything on it
bDebug = True
ExecuteRegexCapture = ""
Set theMatches = oRegex.Execute(sStringToSearch)
If bDebug Then Debug.Print "theMatches.Count=" & theMatches.Count
For i = 0 To theMatches.Count - 1
If bDebug Then Debug.Print "Match='" & theMatches(i) & "'"
If bDebug Then Debug.Print " Submatch Count=" & theMatches(i).SubMatches.Count
For j = 0 To theMatches(i).SubMatches.Count - 1
If bDebug Then Debug.Print " Submatch='" & theMatches(i).SubMatches(j) & "'"
Next j
Next i
If bDebug Then Debug.Print ""
If iMatch < theMatches.Count Then
If iSubmatch < theMatches(iMatch).SubMatches.Count Then
ExecuteRegexCapture = theMatches(iMatch).SubMatches(iSubmatch)
End If
End If
End Function