【ExcelVBA】IPアドレスの自動比較。特定のIPアドレスが複数のネットワークのどれに一致しているか判断する。

スポンサーリンク




趣味のプログラム

ネットワークの仕事を自動化しようと思ったら特定のIPアドレスがネットワークの一覧のどれに該当しているか確認する必要がでてくる。

VBAにはIPアドレスを操作する関数がないので毎回自作していたが改めて書き直して汎用的に使えるようにしてみた。

スポンサーリンク

コード

コピーしやすいようにコードは1つにまとめて記載します。

Option Explicit
Sub test()
    Dim リスト, リスト2次配列 As Variant
    Dim 検索アドレス, 検索結果 As String
    Dim 開始行, 情報列, 終了行 As Long
    開始行 = Selection(1).Row
    情報列 = Selection(1).Column
    終了行 = Cells(Rows.Count, 情報列).End(xlUp).Row
    リスト2次配列 = Range(Cells(開始行, 情報列), Cells(終了行, 情報列))
    リスト = WorksheetFunction.Transpose(WorksheetFunction.Index(リスト2次配列, 0, 1))
    検索アドレス = InputBox("検索したいIPアドレスを入力してください。" & vbCrLf & _
                    "例:10.1.1.21/24")
    If 検索アドレス = "" Then
        MsgBox "処理を中止します。"
        Exit Sub
    End If
    検索結果 = リストに最も一致する値を返す(リスト, 検索アドレス)
    If 検索結果 = Empty Then
        MsgBox 検索アドレス & "はアドレスリストのネットワークに一致しません。"
    Else
        検索結果 = InputBox(Prompt:=検索アドレス & "は下記アドレスに含まれます。", Default:=検索結果)
    End If
End Sub


Function リストに最も一致する値を返す(ByVal リスト As Variant, ByVal 検索値 As String) As String
    Dim 検索値CIDR As Integer
    ReDim 比較先CIDR(UBound(リスト)) As Integer
    
    If 検索値 Like "*/*" Then
        検索値CIDR = Split(検索値, "/")(1)
    Else
        検索値CIDR = 32
    End If
    
    Dim 検索値2進数, 比較先2進数 As String
    Dim i, ビット捜査値, 最大一致ビット値 As Long
    For i = 1 To UBound(リスト)
        If リスト(i) Like "*/*" Then
            比較先CIDR(i) = Split(リスト(i), "/")(1)
        Else
            比較先CIDR(i) = 32
        End If
        
        If 比較先CIDR(i) > 検索値CIDR Then
            GoTo コンテニュー
        End If
        検索値2進数 = アドレス10進数から2進数(検索値)
        比較先2進数 = アドレス10進数から2進数(リスト(i))
        
        If 比較先2進数 = 検索値2進数 Then
            ビット捜査値 = 32
        Else
            ビット捜査値 = 0
            Do Until Mid(比較先2進数, ビット捜査値 + 1, 1) <> Mid(検索値2進数, ビット捜査値 + 1, 1)
                ビット捜査値 = ビット捜査値 + 1
            Loop
        End If
        If 比較先CIDR(i) <= ビット捜査値 And 最大一致ビット値 < ビット捜査値 Then
            最大一致ビット値 = 比較先CIDR(i)
            リストに最も一致する値を返す = リスト(i)
        End If
コンテニュー:
    Next
End Function
Function アドレス10進数から2進数(ByVal アドレス As String) As String
    Dim アドレスCIDR無し As String
    If アドレス Like "*/*" Then
        アドレスCIDR無し = Split(アドレス, "/")(0)
    Else
        アドレスCIDR無し = アドレス
    End If
    
    Dim オクテット値 As Variant
    Dim オクテット値2進数, 余り As String
    For Each オクテット値 In Split(アドレスCIDR無し, ".")
        オクテット値2進数 = ""
        余り = ""
        Do Until オクテット値 < 2
            余り = オクテット値 Mod 2
            オクテット値 = Int(オクテット値 / 2)
            オクテット値2進数 = 余り & オクテット値2進数
        Loop
        オクテット値2進数 = Format(オクテット値 & オクテット値2進数, "00000000")
        アドレス10進数から2進数 = アドレス10進数から2進数 & オクテット値2進数
    Next
