Code Library

Convert CIDR Notation to IP Address Range in VBA

A VBA function to convert from CIDR notation (192.168.1.1/24) to the corresponding IP range (192.168.1.0 - 192.168.1.255).

Mike Wolfe

• 7 min read
Convert CIDR Notation to IP Address Range in VBA

If you've ever had to do any kind of network administration, you have likely come across the CIDR notation.  It's a compact way of representing a range of IP addresses, also known as a subnet.

10.0.0.0/8

For example, the above CIDR represents the largest available contiguous private network.  This private subnet encompasses 16,777,216 IP addresses from 10.0.0.0 through 10.255.255.255.

If you want to convert CIDR notation to IP address ranges, there are lots of online tools available, such as this one.  But what if you want to do it in VBA?

The ParseCidr Function

I wrote a function that parses a CIDR string and returns the following information:

  • Starting IP address
  • Ending IP address
  • Subnet Mask
  • IP Address Count

It returns this information in the form of a VBA user-defined type (UDT).  UDTs are a great way to return multiple pieces of simple information from a single function call.

Building a function to extract this information was straightforward, but it's the kind of code that is very easy to end up with off-by-one errors and other similar mathematical mistakes.  This is the perfect candidate for test-driven development.

Start with the Tests

I used my DocTest feature to create a series of tests using outputs from an online CIDR conversion calculator.  

Normally with doc tests, I write the tests directly against the function I am documenting/verifying.  In this case, though, my function returns multiple values.  I could have tested each value individually.  In fact, unit-testing purists would require such an approach.  However, I'm less of a purist and more of a signal vs. noise guy.  

So, I wrote a test function that combines all four outputs into a single string:

Function Test_ParseCidr(Cidr As String) As String
    Dim IPRange As udtIPRange
    
    IPRange = ParseCidr(Cidr)
    Test_ParseCidr = IPRange.StartIP & " - " & IPRange.EndIP & _
                     " [" & IPRange.SubnetMask & "] (" & IPRange.Count & ")"
End Function

I then wrote a series of test cases:

'>>> Test_ParseCidr("68.70.16.0/20")
' 68.70.16.0 - 68.70.31.255 [255.255.240.0] (4096)
'>>> Test_ParseCidr("192.168.1.1/24")
' 192.168.1.0 - 192.168.1.255 [255.255.255.0] (256)
'>>> Test_ParseCidr("72.28.0.0/18")
' 72.28.0.0 - 72.28.63.255 [255.255.192.0] (16384)
'>>> Test_ParseCidr("10.0.0.0/29")
' 10.0.0.0 - 10.0.0.7 [255.255.255.248] (8)
'>>> Test_ParseCidr("10.0.0.0/30")
' 10.0.0.0 - 10.0.0.3 [255.255.255.252] (4)
'>>> Test_ParseCidr("10.0.0.0/31")
' 10.0.0.0 - 10.0.0.1 [255.255.255.254] (2)
'>>> Test_ParseCidr("10.0.0.0/32")
' 10.0.0.0 - 10.0.0.0 [255.255.255.255] (1)
'>>> Test_ParseCidr("10.0.0.0/8")
' 10.0.0.0 - 10.255.255.255 [255.0.0.0] (16777216)
'>>> Test_ParseCidr("10.134.26.194/8")
' 10.0.0.0 - 10.255.255.255 [255.0.0.0] (16777216)

Once all of these tests passed, I felt confident that I didn't screw up any important details.

The Source Code

The code below is everything you need to copy and paste into a new standard code module.  I included a dummy implementation of my custom Throw() routine so that the code compiles, but I highly recommend you replace it with the real code or your own preferred approach for raising custom errors.

Option Explicit

Public Type udtIPRange
    StartIP As String
    EndIP As String
    SubnetMask As String
    Count As Double 'Supports very large values in 32-bit VBA
End Type

