Validate Phone Number in VBA (Visual Basic for Applications) code

Using RegEx:

Public Function ValidatePhoneNumber(phoneNumber As String) As String
    Dim oPattern As String: strPattern = "[^a-zA-Z0-9]"
    Dim oReplace As String: strReplace = ""
    
    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")
    
    
    ' Configure the regex object
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = oPattern
    End With
    
    ' replacement
    ValidatePhoneNumber = regEx.Replace(phoneNumber, oReplace)
End Function

Using VBA code:

Sub ValidPhoneNumber()
    Dim oPhoneString As String
    'Put your phone number here
    oPhoneString = "(1234-12)1222-232"
    Dim myArray() As String
    myArray = ConvertToArray(oPhoneString)
    
    Dim oPhoneNumber As String
    For Each Oval In myArray
        If IsNumeric(Oval) = True Then
            oPhoneNumber = oPhoneNumber & Oval
        End If
    Next Oval
End Sub

Function:

Function ConvertToArray(ByVal value As String)
    value = StrConv(value, vbUnicode)
    ConvertToArray = Split(Left(value, Len(value) - 1), vbNullChar)
End Function

Share on Facebook

Tweet