2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

This is just an idea.

1. using SUBSTITUTE() replace + - / * ^ with a common symbol.
2. using SPLIT() separate the equation into pieces
3. using IsNumber() or IsNumeric() find the pieces that are numbers

--
Gary's Student


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

Interesting idea.

My idea was to LEN( Formula).
For/next loop the Formula string from Left to Right while incrementing the start point in Mid() till
Len(Formula).

That said, there are best ideas out there.

Thanks for your time/effort

EagleOne

Gary''s Student wrote:

This is just an idea.

1. using SUBSTITUTE() replace + - / * ^ with a common symbol.
2. using SPLIT() separate the equation into pieces
3. using IsNumber() or IsNumeric() find the pieces that are numbers

On Sat, 25 Nov 2006 02:37:07 GMT, wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne


Is your ROUND function supposed to be an actual Excel function? If so, the
syntax is wrong as you've left out the number of digits:

=ROUND(A1*0.035,num_digits).

Since num_digits will also be a constant, would you want that extracted also?
--ron

An excellent question. The answer is No to formula arguments. I need just the constants used by or
in the formula.

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 02:37:07 GMT, wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne


Is your ROUND function supposed to be an actual Excel function? If so, the
syntax is wrong as you've left out the number of digits:

=ROUND(A1*0.035,num_digits).

Since num_digits will also be a constant, would you want that extracted also?
--ron

On Sat, 25 Nov 2006 14:20:41 GMT, wrote:

An excellent question. The answer is No to formula arguments. I need just the constants used by or
in the formula.

That makes it very difficult.

Without that requirement, you could easily strip out the operators and cell
references by using Regular Expressions, and then return everything else that
is numeric.

With the requirement that numeric arguments to functions not be returned, such
as the type I mentioned, you would have to have some kind of table to analyze
each function. I can't think of any other way to, for example, given:

=ROUND(.035,1)

to extract the ".035" but not extract the "1"


--ron

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 14:20:41 GMT, wrote:

An excellent question. The answer is No to formula arguments. I need just the constants used by or
in the formula.

That makes it very difficult.

Without that requirement, you could easily strip out the operators and cell
references by using Regular Expressions, and then return everything else that
is numeric.

With the requirement that numeric arguments to functions not be returned, such
as the type I mentioned, you would have to have some kind of table to analyze
each function. I can't think of any other way to, for example, given:

=ROUND(.035,1)

to extract the ".035" but not extract the "1"


--ron

On Sat, 25 Nov 2006 16:11:13 GMT, wrote:

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

If I were going to do that, I think I would first replace every character that
is a function parameter with a nonsense string, perhaps a tilde ~.

Then replace all the cell references and operators with tilde's.

Then extract the numbers that remain.

I would use regular expressions to do all that.
--ron

I am in the process of doing just that, I'll post back when I get it.

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 16:11:13 GMT, wrote:

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

If I were going to do that, I think I would first replace every character that
is a function parameter with a nonsense string, perhaps a tilde ~.

Then replace all the cell references and operators with tilde's.

Then extract the numbers that remain.

I would use regular expressions to do all that.
--ron

Ron,

Here is what I came up with. Any thoughts for improvement appreciated.

For testing, the "formula" I used in "A1":
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(C1*3)

My results a
687319~523187~7344000~758450~2232642~1995819~27215 38~1491693~723564~3

Because I changed the operational signs to "~" I have lost positive vs negative numbers.

Can you think of a way to preserve the positive vs negative numbers?

EagleOne

Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 16:11:13 GMT, wrote:

Appreciate your time & knowledge. I agree, the challenge is awesome.

EagleOne

If I were going to do that, I think I would first replace every character that
is a function parameter with a nonsense string, perhaps a tilde ~.

Then replace all the cell references and operators with tilde's.

Then extract the numbers that remain.

I would use regular expressions to do all that.
--ron

On Sun, 26 Nov 2006 04:00:10 GMT, wrote:

Ron,

Could you be more specific with your comment:

"However, it would need to be extended to take care
of the worksheet function issue I raised earlier."

Other than my error with the Round worksheet function argument,
I am not sure what you mean.

Thanks, EagleOne

You mentioned in an earlier post that did NOT want to return the values that
were used as function criteria, such as num_digits in the ROUND function.

There are a number of functions with similar criteria.

For example, range_lookup in the various LOOKUP functions (which can be
expressed as 0, 1, TRUE or FALSE)

match_type in the MATCH function

