正则表达式在VBA（Excel）中匹配 [英] Regex match in VBA (Excel)

问题描述

我正在尝试为excel 2010编写一个函数，该函数将采用一行HTML并返回链接地址（示例中为http://www.google.com）。每行都遵循以下确切模式：

I am trying to write a function for excel 2010 that will take a line of HTML and return the link address ("http://www.google.com" in the example). Every lines follows this exact pattern:

<a href="http://www.google.com"class="linkClass " >Google Link</a><br/>

我正在使用正则表达式来完成此操作。我能够使用在线正则表达式构建器构建此正则表达式。当我测试它时，这个表达式很好用：

I am trying to accomplish this using regular expressions. I was able to build this regular expression using an online regular expression builder. This expression works nicely when I tested it:

(?<=href\=)[^]+?(?=class)

这是我正在使用的VBA功能。我可以得到这个函数为其他模式工作，但它会为此模式引发错误。

Here is the VBA the function that I am using. I am able to get this function to work for other patterns but it throws an error for this pattern.

Function getLink(strData As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .pattern = "(?<=href\=)[^]+?(?=class)"
    End With
     Debug.Print RE.pattern
    Set REMatches = RE.Execute(strData)
    getLInk = REMatches(0)

End Function

为什么我的模式不能在我的getLink函数中工作？

Why doesn't my pattern work in my getLink function?

推荐答案

正则表达式的VBA实现不允许lookbehind，所以？<= $ / code>将无法正常工作。您显示的示例可以在大多数正则表达式中正常工作，但不能使用VBA / VB6。

The VBA implementation of regex does not allow lookbehind so ?<= will not work. The example you've shown will work fine in most regex flavours but not VBA/VB6.

通常使用VBA，您必须提供一些快捷方式才能实现您的需要，例如一些解决方法可能包括

Often with VBA you have to come up with a few shortcuts to achieve what you need, for example some workarounds could include

• 在阶段使用正则表达式


• 首先反转字符串，使用正则表达式进行匹配，然后反转结果


• 将正则表达式与替换方法组合

非常有用的这里& here

These articles are quite useful here & here

这将工作对于您给出的示例

This will work for the example you've given

Option Explicit

Sub test()
Dim testthis As String

 testthis = getLink("<a href=""http://www.google.com""class=""linkClass "" >Google Link</a><br/>")
End Sub

Function getLink(strData As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "href=.+?"""
    End With
     Debug.Print RE.Pattern
    Set REMatches = RE.Execute(strData)
    getLink = Replace(Replace(REMatches(0), """", ""), "href=", "")

End Function

有很多不同的方式来实现这一点，所以你可以找到一个更好的解决方案

There's quite a few different ways to achieve this so you may well find a better solution.

这篇关于正则表达式在VBA（Excel）中匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！