VBA – Function to Validate IP Addresss

Below is a function which will return True or False if a valid IP address is entered. A valid IP address is of the form nnn.nnn.nnn.nnn where nnn >=0 and <=255

You can put following type of construct in a cell

=IsValidIP(A2)

1. Open your workbook and ALT+F11
2. Locate your Workbook name in Project Explorer Window
3. Right click on your workbook name > Insert > Module
4. Copy paste the Macro code given
5. Delete you Macro if the Macro was needed to be run only once.
6. Otherwise save your file as .xlsm if you intend to reuse Macro again.

Function IsValidIP(Cell) As Boolean
    Dim Arr
    Dim i As Long
    Application.Volatile
    Arr = Split(Cell, ".")
    If UBound(Arr) = 3 Then
        For i = LBound(Arr) To UBound(Arr)
            If Not IsNumeric(Arr(i)) Or Arr(i) > 255 Then
                Exit Function
            End If
        Next i
        IsValidIP = True
    End If
End Function

The workbook related to this can be downloaded from Function_Valid_ IP_Address

Edit – 18-Aug-20

The perfect solution is from Rick's comment

Function IsValidIP(Cell) As Boolean
Dim X As Long, Arr As Variant
Arr = Split(Cell, ".")
If UBound(Arr) = 3 Then
For X = 0 To 3
If Arr(X) > 255 Or Not Arr(X) Like String(Len(Arr(X)), "#") Then Exit Function
Next
IsValidIP = True
End If
End Function