How to insert a regular expression check in a VBA script for excel?

Question

Programming

Hello. Perhaps I’ll go right away, in general, there is a script (I didn’t write it, they helped) which, on a certain basis (operator codes that are indicated in the array), separates mobile numbers in the table from city numbers. The script works fine, but because it relies on parentheses around the code (050), sometimes false positives happen. I would like to ask for help from those who understand VBA, please help me remake the script so that it detects numbers not by brackets, but by the following regular expression:

\b\(?(039|050|063|066|067|068|091|092|093|094|095|096|097|098|099)\)?\s?\-?\d{3}\s?\-?\d{2}\s?\-?\d{2}\b

The only thing, I'm not entirely sure about the correctness of the regular expression, it seems I made a mistake with the line ending (\b).
The script itself:

Public Sub QWERT()
Dim R, C, i
Dim OD: Set OD = CreateObject("Scripting.Dictionary")
Dim T: Set T = CreateObject("Scripting.Dictionary")
Dim M(), RZ(), U() As String
Dim MB
M = Array(39, 50, 63, 66, 67, 68, 91, 92, 93, 94, 95, 96, 97, 98, 99)

'закидываем в словарь префиксы
For R = 0 To UBound(M)
    T("(0" & M(R) & ")") = 1
Next R

'считываем в маассив данные
With Ëèñò1
    M = .Range("A1:G" & .Cells(.Rows.Count, 1).End(xlUp).Row)
End With
    ReDim RZ(1 To UBound(M), 1 To UBound(M, 2) + 2)
    
'перебираем все строки массива
    For R = 1 To UBound(M)
'    отделяеем название фирмы
        If InStr(1, M(R, 1), ",") > 0 Then
            C = Split(M(R, 1), ",")(0)
            RZ(R, 1) = C
            RZ(R, 2) = Replace(M(R, 1), C & ",", "")
        Else
            RZ(R, 1) = M(R, 1)
        End If
            RZ(R, 3) = M(R, 2)
' ищем мобильные операторы

            U = Split(M(R, 3), ",")
            For i = 0 To UBound(U)
            Debug.Print i, UBound(U), U(i), R
                If T.Exists(Left(U(i), 5)) Then
                    RZ(R, 4) = IIf(RZ(R, 4) = "", U(i), RZ(R, 4) & "," & U(i))
                Else
                    RZ(R, 5) = IIf(RZ(R, 5) = "", U(i), RZ(R, 5) & "," & U(i))
                End If
            Next i
            For i = 4 To UBound(M, 2)
                RZ(R, i + 2) = M(R, i)
            Next i
    Next R
    Worksheets.Add
    Range("A1").Resize(UBound(RZ), UBound(RZ, 2)) = RZ
    Cells.Columns.AutoFit
    Cells.Rows.AutoFit

End Sub

By the way, if it were possible, it would be cool if, when selecting company names, one could rely not on the first comma, but on the last one in the line.
Thanks a lot.

Answer 1

Look at the link, www.script-coding.com/WSH/RegExp.html should help