decimals & no_commas in the FIXED function

etc.

There's no general rule of which I am aware that can differentiate a number
that is a function argument versus a number that the function is operating on.
So unless someone comes up with something, you'd have to look at every
function, and only return the value that is in the appropriate location.


--ron

Something like this ?

Sub extractConstants()
Dim range_to_check As Range
Dim re As New RegExp
Dim matches, match, i, c

Set range_to_check = [A1:A4] ' SET AS NEEDED

re.Global = True
re.Pattern = "[=(^*/+\-](-*\d*\.?\d+)"
For Each c In range_to_check
Set matches = re.Execute(c.Formula)
For Each match In matches
i = i + 1
Debug.Print i, match, match.SubMatches(0)
Next
Next
End Sub

--
Regards,
Luc.

"Festina Lente"


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

On Sat, 25 Nov 2006 21:45:02 -0800, PapaDos
wrote:

Something like this ?

Sub extractConstants()
Dim range_to_check As Range
Dim re As New RegExp
Dim matches, match, i, c

Set range_to_check = [A1:A4] ' SET AS NEEDED

re.Global = True
re.Pattern = "[=(^*/+\-](-*\d*\.?\d+)"
For Each c In range_to_check
Set matches = re.Execute(c.Formula)
For Each match In matches
i = i + 1
Debug.Print i, match, match.SubMatches(0)
Next
Next
End Sub

Nice regex. I was trying to think of something like that but could not.

But it fails on formulas such as:

=AVERAGE(1,2,3)

It also fails to return the negative values, as the OP mentioned in a
subsequent post.


--ron

PapaDos,

Code works great. I have not used VbScript 5.5 before.

Was this task just easier done in VbScript? If you have time could you share in 25 words or less
(To keep your time to a minimum) why VbScript? I realize I am exposing my ignorance.

Ron Rosenfeld also suggested VbScript 5.5

Thanks EagleOne

PapaDos wrote:

Something like this ?

Sub extractConstants()
Dim range_to_check As Range
Dim re As New RegExp
Dim matches, match, i, c

Set range_to_check = [A1:A4] ' SET AS NEEDED

re.Global = True
re.Pattern = "[=(^*/+\-](-*\d*\.?\d+)"
For Each c In range_to_check
Set matches = re.Execute(c.Formula)
For Each match In matches
i = i + 1
Debug.Print i, match, match.SubMatches(0)
Next
Next
End Sub

Ron,
I was able to use PapaDos code as to negatives via:

Range("G" & i).Value = IIf(Left(match, 1) = "-", match, match.SubMatches(0))

That said, I am also interested =AVERAGE(1,2,3)

Both you and PapaDos spent a great deal of time for which I am very appreciative.

EagleOne


Ron Rosenfeld wrote:

On Sat, 25 Nov 2006 21:45:02 -0800, PapaDos
wrote:

Something like this ?

Sub extractConstants()
Dim range_to_check As Range
Dim re As New RegExp
Dim matches, match, i, c

Set range_to_check = [A1:A4] ' SET AS NEEDED

re.Global = True
re.Pattern = "[=(^*/+\-](-*\d*\.?\d+)"
For Each c In range_to_check
Set matches = re.Execute(c.Formula)
For Each match In matches
i = i + 1
Debug.Print i, match, match.SubMatches(0)
Next
Next
End Sub

Nice regex. I was trying to think of something like that but could not.

But it fails on formulas such as:

=AVERAGE(1,2,3)

It also fails to return the negative values, as the OP mentioned in a
subsequent post.


--ron

On Sun, 26 Nov 2006 16:09:33 GMT, wrote:

PapaDos,

Code works great. I have not used VbScript 5.5 before.

Was this task just easier done in VbScript? If you have time could you share in 25 words or less
(To keep your time to a minimum) why VbScript? I realize I am exposing my ignorance.

Ron Rosenfeld also suggested VbScript 5.5

Thanks EagleOne


VbScript Regular Expressions is used in both our routines so we can use Regular
Expressions, which are a much more efficient method of handling this sort of
text manipulation.


--ron

Ron,

Where is good source information on Regular Expressions? When you mentioned it previous I thought
you were referring to some subset of VBA.

EagleOne

Ron Rosenfeld wrote:

On Sun, 26 Nov 2006 16:09:33 GMT, wrote:

PapaDos,

Code works great. I have not used VbScript 5.5 before.

Was this task just easier done in VbScript? If you have time could you share in 25 words or less
(To keep your time to a minimum) why VbScript? I realize I am exposing my ignorance.

