Monday, September 19, 2011

Excel User Defined Function for CIDR Subnet Mask Notation

Here's another one of the Excel UDF's I have prepared for performing network operations in Excel. I find it specifically useful when dealing with large data sets. This function converts a subnet mask to CIDR notation. It is implemented with statically defining a dictionary object. It may not be an elegant solution, but it is works.

Ex. CIDR("255.128.0.0")=9

'--------------------------------------------
'CIDR (Classless Inter-Domain Routing)
'--------------------------------------------
'Given an inputStr value containing a subnet mask in an IP 
'format this functio returns the corresponding subnet mask 
'in CIDR Notation
Function cidr(inputStr As String)
    Dim maskDict As Dictionary
    Set maskDict = New Dictionary
    maskDict.Add "128.0.0.0", 1
    maskDict.Add "192.0.0.0", 2
    maskDict.Add "224.0.0.0", 3
    maskDict.Add "240.0.0.0", 4
    maskDict.Add "248.0.0.0", 5
    maskDict.Add "252.0.0.0", 6
    maskDict.Add "254.0.0.0", 7
    maskDict.Add "255.0.0.0", 8
    maskDict.Add "255.128.0.0", 9
    maskDict.Add "255.192.0.0", 10
    maskDict.Add "255.224.0.0", 11
    maskDict.Add "255.240.0.0", 12
    maskDict.Add "255.248.0.0", 13
    maskDict.Add "255.252.0.0", 14
    maskDict.Add "255.254.0.0", 15
    maskDict.Add "255.255.0.0", 16
    maskDict.Add "255.255.128.0", 17
    maskDict.Add "255.255.192.0", 18
    maskDict.Add "255.255.224.0", 19
    maskDict.Add "255.255.240.0", 20
    maskDict.Add "255.255.248.0", 21
    maskDict.Add "255.255.252.0", 22
    maskDict.Add "255.255.254.0", 23
    maskDict.Add "255.255.255.0", 24
    maskDict.Add "255.255.255.128", 25
    maskDict.Add "255.255.255.192", 26
    maskDict.Add "255.255.255.224", 27
    maskDict.Add "255.255.255.240", 28
    maskDict.Add "255.255.255.248", 29
    maskDict.Add "255.255.255.252", 30
    maskDict.Add "255.255.255.254", 31
    maskDict.Add "255.255.255.255", 32
    cidr = maskDict.Item(FindIP(inputStr))
End Function

'--------------------------------------------
'Find IP Address
'--------------------------------------------
'A support function that parses input strTest through a regular 
'expression to find an IP Address
'A blank value is returned if no IP address can be found
Function FindIP(strTest As String) As String
    Dim RegEx As Object
    Dim valid As Boolean
    Dim Matches As Object
    Dim i As Integer
    Set RegEx = CreateObject("VBScript.RegExp")
   
    RegEx.Pattern = "\b(?:\d{1,3}\.){3}\d{1,3}\b"
    valid = RegEx.test(strTest)
    If valid Then
        Set Matches = RegEx.Execute(strTest)
        FindIP = Matches(0)
    Else
        FindIP = ""
    End If
End Function

2 comments:

  1. Helpful script but you need to add a reference to the Microsoft scripting runtime for it to work

    ReplyDelete
  2. An IP (Internet Protocol) address is a logical address of a device in a computer network using IP protocol (works on Layer 3 of ISO/OSI model). IP addresses are used on the Internet. IP addresses are stored as 32-bit binary numbers, but they are displayed as four decimal numbers divided with a dot (dot-decimal notation), example 68.12.5.10. An IP address indicates the logical location of a device. An IP address range is from 0.0.0.0 to 239.255.255.255. Public addresses are managed by IANA (Internet Assigned Numbers Authority) and blocks of addresses are allocated to Local Internet Registries. These address blocks correspondent to geological locations.


    ReplyDelete