CODEWITHSUNDEEP
regexexcelvba
user1665871
user1665871

Reputation: 13

Excel: Replacing strings in hyperlinks with regex by macro

I am trying to replace strings (for example all digits) in Excel 2003 (VB5.5) via macro. Now, I have this code:

Sub DeleteDigits()
Dim hLink As Hyperlink
Dim wSheet As Worksheet

For Each wSheet In Worksheets
    For Each hLink In wSheet.Hyperlinks
        hLink.Address = regEx.Replace(hLink.Address, "\d", "")
    Next hLink
Next wSheet
End Sub

I checked Regular Expressions in References, but I do not know how to get through the message that hLink.Address is not an object, which is the error I am getting.

Upvotes: 1

Views: 942

Answers (1)

JDB
JDB

Reputation: 25855

Replace doesn't support three arguments. You must set the pattern first via a property. Like so:

Sub DeleteDigits()
    Dim hLink As Hyperlink
    Dim wSheet As Worksheet
    Dim regEx As New VBScript_RegExp_55.RegExp

    For Each wSheet In Worksheets
        For Each hLink In wSheet.Hyperlinks
            regEx.Pattern = "\d"
            hLink.Address = regEx.Replace(hLink.Address, "")
        Next hLink
    Next wSheet
End Sub

EDIT:
Supporting documentation:

"Pattern - A string that is used to define the regular expression. This must be set before use of the regular expression object."
...
"Replace (search-string, replace-string) - The Replace method takes 2 strings as its arguments. If it is able to successfully match the regular expression in the search-string, then it replaces that match with the replace-string, and the new string is returned. If no matches were found, then the original search-string is returned."

http://msdn.microsoft.com/en-us/library/ms974570.aspx#scripting05_topic2

Upvotes: 1

Related Questions