Regex as replace function
I am using Regular Expressions to for a replace function. I could not get
the following to work below so I changed the last line by using a Substitute function from Excel. Why isn't the line: 'tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) working? Public Function RegReplace(Pattern As String, SeriesAddress As String) As String 'Set Reference To Microsoft Scripiting Runtime Dim objRegExp As Object Dim Match Dim tempstring As String Set objRegExp = CreateObject("Vbscript.RegExp") objRegExp.IgnoreCase = IgnoreCase objRegExp.MultiLine = MultiLine objRegExp.Pattern = Pattern Set Match = objRegExp.Execute(SeriesAddress) 'tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) tempstring = WorksheetFunction.Substitute(SeriesAddress, SeriesAddress, Match(0).Value) RegReplace = tempstring End Function Thanks EM |
Regex as replace function
On Mon, 4 May 2009 13:01:02 -0700, Excel Monkey
wrote: I am using Regular Expressions to for a replace function. I could not get the following to work below so I changed the last line by using a Substitute function from Excel. Why isn't the line: 'tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) working? Public Function RegReplace(Pattern As String, SeriesAddress As String) As String 'Set Reference To Microsoft Scripiting Runtime Dim objRegExp As Object Dim Match Dim tempstring As String Set objRegExp = CreateObject("Vbscript.RegExp") objRegExp.IgnoreCase = IgnoreCase objRegExp.MultiLine = MultiLine objRegExp.Pattern = Pattern Set Match = objRegExp.Execute(SeriesAddress) 'tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) tempstring = WorksheetFunction.Substitute(SeriesAddress, SeriesAddress, Match(0).Value) RegReplace = tempstring End Function Thanks EM It's hard to tell exactly what you're doing. But you have a number of variables that are not declared. You are doing both an extraction and then a replacement, so your routine is more than just a simple use of the Replace method of the Regex object. It would be useful if you could supply the values for Pattern & SeriesAddress, as well as your expected result. You should also make it a habit to always have Option Explicit at the start of your modules, so that you will detect your undeclared variables. This can be automated by selecting Tools/Options/Editor and select "Require Variable Definition". --ron |
Regex as replace function
I turned Option Explicit on and changed some of my variables to Booleans.
I am just trying to parse out the file name from the network Path. The value of StringAddress is: ='\\CGAS114\Username\My Documents\The Folder\ExcelVBA\[TestFile.xls]Sheet1'!$A$2 The Pattern is: \[.+\] The line of code tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) shold be returning: [TestFile.xls] As ?Match(0).Value is[ListFunctionandSubs.xls] Below is the function again. Public Function RegReplace(Pattern As String, SeriesAddress As String) As String 'Set Reference To Microsoft Scripiting Runtime Dim objRegExp As Object Dim Match Dim tempstring As String Set objRegExp = CreateObject("Vbscript.RegExp") objRegExp.IgnoreCase = True ' Was IgnoreCase objRegExp.MultiLine = True 'Was MultiLine objRegExp.Pattern = Pattern Set Match = objRegExp.Execute(SeriesAddress) tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) 'tempstring = WorksheetFunction.Substitute(SeriesAddress, SeriesAddress, Match(0).Value) RegReplace = tempstring End Function Thanks "Ron Rosenfeld" wrote: On Mon, 4 May 2009 13:01:02 -0700, Excel Monkey wrote: I am using Regular Expressions to for a replace function. I could not get the following to work below so I changed the last line by using a Substitute function from Excel. Why isn't the line: 'tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) working? Public Function RegReplace(Pattern As String, SeriesAddress As String) As String 'Set Reference To Microsoft Scripiting Runtime Dim objRegExp As Object Dim Match Dim tempstring As String Set objRegExp = CreateObject("Vbscript.RegExp") objRegExp.IgnoreCase = IgnoreCase objRegExp.MultiLine = MultiLine objRegExp.Pattern = Pattern Set Match = objRegExp.Execute(SeriesAddress) 'tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) tempstring = WorksheetFunction.Substitute(SeriesAddress, SeriesAddress, Match(0).Value) RegReplace = tempstring End Function Thanks EM It's hard to tell exactly what you're doing. But you have a number of variables that are not declared. You are doing both an extraction and then a replacement, so your routine is more than just a simple use of the Replace method of the Regex object. It would be useful if you could supply the values for Pattern & SeriesAddress, as well as your expected result. You should also make it a habit to always have Option Explicit at the start of your modules, so that you will detect your undeclared variables. This can be automated by selecting Tools/Options/Editor and select "Require Variable Definition". --ron |
Regex as replace function
On Thu, 7 May 2009 07:36:06 -0700, ExcelMonkey
wrote: The value of StringAddress is: ='\\CGAS114\Username\My Documents\The Folder\ExcelVBA\[TestFile.xls]Sheet1'!$A$2 The Pattern is: \[.+\] The line of code tempstring = objRegExp.Replace(SeriesAddress, Match(0).Value) shold be returning: [TestFile.xls] This really is just a simple extraction. Given that you want to return the above, using your StringAddress and Pattern, then replace your "tempstring = ..." line with: tempstring = Match(0) --ron |
All times are GMT +1. The time now is 12:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com