Ron Rosenfeld also suggested VbScript 5.5

Thanks EagleOne


VbScript Regular Expressions is used in both our routines so we can use Regular
Expressions, which are a much more efficient method of handling this sort of
text manipulation.


--ron

Like you said before, it is probably not possible to adjust it for every
circumstances (Average(1,2,3) and so on...).

But it should pick up the negative values !
Any example where it failed to do so ?

I didn't allowed spaces in my first version, this Regexp shoud fix that:
re.Pattern = "[=(^*/+\-]\s*(-*\s*\d*\.?\d+)"


--
Regards,
Luc.

"Festina Lente"


"Ron Rosenfeld" wrote:

On Sat, 25 Nov 2006 21:45:02 -0800, PapaDos
wrote:

Something like this ?

Sub extractConstants()
Dim range_to_check As Range
Dim re As New RegExp
Dim matches, match, i, c

Set range_to_check = [A1:A4] ' SET AS NEEDED

re.Global = True
re.Pattern = "[=(^*/+\-](-*\d*\.?\d+)"
For Each c In range_to_check
Set matches = re.Execute(c.Formula)
For Each match In matches
i = i + 1
Debug.Print i, match, match.SubMatches(0)
Next
Next
End Sub

Nice regex. I was trying to think of something like that but could not.

But it fails on formulas such as:

=AVERAGE(1,2,3)

It also fails to return the negative values, as the OP mentioned in a
subsequent post.


--ron

On Sun, 26 Nov 2006 13:38:01 -0800, PapaDos
wrote:

Like you said before, it is probably not possible to adjust it for every
circumstances (Average(1,2,3) and so on...).

But it should pick up the negative values !
Any example where it failed to do so ?

I didn't allowed spaces in my first version, this Regexp shoud fix that:
re.Pattern = "[=(^*/+\-]\s*(-*\s*\d*\.?\d+)"

On the OP's first example:

1 =687319 687319
2 +523187 523187
3 -7344000 7344000
4 +758450 758450
5 +2232642 2232642
6 +1995819 1995819
7 -2721538 2721538
8 +1491693 1491693
9 +723564 723564
10 *3 3

Note that the results or your routine are unsigned.

In my algorithm, I handled that problem differently, and so was able to retain
the negative signs.

1 687319
2 523187
3 -7344000
4 758450
5 2232642
6 1995819
7 -2721538
8 1491693
9 723564
10 3


--ron

On Sun, 26 Nov 2006 13:38:01 -0800, PapaDos
wrote:

Like you said before, it is probably not possible to adjust it for every
circumstances (Average(1,2,3) and so on...).

But it should pick up the negative values !
Any example where it failed to do so ?

I didn't allowed spaces in my first version, this Regexp shoud fix that:
re.Pattern = "[=(^*/+\-]\s*(-*\s*\d*\.?\d+)"


Here's another simple function where your routine does not pick up anything:

=IF(G110000,C1,-10)

My approach picks up both:

1 10000
2 -10




--ron

On Sun, 26 Nov 2006 21:53:42 -0500, Ron Rosenfeld
wrote:

Note that the results or your routine are unsigned

That should read:

Note that the results of your routine are unsigned

--ron

LOL
That's a peculiar way to define a negative constant !
So X-Y means that Y is negative ?

Seriously, if that's what the OP wanted it could easily be fixed.
The "within string" occurences may very well be a problem too like in
MID("attention=2*too low",1,1)
--
Regards,
Luc.

"Festina Lente"


"Ron Rosenfeld" wrote:

On Sun, 26 Nov 2006 13:38:01 -0800, PapaDos
wrote:

Like you said before, it is probably not possible to adjust it for every
circumstances (Average(1,2,3) and so on...).

But it should pick up the negative values !
Any example where it failed to do so ?

I didn't allowed spaces in my first version, this Regexp shoud fix that:
re.Pattern = "[=(^*/+\-]\s*(-*\s*\d*\.?\d+)"

On the OP's first example:

1 =687319 687319
2 +523187 523187
3 -7344000 7344000
4 +758450 758450
5 +2232642 2232642
6 +1995819 1995819
7 -2721538 2721538
8 +1491693 1491693
9 +723564 723564
10 *3 3

Note that the results or your routine are unsigned.

In my algorithm, I handled that problem differently, and so was able to retain
the negative signs.

