Calculer des adresses réseaux, broadcast et les adresses utilisables sous Excel
Ce billet est consacré à une contribution de Guillaume D., alias y0m, qui a décidé de me faire parvenir un ensemble de macros Excel qu’il a été amené à coder pour permettre le calcul d’adresses réseaux, de broadcast ou la première et dernière adresse utilisable d’une plage d’adresses sous Excel en fonction d’une adresse de réseau et de son masque.
L’intégralité de son code est présentée ici, et c’est avec grand plaisir que je remercie Guillaume pour la rendre publique :
‘ copyleft y0m ‘
‘ ‘
‘ This Function returns the network broadcast address of the host whose ip’
‘ and network mask address are passed as arguments ‘
‘ @param IPAddress String representation of the ip address ‘
‘ @param SubnetMask String representation of the network mask address ‘
‘ @return The broadcast address of the network in String format ‘
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”‘
Public Function GetNetworkAddress(ByVal IPAddress As String, ByVal SubnetMask As String) As String
””””””’
‘ VARIABLES ‘
””””””’
Dim ipTab, maskTab As Variant
Dim i As Integer
”””””””’
‘ BODY OF SUB ‘
”””””””’
‘ Split the addresses with dot (.) as separator
ipTab = Split(IPAddress, “.”)
maskTab = Split(SubnetMask, “.”)
‘ For itch digit of the address, do a logical AND between Ip digit and the mask digit
‘ Result is stored and concatened in the GetNetworkAddress variable and return it automatically
For i = 0 To 3
If (i <> 3) Then
GetNetworkAddress = GetNetworkAddress & (ipTab(i) And maskTab(i)) & “.”
Else
GetNetworkAddress = GetNetworkAddress & (ipTab(i) And maskTab(i))
End If
Next
End Function
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”‘
‘ copyleft y0m ‘
‘ ‘
‘ This Function returns the first available IP address of the network of ‘
‘ the host whose ip and network mask address are passed as arguments ‘
‘ @param IPAddress String representation of the ip address ‘
‘ @param SubnetMask String representation of the network mask address ‘
‘ @return First available Ip address of the network in String format ‘
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”‘
Public Function GetFirstAddress(ByVal IPAddress As String, ByVal SubnetMask As String) As String
””””””’
‘ VARIABLES ‘
””””””’
Dim ipTab As Variant
Dim i As Integer
”””””””’
‘ BODY OF SUB ‘
”””””””’
‘ Get the Network address and split it with dot (.) as separator
ipTab = Split(GetNetworkAddress(IPAddress, SubnetMask), “.”)
‘ Get the last digit of the network address and add one to get the last digit of the first available address
ipTab(UBound(ipTab)) = ipTab(UBound(ipTab)) + 1
‘ Build the first address by using digits of the network address, concatened in the GetFirstAddress variable and return it automatically
For i = LBound(ipTab) To UBound(ipTab)
If (i <> 3) Then
GetFirstAddress = GetFirstAddress & ipTab(i) & “.”
Else
GetFirstAddress = GetFirstAddress & (ipTab(i))
End If
Next
End Function
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”‘
‘ copyleft y0m ‘
‘ ‘
‘ This Function returns the last available IP address of the network of ‘
‘ the host whose ip and network mask address are passed as arguments ‘
‘ @param IPAddress String representation of the ip address ‘
‘ @param SubnetMask String representation of the network mask address ‘
‘ @return Last available Ip address of the network in String format ‘
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”‘
Public Function GetLastAddress(ByVal IPAddress As String, ByVal SubnetMask As String) As String
””””””’
‘ VARIABLES ‘
””””””’
Dim ipTab As Variant
Dim i As Integer
”””””””’
‘ BODY OF SUB ‘
”””””””’
‘ Get the broadcast address and split it with dot (.) as separator
ipTab = Split(GetBroadCastAddress(IPAddress, SubnetMask), “.”)
‘ Get the last digit of the network address and minus one to get the last digit of the last available address
ipTab(UBound(ipTab)) = ipTab(UBound(ipTab)) – 1
‘ Build the last address by using digits of the broadcast address, concatened in the GetLastAddress variable and return it automatically
For i = LBound(ipTab) To UBound(ipTab)
If (i <> 3) Then
GetLastAddress = GetLastAddress & ipTab(i) & “.”
Else
GetLastAddress = GetLastAddress & (ipTab(i))
End If
Next
End Function
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”‘
‘ copyleft y0m ‘
‘ ‘
‘ This Function returns the broadcast address of the network of ‘
‘ the host whose ip and network mask address are passed as arguments ‘
‘ @param IPAddress String representation of the ip address ‘
‘ @param SubnetMask String representation of the network mask address ‘
‘ @return Broadcast Ip address of the network in String format ‘
‘”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”"”‘
Public Function GetBroadCastAddress(ByVal IPAddress As String, ByVal SubnetMask As String) As String
””””””’
‘ VARIABLES ‘
””””””’
Dim a, b, c, d As Long
‘Dim e, f, g, h As Long
Dim ipTab, maskTab As Variant
Dim i As Integer
””””””’
‘ VARIABLES ‘
””””””’
‘ Split the addresses with dot (.) as separator
ipTab = Split(IPAddress, “.”)
maskTab = Split(SubnetMask, “.”)
‘ For itch digit of the address, do a NO (same as “Xor 11111111″) to the digit of the mask and do a OR between the result and the digit of the Ip address
‘ Result is stored and concatened in the GetBroadCastAddress variable and return it automatically
For i = 0 To 3
a = ipTab(i)
b = maskTab(i)
c = b Xor 255
d = a Or c
If (i <> 3) Then
GetBroadCastAddress = GetBroadCastAddress & d & “.”
Else
GetBroadCastAddress = GetBroadCastAddress & d
End If
Next
End Function
Public Sub quiSertaRien()
Dim rez As String
MsgBox GetNetworkAddress(“192.168.222.2″, “255.255.255.240″)
MsgBox GetFirstAddress(“192.168.222.2″, “255.255.255.240″)
MsgBox GetLastAddress(“192.168.222.2″, “255.255.255.240″)
MsgBox GetBroadCastAddress(“192.168.222.2″, “255.255.255.240″)
End Sub
Si vous souhaitez plus d’informations sur l’utilisation de ce code ou des détails concernant sa mise en place, n’hésitez pas à me solliciter par mail ou à poster des commentaires sur ce billet, l’auteur se chargera très certainement d’y répondre au mieux !
Laissez votre réponse !
Vous devez être connecté pour poster un commentaire