Regex as replace function

[Excel Monkey]

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

 

[Ron Rosenfeld]

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

 

[ExcelMonkey]

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]

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