pix 501 - 站点到站点 vpn 子网问题

Question

Martin

Asked: 2009-09-30 06:42:09 +0800 CST2009-09-30 06:42:09 +0800 CST 2009-09-30 06:42:09 +0800 CST

MS Excel 的子网计算器

772

我经常在这里使用方便的子网计算器：

http://www.subnet-calculator.com/

我觉得这非常有用。但是，是否有人有提供类似功能但在 Excel 电子表格中的版本的详细信息/链接。

然后我可以将它包含在工作表中，而我不必继续访问该网站。如果我没有互联网访问权限，也很方便 - 当我需要它时经常会出现这种情况。

5 个回答

Voted

Tomáš Pospíšek · Answer 1 · 2016-11-25T02:28:20+08:00

Tomáš Pospíšek

2016-11-25T02:28:20+08:002016-11-25T02:28:20+08:00

以下函数（调用 Marcus Mansfield 的 IPSubnetCalc）将在给定子网时输出网络范围：

    Public Function IPRange(IPandMaskInput$)

    ' PROGRAM:
    ' IP Range Calculator by Tomas Pospisek that uses Marcus Mansfield's IPSubnetCalc
    '
    ' INPUTS:
    ' Input Expected xx.xx.xx.xx/yy
    ' Where xx = decimal IPv4 Octet
    '       yy = BitMask Value ( 0 - 32 ex.31 )
    '
    ' OUTPUT:
    ' IP Range in the form: "aa.aa.aa.aa - bb.bb.bb.bb"

        Range_From = IPSubnetCalc(4, IPandMaskInput)
        Range_To = IPSubnetCalc(5, IPandMaskInput)

        IPRange = Range_From & " - " & Range_To
    End Function

4

Izzy · Answer 2 · 2009-09-30T07:12:21+08:00

Izzy

2009-09-30T07:12:21+08:002009-09-30T07:12:21+08:00

这是一个非常基本的。创建自己的任务实际上是一项非常简单的任务。

3

Rui Sousa · Answer 3 · 2010-10-07T11:31:25+08:00

我添加了一个新选项来计算网络中可用主机的数量。

希望能帮助到你。

瑞

Public Function IPSubnetCalc(intFn%, IPandMaskInput$)

' PROGRAM:
' IP Subnet Calculator by Marcus Mansfield
'
' CHANGES:
' Date: 2010-10-06
' Author: Rui Sousa (ruib.sousa@gmail)
' Change: Add funtion 6 to return number os hosts
'
' INPUTS:
' Input Expected xx.xx.xx.xx/yy
' Where xx = decimal IPv4 Octet
'       yy = BitMask Value ( 0 - 32 ex.31 )
'
'
' FUNCTIONS:
' 1 - Return Subnet Mask in dotted decimal
' 2 - Return Subnet Address in Dot. Dec.
' 3 - Return Broadcast Address in Dot. Dec.
' 4 - Return Lowest Client IP in Dot. Dec.
' 5 - Return Highest Client IP in Dot. Dec.
' 6 - Return Total Number of Host in the Network.
'


Dim IPAddress$, BitMask%
Dim IPOctet1%, IPOctet2%, IPOctet3%, IPOctet4%
Dim MaskOctet1%, MaskOctet2%, MaskOctet3%, MaskOctet4%
Dim Dot1%, Dot2%, Dot3%, Slash1%
Dim IPNetwork1BIN$, IPNetwork2BIN$, IPNetwork3BIN$, IPNetwork4BIN$


On Error Resume Next

Dot1 = InStr(1, IPandMaskInput, ".")
Dot2 = InStr(Dot1 + 1, IPandMaskInput, ".")
Dot3 = InStr(Dot2 + 1, IPandMaskInput, ".")
Slash1 = InStr(1, IPandMaskInput, "/")

'Check Basic Format
If Dot1 = 0 Or Dot2 = 0 Or Dot3 = 0 Or Slash1 = 0 Then
    IPSubnetCalc = "Incorrect Format"
    Exit Function
End If

'Split IP into 8bit Octets
IPOctet1 = Mid(IPandMaskInput, 1, Dot1 - 1)
IPOctet2 = Mid(IPandMaskInput, Dot1 + 1, Dot2 - Dot1 - 1)
IPOctet3 = Mid(IPandMaskInput, Dot2 + 1, Dot3 - Dot2 - 1)
IPOctet4 = Mid(IPandMaskInput, Dot3 + 1, Slash1 - Dot3 - 1)
BitMask = Right(IPandMaskInput, Len(IPandMaskInput) - Slash1)

