How to create a function to validate IP addresses in Excel with VBA

In this Excel VBA tutorial, we will be creating a User-Defined Function (aka UDF) to validate if IP addresses are valid.

Assuming you have a spreadsheets with a list of IP addresses, and your job is to validate whether each IP address is valid. My personal experience with writing a formula for such task is a bit cumbersome and complicated. Using VBA to create our own function can simplify the process and we can also export the function as an add-in for future use.

Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn

Source Code:

Public Function Valid_IP_Address(ByVal IP_Address As String) As Boolean

    Dim IPAddress As String
    Dim arrIPs() As String
    Dim IP(3) As String
    Dim i As Long

    On Error GoTo errHandler

    Valid_IP_Address = True

    IPAddress = IP_Address

    arrIPs = Split(IPAddress, ".")

    ' First validation
    If UBound(arrIPs) <> 3 Then
        GoTo errHandler
    End If

    For i = LBound(arrIPs) To UBound(arrIPs)
        IP(i) = arrIPs(i)

        ' Second validation
        If 1 > IP(i) Or IP(i) > 255 Then
            GoTo errHandler
        End If
    Next i

Exit Function

errHandler:
    Valid_IP_Address = False
End Function