"String" manipulation for a Case clause

[Eagl...@discussions.microsoft.com]

2003, 2007

What is the smartest way to be able to keep a "." (period) or a number from an cell address or
worksheet name/link or a workbook name/link being considered in the Case line below:

Dim CheckStr as string

CheckStr = Activecell.formula
TestChar = Mid(CheckStr, Counter, 1)
TestAsc = Asc(TestChar)
.....
' Is current character a Number or a "." (period)?
Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46

...... <Do things>

I need to consider the complete formula string so that I can obtain the starting position of every
constant (defined as a number preceeded by a mathmatical operator) in the formula string. So I
cannot delete anything from CheckStr.

In short, I do not want the numbers from a cell address, a sheetname, workbook name or from a
directory link to be considered.

For example, in the formula below:
=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+[C:\123]'Closing'!E31

I do not want the "." or 26 or 31 or 31 or 123 to be considered "acceptable" by:

Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46

My thoughts have included but not limited to:

1) ActiveWorkbook.LinkSources(xlExcelLinks)
2) Toggling Booleen True/False for alternate " ' " in the formula string (elim w/s references)
3) Toggling Booleen True/False for alternate "[" then "]" for links

Bottom line the VBA code to effectively:

Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
(except "." and numbers which are not constants)

Hopefully there is an easy way to do this that I have not considered. ??

TIA EagleOne

[Jim Cone]

Not exactly clear, but try this...
(and "\" is an arithmetic operator)
---
Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M > 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub

--
Jim Cone
Portland, Oregon USA

<Eagl...@discussions.microsoft.com>
wrote in message

[Eagl...@discussions.microsoft.com]

Some progress below:

Assuming that CheckStr = "='Min. Int.'!D18-362.33"

The following code deletes Worksheet references and links from CheckStr2 and yields "=D18-362.33"
which represents the remaining Constants and Cell references.

CheckStr2 = CheckStr2
Set Wks = Nothing
For Each Wks In Worksheets
CheckStr2 = Replace(CheckStr2, "'" + Wks.Name + "'!", "")
CheckStr2 = Replace(CheckStr2, Wks.Name + "!", "")
Next Wks
On Error Resume Next
'Delete links from CheckStr2
Do
LinkStart = WorksheetFunction.Find("'", CheckStr2, 1)
' Intention below find " ' " plus link plus " '!" thus + 2
LinkEnd = WorksheetFunction.Find("'", CheckStr2, LinkStart + 1) + 2
LinkLength = LinkEnd - LinkStart
LinkStringToDelete = Trim(Mid(CheckStr2, LinkStart, LinkLength))
CheckStr2 = Trim(Replace(CheckStr2, LinkStringToDelete, ""))
Loop While WorksheetFunction.Find("'", CheckStr2)

Need help to programmatically remove all Cell references in CheckStr2 (in this case "D18") ?

Then I was going to do some kind of analysis of CheckStr and CheckStr2 like checking
InStr(1,CheckStr2,TestChar,xlTextCompare) > 0 vs ?? InStr(1,CheckStr2,TestChar,xlTextCompare) > 0
This is where my progress stops

Any thoughts appreciated

TIA EagleOne

[Eagl...@discussions.microsoft.com]

Jim, I saw your response just after my additional information.

Does my additional info change your code?

Thank you so much for your time and knowledge!

[Jim Cone]

I don' know; you were talking operator constants and now cell references?
My head hurts. The code is self contained - just run it.

--
Jim Cone
Portland, Oregon USA

<Eagl...@discussions.microsoft.com>
wrote in message

[Eagl...@discussions.microsoft.com]

Jim, I have to integrate the code in the morning. I'll let you know tomorrow morning.

I do not wish any numeric's other than numeric constants.

=-'Min. Int.'!F26-'Min. Int.'!F31+28038.35+[C:\123]'Closing'!E31+ A1

From Cell Refs, worksheet Tab names or filepath link info to be in the subset of allowable
characters. DO NOT want the numerals in sheetnames or the inclusive"." Periods or 26 or 123 or 31
or A1to be considered "acceptable".

I DO want the 28038.35 and the "+" sign infront of it . Hope that I have been clear!

Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46
.

[Ron Rosenfeld]

I'm not quite sure exactly what you want to do.

But if you have defined the value that you wish to extract from the string as
being any numeric value that is preceded by an arithmetic operator, then the
following code is one example of how to do that, using Regular Expressions:

===========================
Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"
If re.test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub
========================================
--ron

[Eagl...@discussions.microsoft.com]

Jim, your code is very helpful. I have modified it slightly to add a second element to the array to
capture the character position in the strArr.

I am not sure how to correctly ReDim Preserve the new 2 element strArr. Currently, I get a
"Subscript out of range" the way it is. I tried ReDim Preserve(1 To x).elements(1 to 2) but it
failed.

Sub FigureItOut()
Dim N As Long
Dim M As Long
Dim x As Long
Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String

ReDim strArr(1 To 100, 1 To 2) 'Note the 2nd element

'some extras in the string
strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M > 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then
strWhat = Mid$(strGiven, M, 2)
x = x + 1

strArr(x, 1) = strWhat
strArr(x, 2) = M + 1 'Note the 2nd element

End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x) 'This line fails because I added a 2nd element
For x = 0 To UBound(strArr)
Debug.Print strArr(x)
Next
End Sub

