vba的like和正则表达式

like的详细用法

Sub aa()
    a= "aBBBa"Like "a*a"					'''true
    a = "asd" Like "a?"                     '''false
    a = "asd" Like "[agjs3]*"               '''true ,[ABCDEGHIJ2345]=[A-EG-J2-5]，
    a = "asd" Like "asD"                    '''false
    a = "a11" Like "[a-z][0-9]#"            '''true,# = [0-9],[a-c] is OK,[ca] is OK,[c-a] is NG
    a = "a11" Like "[!c-z]11"               '''true,! is not
    a = ",?*#-" Like "[,][?][*][#][2-]"     '''true,",?*#-" in [] will match itself
    a = ",?*#-" Like "[1-2]"                '''false,[-] must putted By side
    a = "][" Like "[]][[]"                  '''true
    a = "D" Like "[A-a]"                    '''true,perhaps [A-a]=[A-Za]
    a = "d" Like "[A-a]"                    '''false,[a-A] is NG
End Sub

正则表达式

1. runoo+b，可以匹配 runoob、runooob、runoooooob 等，+ 号代表前面的字符必须至少出现一次（1次或多次）。
2. runoob，可以匹配 runob、runoob、runoooooob 等， 号代表字符可以不出现，也可以出现一次或者多次（0次、或1次、或多次）。
3. colou?r 可以匹配 color 或者 colour，? 问号代表前面的字符最多只可以出现一次（0次、或1次）。

• . 匹配除换行符以外的任意字符
• \w 匹配字母或数字或下划线或汉字 等价于 ‘[A-Za-z0-9_]’。大写取反
• \s 匹配任意的空白符。大写取反
• \d 匹配数字。大写取反
• \b 匹配单词的开始或结束
• ^ 匹配字符串的开始
• $ 匹配字符串的结束
• \w能不能匹配汉字要视你的操作系统和你的应用环境而定

  Sub testREGExecute()
    Dim reg, k, mh, strA$
    strA = "画面概要:iphone_5s;画面概要:Nokia_1020"
    Set reg = CreateObject("vbscript.regexp")
    reg.Pattern = "\w+"
    reg.Global = True
    Set mh = reg.Execute(strA)
    Debug.Print mh.Count               '2,if can not find,mh.count=0,
    For Each mhk In mh
        'mhk:{firstindex:index(starts from 0),value:match string,length:mhk.value.length}
        Debug.Print mhk.Value           'iphone_5s,Nokia_1020
    Next
End Sub

Public Function GetWebTxt(ByVal url As String) As String
    Dim xmlHttp As Object
    Application.DisplayAlerts = False
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP")
    xmlHttp.Open "GET", url, False
    'xmlHttp.setRequestHeader "Content-Type", "text/xml"
    xmlHttp.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
    xmlHttp.Send
    While xmlHttp.readystate <> 4
        DoEvents            '''let system operator do other thing '''转让控制权，以便让操作系统处理其它事件。
    Wend
    GetWebTxt = xmlHttp.responsetext
End Function

Sub testfindAllByReg()
strA = GetWebTxt("https://bbs.hupu.com/34914799-2.html")
Set matchList= findAllByReg(strA, "class=""floor""[\s\S]*?uname=""([\s\S]*?)""[\s\S]*?<table[\s\S]*?td>([\s\S]*?)</td>[\s\S]*?</table")
    For Each mhk In matchList
        'mhk:{firstindex:index from 0,value:match string,length:mhk.value.length}
        For Each sm In mhk.submatches       'for each loop print all submatches
            Debug.Print sm
        Next
    Next
End Sub


Function findAllByReg(ByVal text$, Optional ByVal pattern$, Optional ByVal reg = Empty)
    If reg = Empty Then
        Set reg = CreateObject("vbscript.regexp")
        'reg.pattern = "class=""floor""[\s\S]*?uname=""([\s\S]*?)""[\s\S]*?<table[\s\S]*?td>([\s\S]*?)</td>[\s\S]*?</table"
        reg.pattern = pattern
        reg.Global = True
        reg.IgnoreCase = True               'setting true will Ignore case
        'reg.MultiLine = False              '更改^和$的含義，分別在任意一行的行首和行尾匹配，而不only在整个字符串的start和end匹配
    End If
    'text = readtxt1("C:\Users\Administrator\Desktop\new3.txt")
    'text = GetWebTxt("https://bbs.hupu.com/34914799-2.html")

    Set findAllByReg = reg.Execute(text)
    'Debug.Print findAllByReg.Count               '2,if can not find,mh.count=0,
    'For Each mhk In findAllByReg
        'mhk:{firstindex:index from 0,value:match string,length:mhk.value.length}
        'For Each sm In mhk.submatches       'for each loop print all submatches
            'Debug.Print sm
        'Next
    'Next
End Function

'https://bbs.hupu.com/34914799-2.html的部分数据如下：
<div id="162051" class="floor">
<div class="floor-show  ">
<div class="user" >
<div class="j_u" uid="193001006372078" uname="一坨大团团呀">
<a class="headpic" target="_blank" href="https://my.hupu.com/193001006372078"><img src="https://i1.hoopchina.com.cn/user/default_small.jpg" width="45" height="45" alt="一坨大团团呀"></a>
</div>
</div>
<div class="floor_box ">
<div class="author">
<div class="left">
<a class="u" target="_blank" href="https://my.hupu.com/193001006372078">一坨大团团呀</a><span class="f666">&nbsp;(<a title="一坨大团团呀的帖子" target="_blank" href="https://my.hupu.com/193001006372078/topic">10级</a>)</span>&nbsp;<span class="stime">2020-04-20 11:39</span>&nbsp;<span class="f666 162051 button-light-inner " pid="162051" uid="193001006372078" style="position:relative">
</span>
<span class="f444 162051" pid="162051" uid="193001006372078" style="position:relative">
<span class="ilike_icon_list"><a class="ilike_icon " mid="1" href="javascript:;" style="width:25px;" dace_clk="clk_bbs_rlights">亮了</a>
(<span class="stime">7</span>)</span>
</span>
</div>
<div class="right f666">
<a href="javascript:void(0);" onclick="report(8,162051);return false;">举报</a>
<a href="/34914799_193001006372078.html" rel="nofollow">只看此人</a>
<a id="28" class="floornum" title="直达本楼回帖的链接" href="/34914799-2.html#162051">28楼</a>
</div>
</div>
<table class="case" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td>
<p>快递行业中转管理 快递真的是暴利，也很暴力</p>

<br />
</td>
</tr>
</tbody>
</table>