1 687319
2 523187
3 -7344000
4 758450
5 2232642
6 1995819
7 -2721538
8 1491693
9 723564
10 3


--ron

I think you should accept to match every "constants", even the "2" in
ROUND(a1,2).
Filtering out those is just about impossible...

Another problem, do we need to ignore string content, like in
=MID("this formula is 2*-3^PI",1,1)
??

And do you really want the results
2
-3
from
=A1+2-3
and not
2
3
??

--
Regards,
Luc.

"Festina Lente"


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

On Sun, 26 Nov 2006 20:18:01 -0800, PapaDos
wrote:

LOL
That's a peculiar way to define a negative constant !
So X-Y means that Y is negative ?

Seriously, if that's what the OP wanted it could easily be fixed.

This is what the OP wrote:
__________________________________________________ _____________________

"Because I changed the operational signs to "~" I have lost positive vs
negative numbers.

"Can you think of a way to preserve the positive vs negative numbers?"
__________________________________________________ _______________________

The "within string" occurences may very well be a problem too like in
MID("attention=2*too low",1,1)

Easily fixed if "within string" occurrences of numbers should be ignored.

In my algorithm, I would merely eliminate all within quotes, including the
quotes, by adding to my Ignore regex.


--ron

One more thing, how about percentages (%) ?
What should be the results from
=A1*3%
=A1%
--
Regards,
Luc.

"Festina Lente"


" wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne

The OP's routine would eliminate the negative from
=a1*-3
but I am not sure what he wants exactly from
=A1-3
....
--
Regards,
Luc.

"Festina Lente"


"Ron Rosenfeld" wrote:

On Sun, 26 Nov 2006 20:18:01 -0800, PapaDos
wrote:

LOL
That's a peculiar way to define a negative constant !
So X-Y means that Y is negative ?

Seriously, if that's what the OP wanted it could easily be fixed.

This is what the OP wrote:
__________________________________________________ _____________________

"Because I changed the operational signs to "~" I have lost positive vs
negative numbers.

"Can you think of a way to preserve the positive vs negative numbers?"
__________________________________________________ _______________________

The "within string" occurences may very well be a problem too like in
MID("attention=2*too low",1,1)

Easily fixed if "within string" occurrences of numbers should be ignored.

In my algorithm, I would merely eliminate all within quotes, including the
quotes, by adding to my Ignore regex.


--ron

On Sat, 25 Nov 2006 02:37:07 GMT, wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells elsewhere on the w/s?

TIA EagleOne


Here's a somewhat cleaned up and better annotated version.

With regard to some of the issues:
it returns the negative signed values
it does NOT return "within string" constants
it returns 3% as 3
it returns all constants within a function
the output goes into the cells to the right of "selection"


============================================
Option Explicit
Sub ParseFormula()
'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

Dim FormulaText As String
Const CellRef As String = "\$?[A-Z]{1,2}\$?\d{1,5}"
Const Operator As String = "[/*^&()=<,+]" 'No "-"
Const WithinString As String = """[^""]*"""
Const NumConstant As String = "-?(\d*\.)?\d+"
Dim Ignore As String
Dim i As Long
Dim c As Range

Ignore = Join(Array(WithinString, CellRef, Operator), "|")

'set the Regex pattern to replace unwanted stuff
objRegExp.pattern = Join(Array(WithinString, CellRef, Operator), "|")

For Each c In Selection
FormulaText = c.Formula

'Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

'change - to +- to retain negative signage
FormulaText = Replace(FormulaText, "-", "+-")

'replace unwanted stuff
objRegExp.pattern = Ignore
FormulaText = objRegExp.Replace(FormulaText, "~")

'Get the matches.
objRegExp.pattern = NumConstant
Set colMatches = objRegExp.Execute(FormulaText) ' Execute search.

End If

'Output for testing, but could go into any range
i = 1
For Each objMatch In colMatches
c.Offset(0, i).Value = objMatch
i = i + 1
Next objMatch

Next c

End Sub
===============================
--ron

Ron & PapaDos,

After using the different routines, and if I may be so bold:

What works best is that the output should be pure constants used to change cell values and not
"constants" within functions. The reason that I added (A1*3) was to make sure that I could get to
the multiplier "3" [when the OP used parenthesis to make the formula more obvious].

Actually, it would be best for the routine to ignore within-function arguements or settings.

Specifically, any numbers added, subtracted, divided, multiplied

Users have a bad habit of not identifying the pieces-parts of cell values, i.e.
Sales A Division 687319
Sales B Division 523187
Expenses A Division -7344000
Sales A Division 758450
etc, etc