Except for the "\1", which I do not want, your code gives me the "Operator" and the first digit and
now its position, in the strGiven. As each TestChar approaches the Case below I could test if
TestChar IsNumeric(strArr(x)) AND if it is within the position-range of the entire Numeric.

[Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46]

The challenge is the code as written only captures the first digit after Operator.

At the end of the day, I would like only the numbers and "." for each true numeric constant in
strGiven to pass to the Case above. In strGiven the only true Numeric constants preceded by an
Operator are:

Begining Full Numeric
1st Digit
-9 9
-7 7
-6 6
+2 28038
^3 35
^1 1
\1 <I'll remove this from consideration> 123

Is there any easy way to capture the full numeric constant value?

Thank you again for your help!

Recapping:
1) How to correctly write the ReDim Preserve (with two elements)?
2) How to capture the full Numeric Constant in lieu of only 1st digit?
3) Any ideas on how to limit the strGiven characters to sucessfully pass to
the Case TestAsc >= 48 And TestAsc <= 57 Or TestAsc = 46

EagleOne

[Eagl...@discussions.microsoft.com]

Ron, thank you.

[Eagl...@discussions.microsoft.com]

Got a bit further by capturing the full Numeric Constant string with:
*****************************************************************************

Sub FigureItOut()
Dim N As Long
Dim M As Long

Dim X As Long
Dim A As Long

Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String

ReDim strArr(1 To 100, 1 To 3)

'some extras in the string

strGiven = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"

'vThings = Array("-", "+", "^", "\", "/", "*")

vThings = Array("-", "+", "^", "/", "*")

M = 0
For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M > 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then

A = M + 1

strWhat = Mid$(strGiven, M, 2)

Do
strWhat = strWhat & IIf(Mid$(strGiven, A + 1, 1) Like "#" Or _
Mid$(strGiven, A + 1, 1) = ".", Mid$(strGiven, A + 1, 1), "")
A = A + 1
Loop While Mid$(strGiven, A + 1, 1) Like "#" Or Mid$(strGiven, A + 1, 1) = "."
X = X + 1
strArr(X, 1) = strWhat
strArr(X, 2) = M
strArr(X, 3) = Len(strArr(X, 1))
Debug.Print "CharPlusSign: "; strArr(X, 1) & Space(5) & "StartPosInStr: " & _
strArr(X, 2) & Space(5) & "StrLength: " & strArr(X, 3)

End If
Else
Exit Do
End If
Loop
Next

Stop
ReDim Preserve strArr(1 To X)
'Range("A1", Cells(1, x)).Value = strArr()
For X = 0 To UBound(strArr)
Debug.Print strArr(X)
Next
End Sub
*****************************************************************************

Still need how to correctly write ReDim Preserve strArr(1 To X).elements( 1 to 3)???

[Jim Cone]