If IPOctet1 > 255 Or IPOctet2 > 255 Or IPOctet3 > 255 Or IPOctet4 > 255 Then
    IPSubnetCalc = "Value Error, Octet > 255"
    Exit Function
End If
If IPOctet1 < 0 Or IPOctet2 < 0 Or IPOctet3 < 0 Or IPOctet4 < 0 Then
    IPSubnetCalc = "Value Error, Octet < 0"
    Exit Function
End If
If BitMask < 8 Or BitMask > 32 Or BitMask = 31 Then
    IPSubnetCalc = "Bitmask Error : Range 8 - 32, Excl. 31"
    Exit Function
End If

Select Case intFn
Case 1 'Subnet Mask Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))
    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 2 'Subnet Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

    IPNetwork1BIN = ""
    IPNetwork2BIN = ""
    IPNetwork3BIN = ""
    IPNetwork4BIN = ""

    For iCounter = 1 To 8

        If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
            IPNetwork1BIN = IPNetwork1BIN & "1"
        Else
            IPNetwork1BIN = IPNetwork1BIN & "0"
        End If

        If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
            IPNetwork2BIN = IPNetwork2BIN & "1"
        Else
            IPNetwork2BIN = IPNetwork2BIN & "0"
        End If

        If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
            IPNetwork3BIN = IPNetwork3BIN & "1"
        Else
            IPNetwork3BIN = IPNetwork3BIN & "0"
        End If

        If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
            IPNetwork4BIN = IPNetwork4BIN & "1"
        Else
            IPNetwork4BIN = IPNetwork4BIN & "0"
        End If

    Next

    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(IPNetwork1BIN)
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(IPNetwork2BIN)
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(IPNetwork3BIN)
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(IPNetwork4BIN)

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 3 'Broadcast Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

    IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

    BuildBIN = ""

    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
        Else
            BuildBIN = BuildBIN & "1"
        End If
    Next


    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 4 'Low IP Dot Dec

    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

    IPNetwork1BIN = ""
    IPNetwork2BIN = ""
    IPNetwork3BIN = ""
    IPNetwork4BIN = ""

    For iCounter = 1 To 8

        If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
            IPNetwork1BIN = IPNetwork1BIN & "1"
        Else
            IPNetwork1BIN = IPNetwork1BIN & "0"
        End If

        If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
            IPNetwork2BIN = IPNetwork2BIN & "1"
        Else
            IPNetwork2BIN = IPNetwork2BIN & "0"
        End If

        If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
            IPNetwork3BIN = IPNetwork3BIN & "1"
        Else
            IPNetwork3BIN = IPNetwork3BIN & "0"
        End If

        If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
            IPNetwork4BIN = IPNetwork4BIN & "1"
        Else
            If iCounter = 8 Then
                IPNetwork4BIN = IPNetwork4BIN & "1"
            Else
                IPNetwork4BIN = IPNetwork4BIN & "0"
            End If
        End If

    Next

    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(IPNetwork1BIN)
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(IPNetwork2BIN)
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(IPNetwork3BIN)
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(IPNetwork4BIN)

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4



Case 5 'High IP Dot Dec
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

    IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

    BuildBIN = ""

    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
        Else
            If iCounter = 32 Then
                BuildBIN = BuildBIN & "0"
            Else
                BuildBIN = BuildBIN & "1"
            End If
        End If
    Next


    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 6 'Number of Hosts/Net

    iZeroCounter = 0
    For iCounter = 1 To 32
        If iCounter > BitMask Then
            iZeroCounter = iZeroCounter + 1
        End If
    Next

    IPSubnetCalc = (2 ^ iZeroCounter) - 2



Case Else 'Incorrect Selection
    IPSubnetCalc = "Incorrect Function"
End Select

End Function

Marcus · Answer 4 · 2010-07-22T23:04:55+08:00

我知道它是一个旧线程，但是......

这是 Excel 的 VBA 函数。我假设您知道如何添加 VBA 函数（或宏等），如果不知道，我希望有人会用简单的操作方法回复此线程。

它非常基本，但它完全符合罐头上所说的并且对我有用。

输入为 =IPSubnetCalc(X, YY.YY.YY.YY/ZZ )