End Function

使い方

5つの手順で使えるようにしています。

IPアドレスの誤記チェックは行っていないのでIPアドレスの形式は事前に確認をお願いします。

対応形式

  • ネットワークアドレスの場合:192.168.10.0/24
  • ホストアドレスの場合:192.168.10.1/32 または 192.168.10.1
  1. 上のコードをコピーしてVBEのモジュールへ貼り付ける
  2. Excelの好きなセルへIPアドレスリストを記載して最初のセルを選択しておく
  3. メイン処理を実行する
  4. 検索IPアドレス確認のポップアップへIPアドレスを入力してOKを押す
  5. 一致するネットワークが画面に表示される

使用は自己責任でお願いします。

スポンサーリンク

メイン処理の説明

3−5変数宣言

6−10選択しているセルをIPアドレスリストの最初の行として取り込む

11ー16入力画面を表示して検索したいIPアドレスを取得する

17(26ー87)IPアドレスリストに検索したいIPアドレスと一致するネットワークがあるか検索する

18ー23検索結果を表示する

メイン処理はここまでです。

 

比較する関数の説明

26引数の受け取り

27ー28変数宣言

30−34検索したいIPアドレスのCIDRの値を取得

36ー37変数宣言

38IPアドレスリスト分繰り返し

39ー43IPアドレスリストのCIDRの値を取得

45ー47検索したいIPアドレスの方がCIDRが小さい場合はリストチェックをスキップする

48−49(66−87)IPアドレスを10進数から2進数へ変換

51ー58 2進数で何ビット一致しているか確認

本当は配列に入れて回そうと思ったんですが、1文字毎に分ける方がコードが長くなりそうだったのでMIDで左から右に1文字ずつ比較する手法にしてみました。

59ー62IPアドレスリストのCIDRより多く一致している場合、他に一致したIPアドレスリストのCIDRより大きい場合の2つを満たしている時に暫定値として戻り値へ格納する

ここはじっくり考えないと分からなくなってしまいます。Andにしているので2つ理由を書きます。
・ネットワークアドレスのCIDRより多く一致していない場合はそのネットワークに含まれていない。
・現時点で多く一致しているビット数より一致が大きくないと暫定1位にはなれない。
スポンサーリンク

アドレス変換を行う関数の説明

66引数受け取り

67変数宣言

68−72CIDRの値を切り捨て

74ー75変数宣言

76ー86 4つのオクテット毎に割り算で余りの0or1を2進数として戻り値に格納していく

 

VBAはどうやって覚える?

プログラムのコードが読めないと、参考にしているコードの理解や自分でプログラムを書くときに時間がかかってしまいます。
そんな時は基礎から学ぶと応用もできるようになり自分の作りたいプログラムがスムーズに作れるようになります。

無料でやるならネット上の学習サイトを参考にするといいです。
こちらはVBAの基礎から説明している無料のWEBサイトになります。

>>基礎から学べるWEBサイトを見てみる


過去にネットでの学習でつまずいた方には専門書をオススメします。
本なら基礎から学べる事はもちろん、読者視点で分かりやすい解説になっています。
一人で集中してコツコツ進めたい方は書籍を試してください。



本を読んでもわからない所が多すぎたり、誰かに質問したい場合はオンラインスクールを見てみましょう。
カウンセリングから目的に応じた学習プランと教材を提供してくれるので、プログラミングの敷居がとても低くなります。
誰かに相談できるのは心強いです。

>>オンラインスクールを無料体験で始める


最後に

プログラムの手法としては難しい事はしていないので、For Each/split/Do Loopあたりを理解していると読める内容になっています。

ネットワークの管理を行なっていたら押さえておきたいコードになると思うので参考になれば嬉しいです。

最後まで読んで頂き、ありがとうございます。

「この記事が参考になった」「お礼でコーヒー代をプレゼントしよう」という方がいましたらチップ(15円〜)をお願いいたします。

Amazonギフト券- Eメールタイプ - Amazonベーシック

メールのあて先は 「mizusibuki8080@gmail.com」です。
※上記のアドレスは投げ銭受け取り専用です。他のメールは受け取れない設定にしてあるのでご注意ください。

趣味のプログラム
ネットワークエンジニアになった人
タイトルとURLをコピーしました