E
EagleOne
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 <[email protected]>
'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)
**************************************************************************************************
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 <[email protected]>
'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)
**************************************************************************************************