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

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.

In message of Wed, 13
Apr 2011 00:57:27 in microsoft.public.excel.programming, Robert Crandal
writes
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...)

I would very much doubt that VBA has such a function.
OTOH, I think it can probably be done in several ways.
I would use Regular expressions for such complicated requirements
The hard thing is getting tab and other non space characters into
strings. I leave generating data to the OP.
He might try playing with this in the debugger.

Option Explicit ' Require all variables to be explicitly declared.

Sub last_whitespace()
Dim S As String
Dim n As Integer

S = "a b c" & Chr(9) & "defghijk" ' Data to analyse
n = Len(S) / 2
Dim S2 As String
S2 = Left(S, n) ' Copy of left half of data
Dim RE As Object
Set RE = CreateObject("VBScript.Regexp")
RE.Pattern = "^(.*[ " & Chr(9) & "]).*$"
' That RE is a sequence of
' ^ Beginning of input
' ( Start remembering match
' .* Any character except a newline 0 or more times
' [ chr(9)] Either a space or a tab
' ) end of match
' .*$ The rest of the input
If Not RE.test(S2) Then
MsgBox "No whitespace found"
Else
MsgBox "Last whitespace at " & Len(RE.Replace(S2, "$1"))
' $1 is the remembered match. Everything else is discarded.
End If
'
End Sub

--
Walter Briscoe

On Wed, 13 Apr 2011 12:53:51 +0100, Walter Briscoe wrote:

In message of Wed, 13
Apr 2011 00:57:27 in microsoft.public.excel.programming, Robert Crandal
writes
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...)

I would very much doubt that VBA has such a function.
OTOH, I think it can probably be done in several ways.
I would use Regular expressions for such complicated requirements
The hard thing is getting tab and other non space characters into
strings. I leave generating data to the OP.
He might try playing with this in the debugger.

Option Explicit ' Require all variables to be explicitly declared.

Sub last_whitespace()
Dim S As String
Dim n As Integer

S = "a b c" & Chr(9) & "defghijk" ' Data to analyse
n = Len(S) / 2
Dim S2 As String
S2 = Left(S, n) ' Copy of left half of data
Dim RE As Object
Set RE = CreateObject("VBScript.Regexp")
RE.Pattern = "^(.*[ " & Chr(9) & "]).*$"
' That RE is a sequence of
' ^ Beginning of input
' ( Start remembering match
' .* Any character except a newline 0 or more times
' [ chr(9)] Either a space or a tab
' ) end of match
' .*$ The rest of the input
If Not RE.test(S2) Then
MsgBox "No whitespace found"
Else
MsgBox "Last whitespace at " & Len(RE.Replace(S2, "$1"))
' $1 is the remembered match. Everything else is discarded.
End If
'
End Sub

Walter,

Two points:

\s is a shortcut for white space in this flavor and is equivalent to [ \f\n\r\t\v] (note the <space at the start)

It does not include the non-break space <nbsp but you could just set

re.pattern = "[\s\xA0]" to achieve the same thing.

The "Match" has a property called FirstIndex which is a count of all the characters to the left of the match.

To return just the right most location of white space, something like this might be a bit simpler:

=================
Option Explicit
Function LastWhiteSpace(StringCheck As String)
Const sPat As String = "[\s\xA0]"
Dim re As Object, mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

If re.test(StringCheck) Then
Set mc = re.Execute(StringCheck)
LastWhiteSpace = mc(mc.Count - 1).firstindex + 1
End If

End Function
=========================

In message of Wed, 13 Apr
2011 09:58:38 in microsoft.public.excel.programming, Ron Rosenfeld
writes
On Wed, 13 Apr 2011 12:53:51 +0100, Walter Briscoe
wrote:

In message of Wed, 13
Apr 2011 00:57:27 in microsoft.public.excel.programming, Robert Crandal
writes
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...)

I would very much doubt that VBA has such a function.
OTOH, I think it can probably be done in several ways.
I would use Regular expressions for such complicated requirements

[snipped my proposed code]


Walter,

Two points:

\s is a shortcut for white space in this flavor and is equivalent to [
\f\n\r\t\v] (note the <space at the start)

I had forgotten that.


It does not include the non-break space <nbsp but you could just set

re.pattern = "[\s\xA0]" to achieve the same thing.

The "Match" has a property called FirstIndex which is a count of all
the characters to the left of the match.

And that. ;)


To return just the right most location of white space, something like
this might be a bit simpler:

=================
Option Explicit
Function LastWhiteSpace(StringCheck As String)
Const sPat As String = "[\s\xA0]"
Dim re As Object, mc As Object

Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

The use of Global and execute to get the last match is magical.


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

The OP's requirement might be better served with something like
Set mc = re.Execute(left(StringCheck,len(StringCheck)/2))

LastWhiteSpace = mc(mc.Count - 1).firstindex + 1
End If

End Function
=========================

I find I do not like Execute. I probably use Replace more than I should
as I encapsulate it in:

Option Explicit ' Force data declaration

Dim RE As Object

Private Sub EnsureREInitialized()
If RE Is Nothing Then
Set RE = CreateObject("VBScript.Regexp")
RE.Global = True
End If
End Sub

Private Function GetSub(ByVal from As String, ByVal Match As String, _
ByVal Part As String) As String
EnsureREInitialized
RE.Pattern = Match
GetSub = RE.Replace(from, Part)
End Function

I might prefer RE to be a static variable, but then would not have an
easy access to Set RE = Nothing

I also encapsulate test with
Private Function IsMatch(ByVal from As String, ByVal Match As String) _
As Boolean
EnsureREInitialized
RE.Pattern = Match
IsMatch = RE.test(from)
End Function

I have yet to evolve a one liner using execute.

These are typical uses of those encapsulations.
If IsMatch(S, "^00:00 .+\r\n00:00 .+$") Then
O.innerhtml = GetSub(O.innerhtml, "<IMG (?:border=0 )?alt=(?:""([^""]+)""|(\w+)) src=[^]+", " $1$2;")

When looking for typical uses, I found some uses of getsub which should
be replaced by IsMatch. My code is never perfect. ;)

P.S.
Can anyone here recommend a public forum to discuss html?
I find Microsoft's offerings in place of Usenet unattractive.

I find the DOM (Document Object Model) for a site I use is completely
different in IE9 than IE8. e.g. <http://www.tfl.gov.uk/tfl/livetravelnew
s/realtime/track.aspx?offset=7
I can scrape data from this in IE8, but have yet to find why the model
is radically different in IE9. I installed IE9, hit a wall I could not
easily climb, and restored IE8.
I suppose I ought to put 8 and 9 on two machines and compare.
That is probably something to do on holiday.
--
Walter Briscoe

On Wed, 13 Apr 2011 18:11:46 +0100, Walter Briscoe wrote:

I find I do not like Execute. I probably use Replace more than I should
as I encapsulate it in:

Yes, I have a group regex functions in a personal add-in which can be called similarly for my own uses. But when responding here, I usually provide routines which are just focused on the primary task.

And in my own work, I find using early binding is more convenient, especially since I get those little reminders as to what all the valid arguments might be.