X = 1 到 5 - 每个选项的作用见下文。Y = IPv4 地址 Z = 8 到 32，不包括 31。（位掩码）

显然，X、Y/Z 可以是单元格引用。

享受

Public Function IPSubnetCalc(intFn%, IPandMaskInput$)

'# IP Subnet Calculator by Marcus Mansfield #
'# Input Expected xx.xx.xx.xx/yy            #
'# Where xx = decimal IPv4 Octet            #
'#       yy = BitMask Value ( 0 - 32 ex.31 )#
'# Functions ...                            #
'# 1 - Return Subnet Mask in dotted decimal #
'# 2 - Return Subnet Address in Dot. Dec.   #
'# 3 - Return Broadcast Address in Dot. Dec.#
'# 4 - Return Lowest Client IP in Dot. Dec. #
'# 5 - Return Highest Client IP in Dot. Dec.#

Dim IPAddress$, BitMask%
Dim IPOctet1%, IPOctet2%, IPOctet3%, IPOctet4%
Dim MaskOctet1%, MaskOctet2%, MaskOctet3%, MaskOctet4%
Dim Dot1%, Dot2%, Dot3%, Slash1%
Dim IPNetwork1BIN$, IPNetwork2BIN$, IPNetwork3BIN$, IPNetwork4BIN$


On Error Resume Next

Dot1 = InStr(1, IPandMaskInput, ".")
Dot2 = InStr(Dot1 + 1, IPandMaskInput, ".")
Dot3 = InStr(Dot2 + 1, IPandMaskInput, ".")
Slash1 = InStr(1, IPandMaskInput, "/")

'Check Basic Format
If Dot1 = 0 Or Dot2 = 0 Or Dot3 = 0 Or Slash1 = 0 Then
    IPSubnetCalc = "Incorrect Format"
    Exit Function
End If

'Split IP into 8bit Octets
IPOctet1 = Mid(IPandMaskInput, 1, Dot1 - 1)
IPOctet2 = Mid(IPandMaskInput, Dot1 + 1, Dot2 - Dot1 - 1)
IPOctet3 = Mid(IPandMaskInput, Dot2 + 1, Dot3 - Dot2 - 1)
IPOctet4 = Mid(IPandMaskInput, Dot3 + 1, Slash1 - Dot3 - 1)
BitMask = Right(IPandMaskInput, Len(IPandMaskInput) - Slash1)

If IPOctet1 > 255 Or IPOctet2 > 255 Or IPOctet3 > 255 Or IPOctet4 > 255 Then
    IPSubnetCalc = "Value Error, Octet > 255"
    Exit Function
End If
If IPOctet1 < 0 Or IPOctet2 < 0 Or IPOctet3 < 0 Or IPOctet4 < 0 Then
    IPSubnetCalc = "Value Error, Octet < 0"
    Exit Function
End If
If BitMask < 8 Or BitMask > 32 Or BitMask = 31 Then
    IPSubnetCalc = "Bitmask Error : Range 8 - 32, Excl. 31"
    Exit Function
End If