You can only "preserve" the last dimension of an array.
I recommend you see if Ron Rosenfeld's regular expressions code
does what you want before going further down the looping path.

--
Jim Cone
Portland, Oregon USA

<Eagl...@discussions.microsoft.com>
wrote in message

[Eagl...@discussions.microsoft.com]

I real stupid question, I have been able to use array elements (I think) within the same procedure
w/o "Preserving".

The latest "myRevision" of your code gives me the info I need as I believe than Ron's code will give
me the numeric constants but not the position in the strGiven.

As you can tell I am a newbie to Arrays, actually VBA.

Thank you very much for your help. For me at my age I learn new areas best when I must deal with it
in a real challenge. Reading about VBA flies thru my brain unless I can embellish code which works.

BTW, I posted an additional quested re "Preserving" in between my work and your response which I
appreciate..

[Eagl...@discussions.microsoft.com]

Love the code; but very weak in RegEx.

That said, your code works fine.

CharPlusSign: -9 StartPosInStr: 1 StrLength: 2
CharPlusSign: -7 StartPosInStr: 24 StrLength: 2
CharPlusSign: -6 StartPosInStr: 62 StrLength: 2
CharPlusSign: +28038.66 StartPosInStr: 36 StrLength: 9
CharPlusSign: ^35 StartPosInStr: 45 StrLength: 3
CharPlusSign: ^1 StartPosInStr: 71 StrLength: 2

I used your code to get:
9
7
28038.66
35
1

Your code as I used it:

Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[-+/*^](\b\d*\.?\d+\b)"

If re.Test(str) = True Then

Set mc = re.Execute(str)
For Each m In mc
Debug.Print m.SubMatches(0)
Next m
End If
End Sub

Sub Test()
Dim str As String
str = "-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
ExtrConstants (str)
End Sub

Is there a way to include the sign, numeric constant and additionally
the position in the string and its Length?

[Jim Cone]

With a 2 dimensional array, you can only resize & preserve the second dimension.
So with. arr(1 to 100, 1 to 3) - you cannot change the 1 to 100 part.
'---
Sub FigureItOut_R1()

Dim N As Long
Dim M As Long
Dim x As Long

Dim i As Long
Dim lngLength As Long

Dim strWhat As String
Dim strGiven As String
Dim vThings As Variant
Dim strArr() As String
ReDim strArr(1 To 100)

strGiven = _

"-9'Min. Int.'!F26-'Min.-7 Int.'!F31+28038.66^35+[C:\123]'Clos-6ing'!E3^1"
vThings = Array("-", "+", "^", "\", "/", "*")

strGiven = strGiven & " "
lngLength = Len(strGiven)

M = 0

For N = 0 To UBound(vThings)
Do
M = InStr(M + 1, strGiven, vThings(N), vbBinaryCompare)
If M > 0 Then
If Mid$(strGiven, M + 1, 1) Like "#" Then

For i = M + 2 To lngLength
If Not Mid(strGiven, i, 1) Like "[0-9.]" Then
' strWhat = Mid$(strGiven, M + 1, i - (M + 1))
strWhat = Mid$(strGiven, M, i - M) & Chr$(10) & _
"Len: " & i - M & Chr$(10) & "Pos: " & M
Exit For
End If
Next

x = x + 1
strArr(x) = strWhat
End If
Else
Exit Do
End If
Loop
Next

ReDim Preserve strArr(1 To x)
Range("A1", Cells(1, x)).Value = strArr()
End Sub

--
Jim Cone
Portland, Oregon USA

<Eagl...@discussions.microsoft.com>
wrote in message

[Eagl...@discussions.microsoft.com]

It is a pleasure to see how well a professional can make VBA concise and efficient!

Thanks Jim

[Ron Rosenfeld]

If what you mean is to replicate what you have above, then yes.

However, please confirm that you do NOT want the "-6" to be returned. That
does not look like a constant to me, but rather a worksheet name.

Also, in your example above, there is no "=" prior to the -9. If that will be
an option, one further modification is needed.

====================

Option Explicit
Private Sub ExtrConstants(str As String)
Dim re As Object, mc As Object, m As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True

re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b"

If re.Test(str) = True Then
Set mc = re.Execute(str)
For Each m In mc

Debug.Print "CharPlusSign: " & m.Value, _
"StartPosInStr: " & m.FirstIndex + 1, _
"StrLength: " & m.Length

Next m
End If
End Sub

===================================

Returns (using your Sub Test()):

CharPlusSign: -9 StartPosInStr: 1 StrLength: 2
CharPlusSign: -7 StartPosInStr: 24 StrLength: 2

CharPlusSign: +28038.66 StartPosInStr: 36 StrLength: 9
CharPlusSign: ^35 StartPosInStr: 45 StrLength: 3
CharPlusSign: ^1 StartPosInStr: 71 StrLength: 2

--ron

[Eagl...@discussions.microsoft.com]

Excellent catch! I do not know how I missed that.

That string was originally conceived as:
"=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+'Min. Int.'!E31"

Then stuff was added to test for odd-ball situations. That said,
I should have caught it

As always, you are outstanding with RegEx.

Thanks Ron

p.s. Consider checking back if I have any questions.

[Ron Rosenfeld]

On Fri, 08 May 2009 17:29:12 -0400, Eagl...@discussions.microsoft.com wrote:

>Excellent catch! I do not know how I missed that.
>
>That string was originally conceived as:
>"=-'Min. Int.'!F26-'Min. Int.'!F31+2803835+'Min. Int.'!E31"
>
>Then stuff was added to test for odd-ball situations. That said,
>I should have caught it
>
>As always, you are outstanding with RegEx.
>
>Thanks Ron
>
>p.s. Consider checking back if I have any questions.
>

You're welcome. Thanks for the feedback.

My newsreader should pick up any additions to this thread, so if you have more
questions, try to keep it in the thread.

Best wishes,
--ron

[Eagl...@discussions.microsoft.com]

Works great!

[Eagl...@discussions.microsoft.com]

Ron,

When you have time, would you mind discussing the components of the pattern line? i.e.
The only recognizable aspect to me are the "signs"/operators [-+/*^=]

're.Pattern = "[-+/*^](\b\d*\.?\d+\b)" 'Previous version
re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b" ' Adds (^| ) and "="

Changes noted (^| = ) )

Also, what pattern adds (does not add) the "signs?

I love this "Regex"

TIA EagleOne

[Eagl...@discussions.microsoft.com]

Ron,

I recalled this information from a few years ago. I present it only for your ease.

Is there anything you would like to add/amplify re the current subject?

TIA EagleOne

*****************************************************************************************
'From: Ron Rosenfeld <ronrosenf...@nospam.org>
'Subject: Re: How in to parse constants in formula to cells
'Date: Thu, 30 Nov 2006 07:48:11 -0500
'Newsgroups: microsoft.public.Excel.programming
'
'"Well, the simplest way (for me) to do that sort of replacement is by using
'Regular Expressions. The routines work quicker if you set a reference to
'Microsoft VBScrip REgular Expressions 5.5 (See Tools/References), but you can
'also use the CreateObject method to set this within the script.
'"Like"
'
''Set up Regex
'
' Dim objRegExp As RegExp
' Dim objMatch As match
' Dim colMatches As MatchCollection
'
''Create a regular expression object.
' Set objRegExp = New RegExp
''Set Case Insensitivity.
' objRegExp.IgnoreCase = True
''Set global applicability.
' objRegExp.Global = True
'
'The "key" as to what is going to be replaced is in how you define "Pattern".
'As set up, the "\b" parameter refers to any word boundary, which could be an
'operator, comma, or any character that is not alphanumeric, or the beginning or
'end of the string.
'
'I don't know how you are planning to input those variables, so I just
'put them in as Constants for testing purposes. You can Dim them and then set
'them to whatever, however you wish.
'
'With the Const statement, the type declaration is optional. But if the values
'were always going to be integers, I would use the Long type; if they might be
'decimal numbers, I would use Double or String.
'
'The "\b" token picks up a word boundary. A word boundary is defined as a
'change from a word character [A-Za-z0-9_] and a few other characters to a
'non-word character.
'
'Since the operators are not word characters, the pattern \b-64596792\b will not
'pick up the "-" as there is no "\b" prior to the "-". By definition, there
'cannot be. The first word boundary in the string "-64596792" is between the
' "-" and the first digit "6".
'
'This only becomes a problem when you specify the "-" as part of a signed
'number, rather than as an operator.
'
'Depending on precise requirements, you could just make the "-" optional, and
'construct a pattern like:
'
' -?\b64596792\b
'
'If that doesn't work, and you must test for ONLY operators, then change two
'lines: (Note that below the "+" sign is to the right of "-" which for this
'is an arguement to a command not an operator.)
'
'Pattern = "([-=+/*])" & NumToReplace & "\b" -or-
'Pattern = "(-?)\b" & NumToReplace & "\b"
' (Note the parentheses around the -? to "capture" it.)
'
'FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplWith)"
' (The "$1" represents the first captured item.)
'
'But I don't understand why you can't just ignore the "-". In other words, why
'can't you treat the "-" as an operator rather than as denoting the sign of the
'constant?
'
'If the reason has to do with how you generate "Pattern", like from your Input
'Box, you could just strip it off by using the ABS function:
'
'=======================
'Do While NumbToReplace = 0
' NumbToReplace = ABS(InputBox("Enter number to _
' replace", "Replace Entry Box"))
' If NumbToReplace = 0 Then
' MsgBox "No Number Entered ... Start Over"
' End If
' Loop
' Pattern = "\b" & NumbToReplace & "\b"
' objRegExp.Pattern = Pattern
'==============================

'If you need to specify that NumbToReplace must follow an operator or an "="
'sign or a comma (for example), then:
'
'Pattern = "([-*/+=,])" & NumbToReplace & "\b"
'
'Note that the Character Class containing the operators and other characters is
'enclosed in parentheses. That will be "captured" and can be referenced in the
'Replace statement, so the same operator is also replaced:
'
'e.g.:
'
'FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplaceWith)
**************************************************************************************************

