正则表达式VBA匹配

Question

如何在第二个msgbox中获得81.16的值?Regex只从字符串

中获取数值

Sub Tests()
    Const strTest As String = "<td align=""right"">116.83<span class=""up2""></span><br>81.16<span class=""dn2""></span></td>"
    RE6 strTest
End Sub

Function RE6(strData As String) As String
    Dim RE As Object, REMatches As Object
    Set RE = CreateObject("vbscript.regexp")
    With RE
        ' .MultiLine = True
        '.Global = False
        .Pattern = "b[d.]+b"
    End With
    Set REMatches = RE.Execute(strData)
    MsgBox REMatches(0)
    MsgBox REMatches(1)    'getting error here
End Function

Answer 1

首先，尽量不要使用RegEx解析任何类型的xml。尝试使用xml解析器或XPath

XPath是用于选择节点的查询语言从XML文档。此外，还可以使用XPath来计算值(例如，字符串、数字或布尔值)从XML的内容文档。

要解决问题，更改

'.Global = False 'Matches only first occurrence

到

.Global = True 'Matches all occurrences

如何正确解析VBA中的XML:

请注意，我必须关闭您的<br />标记才能有效。

Private Sub XmlTestSub()
On Error GoTo ErrorHandler
    Dim xml As MSXML2.DOMDocument60
    Dim nodes As MSXML2.IXMLDOMNodeList
    Dim node As MSXML2.IXMLDOMNode
    yourXmlString = "<td align=""right"">116.83<span class=""up2""></span><br />81.16<span class=""dn2""></span></td>"
    Set xml = New MSXML2.DOMDocument60
    If (Not xml.LoadXML(yourXmlString)) Then
        Err.Raise xml.parseError.ErrorCode, "XmlTestSub", xml.parseError.reason
    End If
    Set nodes = xml.SelectNodes("/td/text()") 'XPath Query
    For Each node In nodes
        Debug.Print node.NodeValue
    Next node
Done:
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & " " & Err.Description, vbCritical
    Resume Done
End Sub