#VBAをでエクセルマクロを使ってみよう
はい、私インフラエンジニアでありながら、IPアドレスの計算が苦手です。
いまだに2進数に直して考えないとわかりません(笑)
そんな私に、ネットワーク機器へのルーティングや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")、変数でまとめれたんじゃないか?
などなど書き終わって振り返ってみると、改善箇所はたくさんありそうです。
が、これが今の私の書く実力なので、日々勉強していきます。
##さいごに
自分だったらこう書く、この書き方はよくないなど、ご意見・アドバイス頂けると嬉しいです。