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
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