Most developers don’t have much of a clue about network fundamentals. What may be a simple thing for a network engineer or system administrator may leave a developer simply scratching his or her head.

This function can help those developers out there that need to figure out what to put into the subnet mask field of their network configuration.

Insert the first and last IP address of your range and it will calculate the subnet mask that fits these values with the starting and ending address for this subnet.

Usage:

`Dim MySubnet as String`

MySubnet = CalculateSubnetMask("192.168.91.254","192.168.90.0")

And here is the code for the function. Simply paste this into a module, and you should be good to go.

`Function CalculateSubnetMask(HighIP As String, LowIP As String) As String`

'Insert the first and last IP address of your range and it will calculate the subnet mask

'that fits these values with the starting and ending address for this subnet.

'get the high numbers

If UBound(Split(HighIP, ".")) = 3 Then

PrevPos = 0

For x = 1 To 4

Pos = InStr(PrevPos + 1, HighIP, ".", 1)

If x = 4 Then Pos = Len(HighIP) + 1

Num = Int(Mid(HighIP, PrevPos + 1, Pos - PrevPos - 1))

PrevPos = Pos

Select Case x

Case 1

highnumber4 = Num

Case 2

highnumber3 = Num

Case 3

highnumber2 = Num

Case 4

highnumber1 = Num

End Select

Next

End If

'get the Low numbers

If UBound(Split(LowIP, ".")) = 3 Then

PrevPos = 0

For x = 1 To 4

Pos = InStr(PrevPos + 1, LowIP, ".", 1)

If x = 4 Then Pos = Len(LowIP) + 1

Num = Int(Mid(LowIP, PrevPos + 1, Pos - PrevPos - 1))

PrevPos = Pos

Select Case x

Case 1

lownumber4 = Num

Case 2

lownumber3 = Num

Case 3

lownumber2 = Num

Case 4

lownumber1 = Num

End Select

Next

End If

'Set up result variables.

Dim subnetmask4 As Integer

Dim subnetmask3 As Integer

Dim subnetmask2 As Integer

Dim subnetmask1 As Integer

'Figure out the higest bit that changes.

'We do this by first doing a binary xor of the low and high numbers.

subnetmask4 = highnumber4 Xor lownumber4

subnetmask3 = highnumber3 Xor lownumber3

subnetmask2 = highnumber2 Xor lownumber2

subnetmask1 = highnumber1 Xor lownumber1

'Then we round it up to the next most significant digit.

subnetmask4 = roundup(subnetmask4)

subnetmask3 = roundup(subnetmask3)

subnetmask2 = roundup(subnetmask2)

subnetmask1 = roundup(subnetmask1)

'Figure out which set of numbers changes, then set the lower numbers from it to 0.

If (subnetmask4 < 255) Then

subnetmask3 = 0

subnetmask2 = 0

subnetmask1 = 0

End If

If (subnetmask3 < 255) Then

subnetmask2 = 0

subnetmask1 = 0

End If

If (subnetmask2 < 255) Then subnetmask1 = 0

CalculateSubnetMask = subnetmask4 & "." & subnetmask3 & "." & subnetmask2 & "." & subnetmask1

End Function

Function roundup(subnetmaskx As Integer) As Integer 'We need to figure out the most significant bit, then set the subnetmaskx to that number.

Select Case subnetmaskx

Case Is = 0

roundup = 255

Case Is < 2

roundup = 254

Case Is < 4

roundup = 252

Case Is < 8

roundup = 248

Case Is < 16

roundup = 240

Case Is < 32

roundup = 224

Case Is < 64

roundup = 192

Case Is < 128

roundup = 128

Case Is < 256

roundup = 0

Case Else

roundup = 0

End Select

End Function