How to check if a cell contains valid IPv6 address?

Question

I have to check with VBA excel that a cell value is a valid IPv6 address. IPv6 address looks like that:

2001:0000:3238:DFE1:0063:0000:0000:FEFB

The problem using short form of IPv6 like that:

dec0::0A68:0300

This also a valid IPv6 address too.

How should I handle this properly?

I think you should use [Regular Expressions](https://www.experts-exchange.com/articles/1336/Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html), although I never used them and do not know exactly how they work. Take a look at [this](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops?answertab=active#tab-top) also. — CMArg, Jun 20 '16 at 11:41
I know how to use Regular Expressions, but the logic is the problem, how to filter the valid IP addresses? — Twi, Jun 20 '16 at 11:52

score 3 · Answer 1 · edited May 23 '17 at 11:52

Taking the regular expression from this thread you can incorporate it in VBA if you use the Microsoft VBScript Regular Expressions 5.5. library (find it with Tools->References in the VB Editor). The code is below - crazy regex!

Option Explicit

Function IsIPv6(ByVal strIn As String) As Boolean

    Dim objRegex As New RegExp
    Dim strPattern As String

    strPattern = "(([0-9a-fA-F]{1,4}:){7,7}[0-9a-fA-F]{1,4}|"
    strPattern = strPattern & "([0-9a-fA-F]{1,4}:){1,7}:|"
    strPattern = strPattern & "([0-9a-fA-F]{1,4}:){1,6}:[0-9a-fA-F]{1,4}|"
    strPattern = strPattern & "([0-9a-fA-F]{1,4}:){1,5}(:[0-9a-fA-F]{1,4}){1,2}|"
    strPattern = strPattern & "([0-9a-fA-F]{1,4}:){1,4}(:[0-9a-fA-F]{1,4}){1,3}|"
    strPattern = strPattern & "([0-9a-fA-F]{1,4}:){1,3}(:[0-9a-fA-F]{1,4}){1,4}|"
    strPattern = strPattern & "([0-9a-fA-F]{1,4}:){1,2}(:[0-9a-fA-F]{1,4}){1,5}|"
    strPattern = strPattern & "[0-9a-fA-F]{1,4}:((:[0-9a-fA-F]{1,4}){1,6})|"
    strPattern = strPattern & ":((:[0-9a-fA-F]{1,4}){1,7}|"
    strPattern = strPattern & ":)|"
    strPattern = strPattern & "fe80:(:[0-9a-fA-F]{0,4}){0,4}%[0-9a-zA-Z]{1,}|"
    strPattern = strPattern & "::(ffff(:0{1,4}){0,1}:){0,1}((25[0-5]|"
    strPattern = strPattern & "(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|(2[0-4]|"
    strPattern = strPattern & "1{0,1}[0-9]){0,1}[0-9])|"
    strPattern = strPattern & "([0-9a-fA-F]{1,4}:){1,4}:((25[0-5]|"
    strPattern = strPattern & "(2[0-4]|1{0,1}[0-9]){0,1}[0-9])\.){3,3}(25[0-5]|"
    strPattern = strPattern & "(2[0-4]|1{0,1}[0-9]){0,1}[0-9]))"

    objRegex.Pattern = strPattern
    IsIPv6 = objRegex.Test(strIn)

End Function

Sub Test()

    Debug.Print IsIPv6("2001:0000:3238:DFE1:0063:0000:0000:FEFB")
    Debug.Print IsIPv6("dec0::0A68:0300")
    Debug.Print IsIPv6("foo")

End Sub

Put this code in a Module and then you can use this function in a Worksheet:

The only problem is, that it's not filter if I do like that: dec0:::::::::::::::::0B58:0100 or feeeeec0::0eeeeeeA58:0100 So it's almost perfect, but sometimes not working the pattern. — Twi, Jun 20 '16 at 12:36
Seems like the perfect ipv6 regex is a bit of a puzzler. You can search SO and the internet and plug in something better if you find it. The one I put in the code is one that got a lot of up-votes . Good luck. — Robin Mackenzie, Jun 20 '16 at 13:17

score 0 · Answer 2 · answered Jun 20 '16 at 12:04

Can you just ping the address? If so, you can use Win32_PingStatus to attempt to resolve the IP, and this will work for IPv4, v6, or even just a hostname. Something like this:

Function IsValidIP(strHost As String) As Boolean

Dim strQuery As String
strQuery = "select * from Win32_PingStatus where Address = '" & strHost & "'"

Dim colItems As Object
Set colItems = GetObject("winmgmts://./root/cimv2").ExecQuery(strQuery)

IsValidIP = False

Dim objItem As Object
For Each objItem In colItems
    If IsObject(objItem) Then
        If objItem.PrimaryAddressResolutionStatus = 0 Then
            IsValidIP = True
            Exit Function
        Else
            'Could do select case here to handle other return codes.
        End If
    End If
Next

End Function

How to check if a cell contains valid IPv6 address?

2 Answers2