Tuesday, March 17, 2015

Custom Formula: IPFromDEC (IP address from decimal)

See part 1 of this thread.
See part 2 of this thread.
Install Instructions

UPDATE: I cleaned up the code. It may be a little less intuitive, but the code itself is simpler. I added a new boolean checker: IPIsValidIP(), which returns true if the IP address is a valid dotted decimal IP address.

UPDATE: I added a new formula: IPGetCIDRList(). This list takes two IP addresses and defines all the address blocks between them, inclusively. For example:


This is handy if you want to get all the address blocks in a range. The more simple the range, the shorter the list of summarizations. The more complex the range, the longer the list. For example:


Download using the same link as below. If you're upgrading, close Excel and copy the downloaded XLAM file to the same location as your existing IPConversion.xlam file. If you don't know where that is, look in your add ins list (File>>Options>>Add Ins, look in the location column).

UPDATE: I compiled and have now published my IPConversion.xlam.  How to install this add-in.
How to use:
FunctionArgument 1Argument 2192.168.15.3410.20.30.40335.20.30.40142.20.30.40
IP2DECConverts from dotted decimal to decimal3232239394169090600Invalid IP Address2383683112
IPFROMDECConverts from decimal to dotted decimal192.168.15.3410.20.30.40#VALUE!
IPNetworkReturns the network number given an IP address and mask24192. IP Address142.20.30.0
IPIsInSubnetDetermines if the given IP address is within the given subnet192.168.0.016TRUEFALSEFALSEFALSE
IPGetOctetReturns the octet specified (1-4)31530Invalid IP Address30
IPGetCIDRListReturns a comma separated list of CIDR address blocks between the two provided IP addresses192.168.255.255192.168.15.34/31,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
IPIsValidIPReturns true if the specified IP address is a valid IP addressTRUETRUEFALSETRUE

In a previous post, I showed how to convert an IP address from dotted decimal notation to a decimal number. Well, i found myself in a situation where i needed to do the reverse.  So, there are two ways of doing this.

The first involves using a big formula to chop the decimal value into its equivalent dotted decimal counter parts. The formula goes like this (this formula references cell B2 where the decimal format of an IP address should be):

While this is nice, it would be even nicer if i could just do something like this:

This is the second method.  If you've already created your IP2DEC.xlam file and have it enabled as an add in, you're ready to go, you can add a custom formula to break the IP address back out into the same add-in (if you haven't, click here to see how).

Open a blank workbook in Excel.  Press Alt+F11 or click 'Visual Basic' on the Developer tab in the ribbon bar.  If the project explorer isn't visible, show it by pressing Ctrl+R or by choosing View>>Project Explorer.  You should see two projects in there, one for the new blank workbook that opened and one for the IP2DEC add-in.  You should see Module1 under the IP2DEC add-in (if you don't see this, you didn't do the steps on the previous post).  Double click it.  You should now see the IP2DEC public function code.  Now all you need to do append some code to the bottom of the module that will define the function for converting back to dotted decimal format.
Public Function IPFROMDEC(ipaddress) As String
If ipaddress + 1 - 1 > 4294967295# Then GoTo toobig
Dim firstoctet As String, secondoctet As String, thirdoctet As String, fourthoctet As String
firstoctet = Int(ipaddress / (2 ^ 24))
secondoctet = ipaddress - (firstoctet * 2 ^ 24)
secondoctet = Int(secondoctet / (2 ^ 16))
thirdoctet = ipaddress - (firstoctet * 2 ^ 24) - (secondoctet * 2 ^ 16)
thirdoctet = Int(thirdoctet / (2 ^ 8))
fourthoctet = ipaddress - (firstoctet * 2 ^ 24) - (secondoctet * 2 ^ 16) - (thirdoctet * 2 ^ 8)
fourthoctet = Int(fourthoctet)
Select Case 255
Case Is < firstoctet
GoTo toobig
Case Is < secondoctet
GoTo toobig
Case Is < thirdoctet
GoTo toobig
Case Is < fourthoctet
GoTo toobig
End Select
IPFROMDEC = firstoctet & "." & secondoctet & "." & thirdoctet & "." & fourthoctet
Exit Function
IPFROMDEC = "Invalid IP Address"
End Function
Hit the save button and go try it out.  Put an IP address in one cell and use IP2DEC() to convert it to decimal.  Then use IPFROMDEC() to convert it back.

You might think this an exercise in futility, however, this can come in handy when trying to parse out IP address blocks given CIDR notation.  For example, if you wanted to calculate the starting and ending IP address for the block of IP addresses, I'd have the following:

 The result of the formula in A2 is shown in B2.  This can also be very handy when trying to determine whether or not a given IP address is within a given subnet.