My plan is to place the output of your routines into separate cells on a new worksheet so that each
constant is completely separated ready to be properly labeled and identified.

Therefore, I need to be able to

1) spin out all constants which are effectively hidden in formula strings (but not within functions
or worksheet links etc)
2) Spin any included function or link out to the same worksheet as a string

Sales A Division 687319
Sales B Division 523187
Expenses A Division -7344000
Sales A Division 758450
etc, etc xxxxxx
'='Sheet1'!A2 999000
Total xxxxxxxxxx
[This total should equal
the displayed value in
the active cell )

I have no references nor information on how to modify/create Regular Expressions

Thanks so much for you time and knowledge

EagleOne


wrote:

2003

If I have a "formulas" like:
=687319+523187-7344000+758450+2232642+1995819-2721538+1491693+723564+(A1*3) 'Need all constants
including the "3" in (A1*3) parsed to cells Z1 thru Z10

-or-

=Round(A1*0.035) ' Need the.35 parsed to Z11

-or-

=P39/$C40*12-P39 'Need the "12" parsed to Z13

-or-

=1000/2 Need 1000 parsed to Z14 and 2 parsed to Z15

I have thought of Cells.Find; Mid(), but what is the smartest lines of VBA code to evaluate formulas
for constants whether + - * / and then parse those constants to cells e and lsewhere on the w/s?

TIA EagleOne

On Mon, 27 Nov 2006 16:12:53 GMT, wrote:

1) spin out all constants which are effectively hidden in formula strings (but not within functions
or worksheet links etc)


So, with regard to the formula:

=ROUND(0.035,1)

what, exactly do you want?

And the references (I'm not sure why they did not come through earlier):


http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn.microsoft.com/library/de...63906a7353.asp



--ron

Ron,

For ROUND(0.035,1) I do not need either of those constants nor do I need the constants in
Average[1,2,3]

Maybe another way to thing of it might me only the full string of numbers after each operator and
the equal sign like "+/-=*". I believe that will give me just the constants which should have been
in a summed worksheet list with labels.

It gets a bit more dicey when IF(X0, A1*3,A1). In this case, I want the 3 but not the 0. But the
3 should be picked up by "the full string of numbers after each operator"

What I realized later is that if the formula had four constants and a link, then a sum of all of the
constants would not add up to the original cell value in this case.

The correct cell value would be the sum of the four constants AND the value of the linked cell.

Because this gets real complicated, I thought that if all functions and the with-in constants would
be appended to the list as its own label ROUND(999.99,2).

Therefore,
= 1+2+3+4+Round(999.99,2) 'Would yield

Description
Item A 1
Item B 2
Item C 3
Item D 4
'Round(999.99,2) 1000 'if complicated to ascertain function's value, leave the value blank
Total 1010

Hopefully, I am clearer now. I'll be close by for the next 6 hours.


Ron Rosenfeld wrote:

On Mon, 27 Nov 2006 16:12:53 GMT, wrote:

1) spin out all constants which are effectively hidden in formula strings (but not within functions
or worksheet links etc)


So, with regard to the formula:

=ROUND(0.035,1)

what, exactly do you want?

