I'm not a programmer! But I'm learning.
I found these functions that do most of what I need them to at this site. They do a varity of IP calculation functions. Like Sorting IPv4 addresses from lowest to highest, or finding IP subnet conflicts.
The site where I found them is:
http://trk.free.fr/ipcalc/
They mostly work but the auto Subnetting portion doesn't quite work as expected. What I would like to see is if I enter an IP address with with a slash notation (i.e. 192.168.1.0/24) it would take that address and split it up by an entered variable.
For instance if i enter into cell - start address - A1 --> 192.168.1.0/24
then split it into 2 equal subnets - last address - A2 --> 192.168.2.0/24
Then add the number of subnets you want - A3 --> 4
The output would be in an array - B1 to Bwhaterver --> 192.168.1.0/25
192.168.1.128/25
192.168.2.0/25
192.168.2.128/25
the current fuction doesn't allow for a user enter "how many subnets" varible
and even in it's current state it doesn't much work
I'm working on a excel sheet that calculates IP address. I got it mostly done but I'm stuggling with this last feature that i wanted to add. Which is why I went to Google.
Below are the relevent functions, but the full list of functions and excel sheet can be found at the site listed above.
I found these functions that do most of what I need them to at this site. They do a varity of IP calculation functions. Like Sorting IPv4 addresses from lowest to highest, or finding IP subnet conflicts.
The site where I found them is:
http://trk.free.fr/ipcalc/
They mostly work but the auto Subnetting portion doesn't quite work as expected. What I would like to see is if I enter an IP address with with a slash notation (i.e. 192.168.1.0/24) it would take that address and split it up by an entered variable.
For instance if i enter into cell - start address - A1 --> 192.168.1.0/24
then split it into 2 equal subnets - last address - A2 --> 192.168.2.0/24
Then add the number of subnets you want - A3 --> 4
The output would be in an array - B1 to Bwhaterver --> 192.168.1.0/25
192.168.1.128/25
192.168.2.0/25
192.168.2.128/25
the current fuction doesn't allow for a user enter "how many subnets" varible
and even in it's current state it doesn't much work
I'm working on a excel sheet that calculates IP address. I got it mostly done but I'm stuggling with this last feature that i wanted to add. Which is why I went to Google.
Below are the relevent functions, but the full list of functions and excel sheet can be found at the site listed above.
Code:
'----------------------------------------------
' IpRangeToCIDR
'----------------------------------------------
' returns a network or a list of networks given the first and the
' last address of an IP range
' if this function is used in a array formula, it may return more
' than one network
' example:
' IpRangeToCIDR("10.0.0.1","10.0.0.254") returns 10.0.0.0/24
' IpRangeToCIDR("10.0.0.1","10.0.1.63") returns the array : 10.0.0.0/24 10.0.1.0/26
' note:
' 10.0.0.0 or 10.0.0.1 as the first address returns the same result
' 10.0.0.254 or 10.0.0.255 (broadcast) as the last address returns the same result
Function IpRangeToCIDR(ByVal firstAddr As String, ByVal lastAddr As String) As Variant
firstAddr = IpAnd(firstAddr, "255.255.255.254") ' set the last bit to zero
lastAddr = IpOr(lastAddr, "0.0.0.1") ' set the last bit to one
Dim list() As String
n = 0
Do
l = 0
Do ' find the largest network which first address is firstAddr and which last address is not higher than lastAddr
' build a network of length l
' if it does not comply the above conditions, try with a smaller network
l = l + 1
net = firstAddr & "/" & l
ip1 = IpAnd(firstAddr, IpMask(net)) ' first @ of this network
ip2 = IpOr(firstAddr, IpWildMask(net)) ' last @ of this network
net = ip1 & "/" & l ' rebuild the network with the first address
diff = IpDiff(ip2, lastAddr) ' difference between the last @ of this network and the lastAddr we need to reach
Loop While (l < 32) And ((ip1 <> firstAddr) Or (diff > 0))
n = n + 1
ReDim Preserve list(1 To n)
list(n) = net
firstAddr = IpAdd(ip2, 1)
Loop While (diff < 0) ' if we haven't reached the lastAddr, loop to build another network
Dim resultArray() As Variant
ReDim resultArray(1 To n + 1, 1 To 1)
For i = 1 To n
resultArray(i, 1) = list(i)
Next i
IpRangeToCIDR = resultArray
End Function
'----------------------------------------------
' IpAdd
'----------------------------------------------
' example:
' IpAdd("192.168.1.1"; 4) returns "192.168.1.5"
' IpAdd("192.168.1.1"; 256) returns "192.168.2.1"
Function IpAdd(ByVal ip As String, offset As Double) As String
IpAdd = IpBinToStr(IpStrToBin(ip) + offset)
End Function
'----------------------------------------------
' IpAnd
'----------------------------------------------
' bitwise AND
' example:
' IpAnd("192.168.1.1"; "255.255.255.0") returns "192.168.1.0"
Function IpAnd(ByVal ip1 As String, ByVal ip2 As String) As String
' compute bitwise AND from right to left
Dim result As String
While ((ip1 <> "") And (ip2 <> ""))
Call IpBuild(IpParse(ip1) And IpParse(ip2), result)
Wend
IpAnd = result
End Function
'----------------------------------------------
' IpBuild
'----------------------------------------------
' Builds an IP address by iteration from right to left
' Adds "ip_byte" to the left the "ip"
' If "ip_byte" is greater than 255, only the lower 8 bits are added to "ip"
' and the remaining bits are returned to be used on the next IpBuild call
' example 1:
' if ip="168.1.1"
' IpBuild(192, ip) returns 0 and ip="192.168.1.1"
' example 2:
' if ip="1"
' IpBuild(258, ip) returns 1 and ip="2.1"
Function IpBuild(ip_byte As Double, ByRef ip As String) As Double
If ip <> "" Then ip = "." + ip
ip = Format(ip_byte And 255) + ip
IpBuild = ip_byte \ 256
End Function
'----------------------------------------------
' IpParse
'----------------------------------------------
' Parses an IP address by iteration from right to left
' Removes one byte from the right of "ip" and returns it as an integer
' example:
' if ip="192.168.1.32"
' IpParse(ip) returns 32 and ip="192.168.1" when the function returns
Function IpParse(ByRef ip As String) As Integer
Dim pos As Integer
pos = InStrRev(ip, ".")
If pos = 0 Then
IpParse = Val(ip)
ip = ""
Else
IpParse = Val(Mid(ip, pos + 1))
ip = Left(ip, pos - 1)
End If
End Function
'----------------------------------------------
' IpOr
'----------------------------------------------
' bitwise OR
' example:
' IpOr("192.168.1.1"; "0.0.0.255") returns "192.168.1.255"
Function IpOr(ByVal ip1 As String, ByVal ip2 As String) As String
' compute bitwise OR from right to left
Dim result As String
While ((ip1 <> "") And (ip2 <> ""))
Call IpBuild(IpParse(ip1) Or IpParse(ip2), result)
Wend
IpOr = result
End Function
'----------------------------------------------
' IpBinToStr
'----------------------------------------------
' Converts a binary IP address to text
' example:
' IpBinToStr(16909060) returns "1.2.3.4"
Function IpBinToStr(ByVal ip As Double) As String
Dim divEnt As Double
Dim i As Integer
i = 0
IpBinToStr = ""
While i < 4
If IpBinToStr <> "" Then IpBinToStr = "." + IpBinToStr
divEnt = Int(ip / 256)
IpBinToStr = Format(ip - (divEnt * 256)) + IpBinToStr
ip = divEnt
i = i + 1
Wend
End Function
'----------------------------------------------
' IpMask
'----------------------------------------------
' returns an IP netmask from a subnet
' both notations are accepted
' example:
' IpMask("192.168.1.1/24") returns "255.255.255.0"
' IpMask("192.168.1.1 255.255.255.0") returns "255.255.255.0"
Function IpMask(ByVal ip As String) As String
IpMask = IpBinToStr(IpMaskBin(ip))
End Function
'----------------------------------------------
' IpWildMask
'----------------------------------------------
' returns an IP Wildcard (inverse) mask from a subnet
' both notations are accepted
' example:
' IpWildMask("192.168.1.1/24") returns "0.0.0.255"
' IpWildMask("192.168.1.1 255.255.255.0") returns "0.0.0.255"
Function IpWildMask(ByVal ip As String) As String
IpWildMask = IpBinToStr(((2 ^ 32) - 1) - IpMaskBin(ip))
End Function
'----------------------------------------------
' IpMaskBin
'----------------------------------------------
' returns binary IP mask from an address with / notation (xx.xx.xx.xx/yy)
' example:
' IpMask("192.168.1.1/24") returns 4294967040 which is the binary
' representation of "255.255.255.0"
Function IpMaskBin(ByVal ip As String) As Double
Dim bits As Integer
bits = IpSubnetLen(ip)
IpMaskBin = (2 ^ bits - 1) * 2 ^ (32 - bits)
End Function
'----------------------------------------------
' IpSubnetLen
'----------------------------------------------
' get the mask len from a subnet
' example:
' IpSubnetLen("192.168.1.1/24") returns 24
' IpSubnetLen("192.168.1.1 255.255.255.0") returns 24
Function IpSubnetLen(ByVal ip As String) As Integer
Dim p As Integer
p = InStr(ip, "/")
If (p = 0) Then
p = InStr(ip, " ")
If (p = 0) Then
IpSubnetLen = 32
Else
IpSubnetLen = IpMaskLen(Mid(ip, p + 1))
End If
Else
IpSubnetLen = Val(Mid(ip, p + 1))
End If
End Function
