vba - 如何检查单元格是否包含有效的 IPv6 地址？

Question

我必须使用 VBA Excel 检查单元格值是否是有效的 IPv6 地址。 IPv6 地址如下所示:

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

使用这样的 IPv6 缩写形式的问题:

dec0::0A68:0300

这也是一个有效的 IPv6 地址。

我应该如何正确处理这个问题？

Answer 1

从 thread 中获取正则表达式如果您使用 Microsoft VBScript 正则表达式 5.5，则可以将其合并到 VBA 中。库(通过 VB 编辑器中的“工具”->“引用”找到它)。代码如下 - 疯狂的正则表达式!

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

将此代码放入模块中，然后您可以在工作表中使用此函数: