VBAでIPアドレス自動計算マクロを作ってみた

VBAをでエクセルマクロを使ってみよう

はい、私インフラエンジニアでありながら、IPアドレスの計算が苦手です。
いまだに２進数に直して考えないとわかりません(笑)

そんな私に、ネットワーク機器へのルーティングやACLの設定依頼がやってきました。
となると、サブネットのCIDRへの変換や、ネットワークアドレスの記述が必要になってきますよね。

一つずつネットの自動変換ツールに打ち込んでいくのは面倒でしたので、VBAで書いてみました。

どんなツール？

どんなツールにしてみたのか？文字で書くと以下のようなモノです。

＜エクセルに手動入力必要＞
・B列：IPアドレス　　　　(192.168.100.1 など)
・C列：サブネットマスク 　(255.255.255.0 など)

＜エクセルに自動入力される＞
・D列：CIDR表記でのIPアドレス　(192.168.100.1/24 など)
・E列：ネットワークアドレス　　　　(192.168.100.0 など)

コード例

Attribute VB_Name = "AutoIP_Cal"

'///エクセルの2列目が見出しで、3列目よりデータを入力

Sub AutoIP()

    '///変数や辞書の準備///

    Dim MaxRow As Integer
    Dim Num As Integer
    Dim DivOc As Variant
    Dim EachRow As Integer
    Dim IPCount As Integer
    Dim KeyCount As Integer
    Dim Cidr As Integer
    Dim Keys() As Variant
    Dim CIDR_Dic As Object
    Dim Subnet As Variant
    Dim IP As Variant

    Set CIDR_Dic = CreateObject("Scripting.Dictionary")

    CIDR_Dic.Add 0, 0
    CIDR_Dic.Add 128, 1
    CIDR_Dic.Add 192, 2
    CIDR_Dic.Add 224, 3
    CIDR_Dic.Add 240, 4
    CIDR_Dic.Add 248, 5
    CIDR_Dic.Add 252, 6
    CIDR_Dic.Add 254, 7
    CIDR_Dic.Add 255, 8

    Keys = CIDR_Dic.Keys

    MaxRow_B = Range("B3").End(xlDown).Row

 '///B列(IPアドレス)と、C列(サブネット)の入力を元に、D列(IP+CIDR)、E列(ネットワークアドレス)を記入するツール///

    For EachRow = 3 To MaxRow_B
        Cidr = 0
        DivOc = 0
        Subnet = Split(Worksheets("Sheet1").Cells(EachRow, 3), ".")
        IP = Split(Worksheets("Sheet1").Cells(EachRow, 2), ".")

        For IPCount = 0 To 3

            For KeyCount = 0 To 8
                If Int(Subnet(IPCount)) = Keys(KeyCount) Then
                    Num = CIDR_Dic.Item(Keys(KeyCount))
                ElseIf CIDR_Dic.Exists(Int(Subnet(IPCount))) = False Then
                MsgBox (EachRow & "行目のサブネットにエラーがあります")
                End
                End If
            Next KeyCount

            Cidr = Cidr + Num
        Next IPCount

        Worksheets("Sheet1").Cells(EachRow, 4) = (Worksheets("Sheet1").Cells(EachRow, 2)) + "/" + LTrim(str(Cidr))

        If Cidr >= 24 And Cidr < 32 Then
            DivOc = IP(3) / (256 - Int(Subnet(3)))
            DivOc = (Application.WorksheetFunction.RoundUp(DivOc, 0) - 1) * (256 - Int(Subnet(3)))
            Worksheets("Sheet1").Cells(EachRow, 5) = (IP(0) + "." + IP(1) + "." + IP(2) + "." + LTrim(str(DivOc)))

        ElseIf Cidr >= 16 And Cidr < 24 Then
            DivOc = IP(2) / (256 - Int(Subnet(2)))
            DivOc = (Application.WorksheetFunction.RoundUp(DivOc, 0) - 1) * (256 - Int(Subnet(2)))
            Worksheets("Sheet1").Cells(EachRow, 5) = (IP(0) + "." + IP(1) + "." + LTrim(str(DivOc)) + "." + LTrim(str(0)))

        ElseIf Cidr >= 8 And Cidr < 16 Then
            DivOc = IP(1) / (256 - Int(Subnet(1)))
            DivOc = (Application.WorksheetFunction.RoundUp(DivOc, 0) - 1) * (256 - Int(Subnet(1)))
            Worksheets("Sheet1").Cells(EachRow, 5) = (IP(0) + "." + LTrim(str(DivOc)) + "." + LTrim(str(0)) + "." + LTrim(str(0)))

        ElseIf Cidr >= 0 And Cidr < 8 Then
            DivOc = IP(0) / (256 - Int(Subnet(0)))
            DivOc = (Application.WorksheetFunction.RoundUp(DivOc, 0) - 1) * (256 - Int(Subnet(0)))
            Worksheets("Sheet1").Cells(EachRow, 5) = (LTrim(str(DivOc)) + "." + LTrim(str(0)) + "." + LTrim(str(0)) + "." + LTrim(str(0)))
        Else
            Worksheets("Sheet1").Cells(EachRow, 5) = Worksheets("Sheet1").Cells(EachRow, 2)

        End If
    Next EachRow


End Sub

振り返り

これだけのツールで、11個も変数を用意しましたが、本当にこんなに必要だったのか？
Cidrに関する「IF文」の箇所は、もっと行数を簡略化できないのか？
Worksheets("Sheet1")、変数でまとめれたんじゃないか？

などなど書き終わって振り返ってみると、改善箇所はたくさんありそうです。
が、これが今の私の書く実力なので、日々勉強していきます。

さいごに

自分だったらこう書く、この書き方はよくないなど、ご意見・アドバイス頂けると嬉しいです。