' Get the mask len from a subnet and remove the mask from the address ' removes the netmask notation at the end of the IP NotMask = 2 ^ 32 - 1 - IpStrToBin(ipmaskstr) ' IpMaskLen("255.255.255.0") returns 24 which is the number of bits of the subnetwork prefixįunction IpMaskLen(ByVal ipmaskstr As String) As Integer ' returns prefix length from a mask given by a string notation (xx.xx.xx.xx) IpInvertMask = IpBinToStr(((2 ^ 32) - 1) - IpStrToBin(mask)) ' IpInvertMask("0.0.0.255") returns "255.255.255.0"įunction IpInvertMask(ByVal mask As String) As String ' returns an IP Wildcard (inverse) mask from a subnet mask IpWildMask = IpBinToStr(((2 ^ 32) - 1) - IpMaskBin(ip)) ' returns an IP Wildcard (inverse) mask from a subnet ' IpSetByte("192.168.1.1" 4 20) returns "192.168.1.20"įunction IpSetByte(ByVal ip As String, pos As Integer, newvalue As Integer) As String ' set one byte in an ip address given its position and value ' IpGetByte("192.168.1.1" 1) returns 192įunction IpGetByte(ByVal ip As String, pos As Integer) As Integer ' get one byte from an ip address given its position IpComp = ((Val(Left(ip1, pos1 - 1)) And mask) = (Val(Left(ip2, pos2 - 1)) And mask)) ' IpComp("10.0.0.0", "10.1.0.0", 16) returns FALSEįunction IpComp(ByVal ip1 As String, ByVal ip2 As String, ByVal n As Integer) As Boolean ' Compares the first 'n' bits of ip1 and ip2 Offset = IpBuild(IpParse(ip) + offset, result) ' another implementation of IpAdd which not use the binary representationįunction IpAdd2(ByVal ip As String, offset As Double) As String ' IpSubnetToBin("1.2.3.0/24") returns 16909056įunction IpSubnetToBin(ByVal ip As String) As DoubleĬall IpBuild(IpParse(ip1) And IpParse(ip2), result) ' This function is similar to IpStrToBin but ignores the host part of the address IpBinToStr = Format(ip - (divEnt * 256)) + IpBinToStr
While i "" Then IpBinToStr = "." + IpBinToStr IpStrToBin = IpStrToBin * 256 + Val(Left(ip, pos - 1))įunction IpBinToStr(ByVal ip As Double) As String IpIsValid = (IpBinToStr(IpStrToBin(ip)) = ip)įunction IpStrToBin(ByVal ip As String) As Double ' no space, no extra zero, no incorrect valueįunction IpIsValid(ByVal ip As String) As Boolean ' Returns true if an ip address is formated exactly as it should be:
' You should have received a copy of the GNU General Public License ' GNU General Public License for more details. ' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. ' but WITHOUT ANY WARRANTY without even the implied warranty of ' This program is distributed in the hope that it will be useful,
' the Free Software Foundation, either version 3 of the License, or
' it under the terms of the GNU General Public License as published by ' This program is free software: you can redistribute it and/or modify ' Copyright 2010-2017 Thomas Rohmer-Kretz
To add these functions in your Excel document, you must first save your document with the xslm format, then press Alt-F11, create a new module and copy/paste the code below in the editor IP subnet calculator for Excel, LibreOffice and Google Sheets IP calculator for Excel, LibreOffice and Google Sheets subnet calculator and IP address functions for your spreadsheet