# 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).

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:

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.

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
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

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

Dim Start(0 To 3) As Byte
For i = 0 To 3
Next i

'Build a string representation of the starting IP address
ParseCidr.StartIP = Start(0) & "." & _
Start(1) & "." & _
Start(2) & "." & _
Start(3)

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:

### Article references

Image by Michael Schwarzenberger from Pixabay

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