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