Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
|
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#36
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#37
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#38
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#39
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
#40
Posted to microsoft.public.excel.programming
|
|||
|
|||
How in to parse constants in formula to cells
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 |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula constants? | Excel Worksheet Functions | |||
Copying formula cells that really ought to be constants? | Excel Programming | |||
How do I capitalize and parse in the same formula? | Excel Worksheet Functions | |||
How do you copy a formula without incrementing some constants? | Excel Worksheet Functions | |||
Parse formula | Excel Programming |