And the references (I'm not sure why they did not come through earlier):


http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn.microsoft.com/library/de...63906a7353.asp



--ron

On Mon, 27 Nov 2006 18:11:58 GMT, wrote:

We're getting closer to defining what you want.


Ron,

For ROUND(0.035,1) I do not need either of those constants nor do I need the constants in
Average[1,2,3]

Maybe another way to thing of it might me only the full string of numbers after each operator and
the equal sign like "+/-=*". I believe that will give me just the constants which should have been
in a summed worksheet list with labels.

It gets a bit more dicey when IF(X0, A1*3,A1). In this case, I want the 3 but not the 0. But the
3 should be picked up by "the full string of numbers after each operator"

What I realized later is that if the formula had four constants and a link, then a sum of all of the
constants would not add up to the original cell value in this case.

The correct cell value would be the sum of the four constants AND the value of the linked cell.

Because this gets real complicated, I thought that if all functions and the with-in constants would
be appended to the list as its own label ROUND(999.99,2).

Therefore,
= 1+2+3+4+Round(999.99,2) 'Would yield

Description
Item A 1
Item B 2
Item C 3
Item D 4
'Round(999.99,2) 1000 'if complicated to ascertain function's value, leave the value blank
Total 1010

Hopefully, I am clearer now. I'll be close by for the next 6 hours.


Aside from the fact that ROUND(999.99,2) -- 999.99 and not 1000, there remains
a problem in differentiating functions from which you want to extract constants
from those you don't wish to execute an extraction.

IF(X0,A1*3,A1)

you write extract the 3 using the "number string after operator algorithm"/

BUT that fails with

IF(X0,3*A1,A1)

or even

IF(X0,3,1)

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

It seems to me it would be a whole lot simpler to develop a user form that
would ensure that entries are made properly.




--ron

On Tue, 28 Nov 2006 21:49:02 GMT, wrote:

Ron,

Included is the current form of your code. I used this all day and it works astoundingly well. The
other issues I was able to obtain from alternate sources.

Thank you for your time, efforts and knowledge.

I must learn much more about Regular Expressions. It really continues where VBA is weak.

Can you provide me with links to the best Regular Expressions sites?

EagleOne




Well I'm glad I was able to help you with your problem. Thanks for the
feedback.

The web references I use a

http://www.regular-expressions.info/reference.html
http://support.microsoft.com/default...02&Product=vbb
http://msdn.microsoft.com/library/de...63906a7353.asp


I would also recommend: Jeffrey Friedl's book 'Mastering Regular Expressions,
2nd Ed.', ISBN 0-596-00289-0.

It was recommended to me by Harlan Grove. And I was able to obtain a used
version on Amazon.com for just a few dollars. It's very good.

Best wishes,
--ron

On 30 Nov 2006 03:57:28 -0800, "Dennis" wrote:

Ron,

If you are still in the mood. Attached is some code that I am
currently using for a huge project and the deadline is tomorrow..

The code is part of the same project with which you helped me earlier.
Here is my whine with cheese.

The code works fine until I attempt to replace i.e. a "2" in formulas
where there are multiple "2's". a specific example is: =4237987/2 or
=122335/2. What I want to replace is the 2 after the operator "/"
(where the 2 is the ENTIRE NUMBER after ANY operator. I think that
those parameters will keep me out of trouble.

Currently, I trap that error and "mark" the cell for manual followup
later.

Would you please help me to devise

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 his within the script.

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.

If that doesn't work, and you must test for ONLY operators, then change two
lines:

Pattern = "([-+/*])" & NumToReplace & "\b"

FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplWith)

================================
Option Explicit

Sub replConstant()
Dim FormulaText As String
Const NumToReplace = 2
Const ReplWith = 4
Dim Pattern As String

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Pattern = "\b" & NumToReplace & "\b"
FormulaText = Selection.Formula

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = True

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

'Do the replacement
FormulaText = objRegExp.Replace(FormulaText, ReplWith)

Debug.Print FormulaText

End If

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

Hope this helps


--ron

After work today, I'll take a look at the code you sent.

Your help has been a God-send for me this week.

Eagle-one


Ron Rosenfeld wrote:
On 30 Nov 2006 03:57:28 -0800, "Dennis" wrote:

Ron,

If you are still in the mood. Attached is some code that I am
currently using for a huge project and the deadline is tomorrow..

The code is part of the same project with which you helped me earlier.
Here is my whine with cheese.

The code works fine until I attempt to replace i.e. a "2" in formulas
where there are multiple "2's". a specific example is: =4237987/2 or
=122335/2. What I want to replace is the 2 after the operator "/"
(where the 2 is the ENTIRE NUMBER after ANY operator. I think that
those parameters will keep me out of trouble.

Currently, I trap that error and "mark" the cell for manual followup
later.

Would you please help me to devise

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 his within the script.

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.

If that doesn't work, and you must test for ONLY operators, then change two
lines:

Pattern = "([-+/*])" & NumToReplace & "\b"

FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplWith)

================================
Option Explicit

Sub replConstant()
Dim FormulaText As String
Const NumToReplace = 2
Const ReplWith = 4
Dim Pattern As String

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Pattern = "\b" & NumToReplace & "\b"
FormulaText = Selection.Formula

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = True

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

'Do the replacement
FormulaText = objRegExp.Replace(FormulaText, ReplWith)

Debug.Print FormulaText

End If

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

Hope this helps


--ron

I could not wait!

How do I handle:

Const NumToReplace = 2
Const ReplWith = 4

These need to be variables determined in subsequent code.

So do I:

Const NumToReplace As Double (needs "=" to something)
Const ReplWith As Double (needs "=" to something)

-or-

Dim NumToReplace as String (or Double or Long)
Dim NumToReplace as String (or Double or Long)

EagleOne


Dennis wrote:
After work today, I'll take a look at the code you sent.

Your help has been a God-send for me this week.

Eagle-one


Ron Rosenfeld wrote:
On 30 Nov 2006 03:57:28 -0800, "Dennis" wrote:

Ron,

If you are still in the mood. Attached is some code that I am
currently using for a huge project and the deadline is tomorrow..

The code is part of the same project with which you helped me earlier.
Here is my whine with cheese.

The code works fine until I attempt to replace i.e. a "2" in formulas
where there are multiple "2's". a specific example is: =4237987/2 or
=122335/2. What I want to replace is the 2 after the operator "/"
(where the 2 is the ENTIRE NUMBER after ANY operator. I think that
those parameters will keep me out of trouble.

Currently, I trap that error and "mark" the cell for manual followup
later.

Would you please help me to devise

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 his within the script.

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.

If that doesn't work, and you must test for ONLY operators, then change two
lines:

Pattern = "([-+/*])" & NumToReplace & "\b"

FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplWith)

================================
Option Explicit

Sub replConstant()
Dim FormulaText As String
Const NumToReplace = 2
Const ReplWith = 4
Dim Pattern As String

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Pattern = "\b" & NumToReplace & "\b"
FormulaText = Selection.Formula

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = True

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(FormulaText) = True) Then