Select Case intFn
Case 1 'Subnet Mask Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))
    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 2 'Subnet Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

    IPNetwork1BIN = ""
    IPNetwork2BIN = ""
    IPNetwork3BIN = ""
    IPNetwork4BIN = ""

    For iCounter = 1 To 8

        If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
            IPNetwork1BIN = IPNetwork1BIN & "1"
        Else
            IPNetwork1BIN = IPNetwork1BIN & "0"
        End If

        If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
            IPNetwork2BIN = IPNetwork2BIN & "1"
        Else
            IPNetwork2BIN = IPNetwork2BIN & "0"
        End If

        If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
            IPNetwork3BIN = IPNetwork3BIN & "1"
        Else
            IPNetwork3BIN = IPNetwork3BIN & "0"
        End If

        If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
            IPNetwork4BIN = IPNetwork4BIN & "1"
        Else
            IPNetwork4BIN = IPNetwork4BIN & "0"
        End If

    Next

    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(IPNetwork1BIN)
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(IPNetwork2BIN)
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(IPNetwork3BIN)
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(IPNetwork4BIN)

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 3 'Broadcast Dot. Dec.
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

    IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

    BuildBIN = ""

    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
        Else
            BuildBIN = BuildBIN & "1"
        End If
    Next


    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case 4 'Low IP Dot Dec
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'AND Binary Expressions.

    IPNetwork1BIN = ""
    IPNetwork2BIN = ""
    IPNetwork3BIN = ""
    IPNetwork4BIN = ""

    For iCounter = 1 To 8

        If Mid(MaskOctet1BIN, iCounter, 1) And Mid(IPOctet1BIN, iCounter, 1) Then
            IPNetwork1BIN = IPNetwork1BIN & "1"
        Else
            IPNetwork1BIN = IPNetwork1BIN & "0"
        End If

        If Mid(MaskOctet2BIN, iCounter, 1) And Mid(IPOctet2BIN, iCounter, 1) Then
            IPNetwork2BIN = IPNetwork2BIN & "1"
        Else
            IPNetwork2BIN = IPNetwork2BIN & "0"
        End If

        If Mid(MaskOctet3BIN, iCounter, 1) And Mid(IPOctet3BIN, iCounter, 1) Then
            IPNetwork3BIN = IPNetwork3BIN & "1"
        Else
            IPNetwork3BIN = IPNetwork3BIN & "0"
        End If

        If Mid(MaskOctet4BIN, iCounter, 1) And Mid(IPOctet4BIN, iCounter, 1) Then
            IPNetwork4BIN = IPNetwork4BIN & "1"
        Else
            If iCounter = 8 Then
                IPNetwork4BIN = IPNetwork4BIN & "1"
            Else
                IPNetwork4BIN = IPNetwork4BIN & "0"
            End If
        End If

    Next

    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(IPNetwork1BIN)
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(IPNetwork2BIN)
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(IPNetwork3BIN)
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(IPNetwork4BIN)

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4



Case 5 'High IP Dot Dec
    BuildBIN = ""
    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & "1"
        Else
            BuildBIN = BuildBIN & "0"
        End If
    Next
    MaskOctet1BIN = Mid(BuildBIN, 1, 8)
    MaskOctet2BIN = Mid(BuildBIN, 9, 8)
    MaskOctet3BIN = Mid(BuildBIN, 17, 8)
    MaskOctet4BIN = Mid(BuildBIN, 25, 8)

    IPOctet1BIN = Application.WorksheetFunction.Dec2Bin(IPOctet1, 8)
    IPOctet2BIN = Application.WorksheetFunction.Dec2Bin(IPOctet2, 8)
    IPOctet3BIN = Application.WorksheetFunction.Dec2Bin(IPOctet3, 8)
    IPOctet4BIN = Application.WorksheetFunction.Dec2Bin(IPOctet4, 8)

'Create Full IP as Binary

    IPFullBIN = IPOctet1BIN & IPOctet2BIN & IPOctet3BIN & IPOctet4BIN

    BuildBIN = ""

    For iCounter = 1 To 32
        If iCounter <= BitMask Then
            BuildBIN = BuildBIN & Mid(IPFullBIN, iCounter, 1)
        Else
            If iCounter = 32 Then
                BuildBIN = BuildBIN & "0"
            Else
                BuildBIN = BuildBIN & "1"
            End If
        End If
    Next


    MaskOctet1 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 1, 8))
    MaskOctet2 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 9, 8))
    MaskOctet3 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 17, 8))
    MaskOctet4 = Application.WorksheetFunction.Bin2Dec(Mid(BuildBIN, 25, 8))

    IPSubnetCalc = MaskOctet1 & "." & MaskOctet2 & "." & MaskOctet3 & "." & MaskOctet4


Case Else 'Incorrect Selection
    IPSubnetCalc = "Incorrect Function"
End Select

End Function

JS. · Answer 5 · 2009-09-30T06:57:16+08:00

JS.

2009-09-30T06:57:16+08:002009-09-30T06:57:16+08:00

您是否特别想要一个电子表格来计算子网？有很多独立的工具可以做到这一点。这里例如

1

MS Excel 的子网计算器

SFTP 使用什么端口？

从 IP 地址解析主机名

如何按大小对 du -h 输出进行排序

命令行列出 Windows Active Directory 组中的用户？

Windows 中执行反向 DNS 查找的命令行实用程序是什么？

如何检查 Windows 机器上的端口是否被阻塞？

我应该打开哪个端口以允许远程桌面？

什么是 Pem 文件，它与其他 OpenSSL 生成的密钥文件格式有何不同？

如何确定bash变量是否为空？

MS Excel 的子网计算器

5 个回答

相关问题