' ----------------------------------------------------------------
' Procedure : ParseCidr
' DateTime  : 1/23/2022 00:42
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/parsecidr/
' Purpose   : Extracts relevant network information from a CIDR string
'               of the form: x.x.x.x/y (where x is 0 - 255 and y is 0 - 32)
'               for example: 192.168.1.1/24
' See: https://www.ionos.com/digitalguide/server/know-how/cidr-classless-inter-domain-routing/
' and: https://www.ipconvertertools.com/cidr2ipranges
' ----------------------------------------------------------------
'
Function ParseCidr(Cidr As String) As udtIPRange
    Const Pattern As String = "(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})\/(\d{1,2})"
    
    Dim re As Object
    Set re = CreateObject("vbscript.regexp")
    With re
        .MultiLine = False
        .Global = False
        .Pattern = Pattern
    End With
    
    'Execute the regular expression
    Dim Matches As Object
    Set Matches = re.Execute(Cidr)
    If Matches.Count <> 1 Then Throw "Invalid CIDR format: {0}", Cidr
    
    Dim SubMatches As Object
    Set SubMatches = Matches(0).SubMatches
    If SubMatches.Count <> 5 Then Throw "Invalid CIDR format: {0}", Cidr
    
    'Extract the IP address octets
    Dim Octet(0 To 3) As Byte, i As Integer
    For i = 0 To 3
        If Val(SubMatches(i)) > 255 Then Throw "Invalid CIDR IP: {0}", Cidr
        Octet(i) = SubMatches(i)
    Next i
    
    'Extract the suffix
    Dim Suffix As Byte
    If Val(SubMatches(4)) > 32 Then Throw "Invalid CIDR suffix: {0}", Cidr
    Suffix = SubMatches(4)
    
    'Calculate the IP Address count from the suffix value
    ParseCidr.Count = 2 ^ (32 - Suffix)
    
    'Convert the suffix to a four-octet bit mask
    Dim Mask(0 To 3) As Byte
    For i = 0 To 3
        Dim OctetValue As Long
        OctetValue = Suffix - (i * 8)
        Select Case OctetValue
        Case Is < 0: Mask(i) = 0
        Case Is > 8: Mask(i) = 255
        Case Else: Mask(i) = 256 - (2 ^ (8 - OctetValue))
        End Select
    Next i
    
    'Build a string representation of the Subnet Mask
    ParseCidr.SubnetMask = Mask(0) & "." & _
                           Mask(1) & "." & _
                           Mask(2) & "." & _
                           Mask(3)
    
    'Calculate the starting IP address
    Dim Start(0 To 3) As Byte
    For i = 0 To 3
        Start(i) = Octet(i) And Mask(i)
    Next i
    
    'Build a string representation of the starting IP address
    ParseCidr.StartIP = Start(0) & "." & _
                        Start(1) & "." & _
                        Start(2) & "." & _
                        Start(3)
    
    'Calculate the ending IP address
    Dim Last(0 To 3) As Byte
    For i = 0 To 3
        Last(i) = Start(i) Or (255 - Mask(i))
    Next i
    
    'Build a string representation of the ending IP address
    ParseCidr.EndIP = Last(0) & "." & _
                      Last(1) & "." & _
                      Last(2) & "." & _
                      Last(3)
End Function


'Dummy implementation; for actual code, see:
'   https://nolongerset.com/throwing-errors-in-vba/
Sub Throw(ParamArray Args()): End Sub



Function CidrToRange(Cidr As String) As String
    Dim IPRange As udtIPRange
    
    IPRange = ParseCidr(Cidr)
    CidrToRange = IPRange.StartIP & " - " & IPRange.EndIP
End Function


'>>> Test_ParseCidr("68.70.16.0/20")
' 68.70.16.0 - 68.70.31.255 [255.255.240.0] (4096)
'>>> Test_ParseCidr("192.168.1.1/24")
' 192.168.1.0 - 192.168.1.255 [255.255.255.0] (256)
'>>> Test_ParseCidr("72.28.0.0/18")
' 72.28.0.0 - 72.28.63.255 [255.255.192.0] (16384)
'>>> Test_ParseCidr("10.0.0.0/29")
' 10.0.0.0 - 10.0.0.7 [255.255.255.248] (8)
'>>> Test_ParseCidr("10.0.0.0/30")
' 10.0.0.0 - 10.0.0.3 [255.255.255.252] (4)
'>>> Test_ParseCidr("10.0.0.0/31")
' 10.0.0.0 - 10.0.0.1 [255.255.255.254] (2)
'>>> Test_ParseCidr("10.0.0.0/32")
' 10.0.0.0 - 10.0.0.0 [255.255.255.255] (1)
'>>> Test_ParseCidr("10.0.0.0/8")
' 10.0.0.0 - 10.255.255.255 [255.0.0.0] (16777216)
'>>> Test_ParseCidr("10.134.26.194/8")
' 10.0.0.0 - 10.255.255.255 [255.0.0.0] (16777216)
Function Test_ParseCidr(Cidr As String) As String
    Dim IPRange As udtIPRange
    
    IPRange = ParseCidr(Cidr)
    Test_ParseCidr = IPRange.StartIP & " - " & IPRange.EndIP & _
                     " [" & IPRange.SubnetMask & "] (" & IPRange.Count & ")"
End Function

Sample Usage

In addition to the ParseCidr() function, the above code also includes a convenience function, CidrToRange(), that takes a CIDR and returns an IP address range.

To demonstrate the code works, first copy and paste the entire code block above into a new standard module.  Then execute the following line from the Immediate Window:

For i = 0 to 32: x = "10.0.0.0/" & i: ?x, CidrToRange(cstr(x)): Next

This one-liner will output the following results:

External references

Classless Inter-Domain Routing - Wikipedia
Wikimedia Foundation, Inc.Contributors to Wikimedia projects
CIDR to IP Addresses Ranges Converter, Subnet, Mask, Broadcast, Network Address
CIDR to IP ranges Converter. Convert from CIDR to IP, subnet mask, network, broadcast addresses and total count.
CIDR to IP ranges, network, subnet, etc | IP Converter Tools

Article references

Python-inspired Doc Tests in VBA
Doc tests are not a replacement for unit or integration testing. But they do provide the best return on investment (ROI) of any type of test, mostly because the effort to write them is near zero.
No Longer SetMike Wolfe
Throwing Errors in VBA
Introducing a frictionless alternative to Err.Raise.
No Longer SetMike Wolfe

Image by Michael Schwarzenberger from Pixabay

Never miss an article.

Sign up to receive a Sunday morning email with links and recaps of the seven articles published that week.
(I'll never sell your email. Unsubscribe any time.)


Enter your email
Subscribe

All original code samples by Mike Wolfe are licensed under CC BY 4.0