'Do the replacement
FormulaText = objRegExp.Replace(FormulaText, ReplWith)

Debug.Print FormulaText

End If

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

Hope this helps


--ron

On 30 Nov 2006 07:39:39 -0800, "Dennis" wrote:

I could not wait!

How do I handle:

Const NumToReplace = 2
Const ReplWith = 4

These need to be variables determined in subsequent code.

So do I:

Const NumToReplace As Double (needs "=" to something)
Const ReplWith As Double (needs "=" to something)

-or-

Dim NumToReplace as String (or Double or Long)
Dim NumToReplace as String (or Double or Long)

EagleOne

Well, I didn't know how you were 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.


--ron

Intrigued by your code, I just said the hell with it and just
experimented. Then walla! I got to your answer at 2:15PM EST.

As you can tell, I am "new" to this. Even though I know the difference
between a constant and variable, I did not realize that Const was just
short for Constant begging Public & Private issues. I even thought it
was short for Construct what ever that is. Just making mountains out of
mole hills when 20/20 hindsight is available.

Thanks again

EagleOne



Ron Rosenfeld wrote:
On 30 Nov 2006 07:39:39 -0800, "Dennis" wrote:

I could not wait!

How do I handle:

Const NumToReplace = 2
Const ReplWith = 4

These need to be variables determined in subsequent code.

So do I:

Const NumToReplace As Double (needs "=" to something)
Const ReplWith As Double (needs "=" to something)

-or-

Dim NumToReplace as String (or Double or Long)
Dim NumToReplace as String (or Double or Long)

EagleOne

Well, I didn't know how you were 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.


--ron

On 30 Nov 2006 11:22:12 -0800, "Dennis" wrote:

Intrigued by your code, I just said the hell with it and just
experimented. Then walla! I got to your answer at 2:15PM EST.

As you can tell, I am "new" to this. Even though I know the difference
between a constant and variable, I did not realize that Const was just
short for Constant begging Public & Private issues. I even thought it
was short for Construct what ever that is. Just making mountains out of
mole hills when 20/20 hindsight is available.

Thanks again

EagleOne

When you have a word in a module that requires further explanation, just put
your cursor in the word, and hit F1. HELP should open to explain it. That
will work for the standard VBA stuff; it doesn't work for the Regular
Expression stuff, though.

Best,
--ron

On 1 Dec 2006 14:23:53 -0800, "Dennis" wrote:

Yep,

My real name is Dennis. I use EagleOne for all the spamers.

Ron, I have constructed the suggested pattern: -?\b64596792\b for the
negative number 64596792 and it still will not replace.

I also tried ?\b64596792\b. Does something else have to change?

Actually, given the data you posted, in which the negative number is not the
first number, either Pattern will work because there is a \b prior to the "-".

This Sub seems to replicate what you have posted for data, and works to do the
substitution.

==================================================
Option Explicit
Sub ReplaceNeg()
Dim FormulaText As String
Dim Pattern As String
Dim ReplaceWith As String

Dim objRegExp As Object

Set objRegExp = New RegExp