[Ron Rosenfeld]

On Sat, 09 May 2009 10:51:20 -0400, Eagl...@discussions.microsoft.com wrote:

>re.Pattern = "(^|[-+/*^=])\b\d*\.?\d+\b"

Here is a formal explanation:

======================================
Extract Constants

(^|[-+/*^=])\b\d*\.?\d+\b

Match the regular expression below and capture its match into backreference
number 1 �(^|[-+/*^=])�
Match either the regular expression below (attempting the next alternative
only if this one fails) �^�
Assert position at the beginning of the string �^�
Or match regular expression number 2 below (the entire group fails if this
one fails to match) �[-+/*^=]�
Match a single character present in the list �-+/*^=� �[-+/*^=]�
Assert position at a word boundary �\b�
Match a single digit 0..9 �\d*�
Between zero and unlimited times, as many times as possible, giving back as
needed (greedy) �*�
Match the character �.� literally �\.?�
Between zero and one times, as many times as possible, giving back as needed
(greedy) �?�
Match a single digit 0..9 �\d+�
Between one and unlimited times, as many times as possible, giving back as
needed (greedy) �+�
Assert position at a word boundary �\b�

Created with RegexBuddy
======================================

We are not using the backreferences. The parentheses are used for grouping
purposes.

A "word boundary" is a position that is between a "word character" and a
"non-word character". Word charactes include [A-Za-z0-9_]

Some of these references may be helpful (some may not still be valid, though):

Regular Expressions
http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx
http://msdn2.microsoft.com/en-us/library/ms974619.aspx
http://www.regex-guru.info/
--ron

[Ron Rosenfeld]

On Sat, 09 May 2009 13:51:20 -0400, Eagl...@discussions.microsoft.com wrote:

>Ron,
>
>I recalled this information from a few years ago. I present it only for your ease.
>
>Is there anything you would like to add/amplify re the current subject?
>
>TIA EagleOne

Not really.

I think the current routine works better for extracting constants.

Every so often I work on a routine to parse formulas correctly, but it is still
a work in progress.
--ron

[Eagl...@discussions.microsoft.com]

Your comment "A work in progress" is extremely interesting to me.

Silly me, I thought I was close that goal - with 100% your help even!

What are some examples/situations your are struggling with?

In short, I do not know what I do not know. I thought I was near heaven.

EagleOne

[Eagl...@discussions.microsoft.com]

Excellent! Thanks

[Ron Rosenfeld]

On Sun, 10 May 2009 13:11:42 -0400, Eagl...@discussions.microsoft.com wrote:

>Your comment "A work in progress" is extremely interesting to me.
>
>Silly me, I thought I was close that goal - with 100% your help even!
>
>What are some examples/situations your are struggling with?
>
>In short, I do not know what I do not know. I thought I was near heaven.
>
>EagleOne

Well, in addition to parsing out constants and operators, you would also need
to parse out Functions; Names; Function arguments; cell addresses and ranges in
a variety of formats; handle arrays properly; and so forth.
--ron

[Eagl...@discussions.microsoft.com]

My needs "might" be a bit more limited. I do realize that Excel, VBA, UDF can manipulate values.

That said I need to be able to find undocumented (almost always, unlabeled additions, subtractions
and/or * or / of same) in formula strings. The user then needs to document those constants.

Obviously my game plan can be made difficult by those sophisticated accountants who may manipulate
values in hidden cells, hidden formulas, Interior.ColorIndex = Font.ColorIndex, ranges, sheets or
functions.

I have proceedures to catch all the above except for functions.

Below are some notes from your prior work that I collected; with current comments:

'From: Ron Rosenfeld <ronros...@nospam.org>

'Subject: Re: How in to parse constants in formula to cells

'Date: Mon, 27 Nov 2006 09:05:29 -0500
'Newsgroups: microsoft.public.Excel.programming
'With regard to some of the issues:
' it returns the negative signed values
' it does NOT return "within string" constants
Here I think you mean the formula string Yes/No?
or do you mean "123456" within a Function?
i.e. VLookup
' it returns 3% as 3
' it returns all constants within a function
This is OK as surprises can occur as a result

Realizing that I am now to be a dead man resulting from my limited perspective, what have I missed?
Gulp!

EagleOne

[Ron Rosenfeld]

On Sun, 10 May 2009 15:02:15 -0400, Eagl...@discussions.microsoft.com wrote:

> 'From: Ron Rosenfeld <ronros...@nospam.org>
> 'Subject: Re: How in to parse constants in formula to cells
> 'Date: Mon, 27 Nov 2006 09:05:29 -0500
> 'Newsgroups: microsoft.public.Excel.programming
> 'With regard to some of the issues:
> ' it returns the negative signed values
> ' it does NOT return "within string" constants
> Here I think you mean the formula string Yes/No?
> or do you mean "123456" within a Function?
> i.e. VLookup

It's been a long time since I've worked on this routine. But, off the top of
my head, this routine will not return anything from a formula of the type:

="1/2/09"-"4/6/08"

It also needs to be able to handle NAME'd constants.
--ron

[Eagl...@discussions.microsoft.com]

That is what I thought and that is fine for my purposes.

Again, thank you so much for sharing your wealth re Regex + other thoughts.

EagleOne

[Eagl...@discussions.microsoft.com]

Ron,

For the heck of it:

Starting with: Pattern = (^|[-+/*^=])\b\d*\.?\d+\b

Is there a modification to the pattern which would eliminate all Functions from consideration?

EagleOne

[Ron Rosenfeld]

On Sun, 10 May 2009 17:49:43 -0400, Eagl...@discussions.microsoft.com wrote:

>Ron,
>
>For the heck of it:
>
>Starting with: Pattern = (^|[-+/*^=])\b\d*\.?\d+\b
>
>Is there a modification to the pattern which would eliminate all Functions from consideration?
>
>EagleOne

I can't think of one off-hand. Probably you'd need a list of functions to
start with, and then look for paired parentheses. Then remove those substrings
from the original. You'll have to deal with nesting also.
--ron

[Eagl...@discussions.microsoft.com]

Well it was thought. Heck it could have been ~{/ !