On Wed, 13 Apr 2011 00:57:27 -0700, "Robert Crandal" wrote:

The variable "n" will be used to denote the midpoint
of my string. Starting at position "n", I need to
traverse the string backwards (or LEFT) and find
the position of the first whitespace character.

Does VBA have a function that does this??
I'm also not sure which function determines if
a character is a whitespace (ie, space, tab, etc...)

Robert Crandal


VBA does not have a shortcut for a whitespace character although it does have an Instrrev function.

You can use character classes but not with Instrrev. You can use it with the Like operator.

So to look for white space backwards in a string, using native VBA, you could use something like:

================================
Sub foo()
Const StringCheck As String = "ABC DEF xyz*ghi "

'The character class contains <space<tab<nbsp

Const WhiteSpace As String = "[ *]"
Dim i As Long

Debug.Print InStrRev(StringCheck, WhiteSpace)

For i = Len(StringCheck) To 1 Step -1
If Mid(StringCheck, i, 1) Like WhiteSpace Then
Debug.Print i
End If
Next i

End Sub
===================================

Or you could use Regular Expressions in VBA. For some reason, the whitespace shorthand does not include the nbsp, so that needs to be added separately if needed;

=================================
Sub WhiteSpaceReverseSearch()
Const StringCheck As String = "ABC DEF xyz*ghi "
Const sPat As String = "[\s\xA0]"
Dim re As Object, mc As Object, m As Object
Dim i As Long
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

If re.test(StringCheck) Then
Set mc = re.Execute(StringCheck)

'matches locations listed in order
For Each m In mc
Debug.Print m.firstindex + 1
Next m
Debug.Print vbLf

'match locations listed in reverse
For i = mc.Count To 1 Step -1
Debug.Print mc(i - 1).firstindex + 1
Next i

End If

End Sub
===========================

Perhaps with more detail about what you are trying to accomplish, a better solution can be devised.