FormulaText = "-'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246"
Pattern = "b-64596792\b"

objRegExp.Pattern = Pattern

'ReplaceWith = Replace(MyCell.Offset(0, 3).Formula, "=", "")
ReplaceWith = "'Constants Input'!$D$245"

If (objRegExp.Test(FormulaText) = True) Then 'Does Pass as True but:
Debug.Print "Original", FormulaText

FormulaText = objRegExp.Replace(FormulaText, ReplaceWith)
Debug.Print "Replaced", FormulaText

End If

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

Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1'Constants
Input'!$D$245+59410246

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

Your negative number is replaced by ReplaceWith.

Of course, this demonstrates the issue of replacing a signed negative number
with some other constant. You have stated that you want the signed number.
But the "-" is really an operator. So when you do the replacement with a cell
reference, there is no operator, and the result has a flawed syntax.

If you want to retain the "-" in the replacement, you need to do things a bit
differently.

You could test for just the number without the sign:

Pattern = "\b64596792\b"

Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-'Constants
Input'!$D$245+59410246

OR, if there is some reason you must have that "-" in the expression, you could
capture it and return it in the ReplaceWith string:

Pattern = "(-?)\b64596792\b"
' Note the parentheses around the -? to "capture" it.

FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplaceWith)
' the "$1" represents the first captured item.


Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-'Constants
Input'!$D$245+59410246

If the value in 'Constants Input'!$D$245 is a negative number, then you'd want
to replace the "-" with a "+", or change the sign of that cell.
--ron

Ron,

In the example, the minus sign is not transferred.

In the interim, I use an input box and flip the sign to positive.
Then, the following:

ReplaceWith = Replace(FormulaText, "=", "```")

If (objRegExp.Test(FormulaText) = True) Then
TempText = objRegExp.Replace(FormulaText, ReplaceWith)
TempText = IIf(NumbToReplace < 0, Replace(TempText, "-```", "+"), _
Replace(TempText, "```", "+"))
ActiveCell.formula = TempText
End if

Thanks for you knowledge and time.

Dennis

Ron Rosenfeld wrote:
On 1 Dec 2006 14:23:53 -0800, "Dennis" wrote:

Yep,

My real name is Dennis. I use EagleOne for all the spamers.

Ron, I have constructed the suggested pattern: -?\b64596792\b for the
negative number 64596792 and it still will not replace.

I also tried ?\b64596792\b. Does something else have to change?

Actually, given the data you posted, in which the negative number is not the
first number, either Pattern will work because there is a \b prior to the "-".

This Sub seems to replicate what you have posted for data, and works to do the
substitution.

==================================================
Option Explicit
Sub ReplaceNeg()
Dim FormulaText As String
Dim Pattern As String
Dim ReplaceWith As String

Dim objRegExp As Object

Set objRegExp = New RegExp

FormulaText = "-'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246"
Pattern = "b-64596792\b"

objRegExp.Pattern = Pattern

'ReplaceWith = Replace(MyCell.Offset(0, 3).Formula, "=", "")
ReplaceWith = "'Constants Input'!$D$245"

If (objRegExp.Test(FormulaText) = True) Then 'Does Pass as True but:
Debug.Print "Original", FormulaText

FormulaText = objRegExp.Replace(FormulaText, ReplaceWith)
Debug.Print "Replaced", FormulaText

End If

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

Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1'Constants
Input'!$D$245+59410246

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

Your negative number is replaced by ReplaceWith.

Of course, this demonstrates the issue of replacing a signed negative number
with some other constant. You have stated that you want the signed number.
But the "-" is really an operator. So when you do the replacement with a cell
reference, there is no operator, and the result has a flawed syntax.

If you want to retain the "-" in the replacement, you need to do things a bit
differently.

You could test for just the number without the sign:

Pattern = "\b64596792\b"

Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-'Constants
Input'!$D$245+59410246

OR, if there is some reason you must have that "-" in the expression, you could
capture it and return it in the ReplaceWith string:

Pattern = "(-?)\b64596792\b"
' Note the parentheses around the -? to "capture" it.

FormulaText = objRegExp.Replace(FormulaText, "$1" & ReplaceWith)
' the "$1" represents the first captured item.


Original -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-64596792+59410246
Replaced -'C:\Acctg\FIT\[Accum Temp.xls]2005'!$A$1-'Constants
Input'!$D$245+59410246

If the value in 'Constants Input'!$D$245 is a negative number, then you'd want
to replace the "-" with a "+", or change the sign of that cell.
--ron