VBA Makro für IP-Adressen

Microsoft Excel hat leider kein Datenformat für IP-Adressen und daher kann man mit Excel auch schlecht IP-Adressen sortieren oder verarbeiten. IPv4 Adressen werden i.d.R. als ASCII String aus Zahlen und Punkten im so genannten „dotted decimal“ Format notiert. Die Subnetzmaske wird i.d.R. als Ganzzahl mit der Anzahl der Subnetz Bits angegeben. Hier ein Beispiel:

192.168.178.12 / 24

Theorie

Die ASCII/String IP-Adresse kann man wie folgt in einen numerischen Wert umzuwandeln:

192.168.178.12
|   |   |   | 
192 |   |   |  * 256^3 = 3221225472
    168 |   |  * 256^2 =   11010048
        178 |  * 256^1 =      45568
            12 * 256^0 =         12
                         ---------- 
                         3232281100

Theoretisch kann man so alle IPv4 Adressen im Bereich 0.0.0.0 (=0) und 255.255.255.255 (=4294967295) darstellen. Praktisch werden IPv4 Adressen im Bereich 0.0.0.0 bis 223.255.255.255 (=3758096383) verwendet. Excel VBA 7 (2010) bietet keinen ganzzahligen Datentyp für diese Zahl (LongLong), daher muss der numerische Wert als Gleitkommazahlen (Double) dargestellt werden.

Wenn man die IP-Adresse mit der Subnetzmaske binär „und“ verknüpft, erhält man die IP-Adresse des dazugehörigen Netzwerks:

192.168.178.12 = 11000000.10101000.10110010.00001100
24             = 11111111.11111111.11111111.00000000
                 -----------------------------------
                 11000000.10101000.10110010.00000000 = 192.168.178.0

ASCII to Numeric

Die Funktion aton() wandelt einen ASCII-String in einem numerischen Wert um. Der Funktionsname ist angeleht an die MySQL-Funktion inet_aton(expr)

Public Function aton(ByVal aip As String) As Double
    ' aip  : ASCII/String IP-Address
    ' aton : Numeric IP-Address
    Dim strary() As String
    strary = Split(aip, ".")
    aton = Int(strary(0)) * 256 ^ 3
    aton = aton + Int(strary(1)) * 256 ^ 2
    aton = aton + Int(strary(2)) * 256 ^ 1
    aton = aton + Int(strary(3)) * 256 ^ 0
End Function

Numeric to ASCII

Die Funktion ntoa() wandelt einen numerischen Wert in einen ASCII-String um. Der Funktionsname ist angelehnt an die MySQL-Funktion inet_ntoa(expr

Public Function ntoa(ByVal nip As Double) As String
    ' nip  : Numeric IP-Address
    ' ntoa : ASCII/String IP-Address
    Dim oct As Integer
    ntoa = ""
    oct = Int(nip / 256 ^ 3)
    nip = nip - oct * 256 ^ 3
    ntoa = oct
    oct = Int(nip / 256 ^ 2)
    nip = nip - oct * 256 ^ 2
    ntoa = ntoa & "." & oct
    oct = Int(nip / 256 ^ 1)
    nip = nip - oct * 256 ^ 1
    ntoa = ntoa & "." & oct
    oct = Int(nip / 256 ^ 0)
    nip = nip - oct * 256 ^ 0
    ntoa = ntoa & "." & oct
End Function

ASCII to Subnet (numeric)

Die Funktion atosn() errechnet aus IP-Adresse (ASCII-String) und Subnetzmaske (Zahl) die IP-Adresse des dazugehörigen IP-Subnetzes (numerischen Wert). Die Funktion ist hilfreich umd IP-Adressen aus gleichen Subnetzen zu identifizieren.

Public Function atosn(ByVal aip As String, ByVal mk As Byte) As Double
    ' aip   : ASCII/String IP-Address
    ' mk    : Numeric Subnet bits
    ' atosn : Numeric IP-Subnet-Address
    Dim aip_ary() As String ' IP ascii --> ascii array
    Dim nmk As Double ' Mask bits --> numeric IP
    Dim amk As String ' Mask numeric IP --> ascii string
    Dim amk_ary() As String ' Mask ascii string --> ascii array
    Dim i As Byte
    If mk > 32 Then mk = 32
    nmk = 0
    For i = 1 To mk
        nmk = nmk + 2 ^ (32 - i)
    Next i
    amk = ntoa(nmk)
    amk_ary = Split(amk, ".")
    aip_ary = Split(aip, ".")
    amk_ary(0) = Int(Int(aip_ary(0)) And Int(amk_ary(0)))
    amk_ary(1) = Int(Int(aip_ary(1)) And Int(amk_ary(1)))
    amk_ary(2) = Int(Int(aip_ary(2)) And Int(amk_ary(2)))
    amk_ary(3) = Int(Int(aip_ary(3)) And Int(amk_ary(3)))
    amk = amk_ary(0) & "." & amk_ary(1) & "." & amk_ary(2) & "." & amk_ary(3)
    atosn = aton(amk)
End Function

Links

  1. https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton
  2. https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-ntoa
Veröffentlicht in Technik Getagged mit: ,

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

*