Custom Formula: IP2DEC (IP address to decimal)

THIS CONTENT IS NOW HOSTED ON GITHUB: https://github.com/sweenig/ipfromdec/



I mentioned in my previous post that i needed to build a custom function around converting IP addresses to decimal numbers. I wanted to share how to add your own custom functions to excel. This involves VBA and excel add-ins. Essentially, you can create functions in VBA then call them from cells in any spreadsheet. The nice thing about this is that you can take a very complex formula, like the one i posted before about converting an IP address to decimal form, and make a very simple call to calculate the result.

TLDR; take me to the download.

In other words, you can change the formula from this:
=VALUE(LEFT(A1,FIND(".",A1)-1))*2^24+VALUE(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1))*2^16+VALUE(MID(A1,FIND(".",A1,FIND(".",A1)+1)+1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)-FIND(".",A1,FIND(".",A1)+1)-1))*2^8+VALUE(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)))

To this:

=IP2DEC(A1)

The process is fairly easy if you only want to use the formula in one spreadsheet. If you want to use it globally, there are a few extra (but easy) steps.

1. The first is to open Excel and save a blank workbook as an xlam file (Excel add-in). Just go to Save As..., choose 'other formats' and pick Excel Add-in from the drop down. When you do, it will change the location, there's no need for you to save it in a different location. Give it a good name like MyFunctions.xlam.
2. Then enable that add in. Go to the Office button and click 'Excel options'. Then go to Add-Ins and click 'Go...'. Check the checkbox next to your add-in and you're done. Now you've got a place that you can drop VBA functions that will always be available. Now to get some in there.
3. Now open a blank excel document. For the purposes of this example, i'll add some data. In A1, i'll put =RANDBETWEEN(0,255)&"."&RANDBETWEEN(0,255)&"."&RANDBETWEEN(0,255)&"."&RANDBETWEEN(0,255). This will generate a random IP address that i'll use to test my function on. I'll put the above formula in B1 as a control to make sure my custom function works properly.

Now to create your custom function:

1. Press Alt+F11 or go to Developer>>View Code (you might not see this option, it's hidden by default).
2. You'll notice that the code browser will show two items, your current new book and your blank add-in. Whether or not your custom function becomes global or stays within the current workbook will depend on where you insert the module. To make it local to the current workbook, make sure the current workbook is selected. To make it global, make sure your add-in is selected.
3. Click Insert>>Module.
4. Now you can start building your function. Start with 'Public Function IP2DEC(ipaddress as string)'. VB will automatically add the End Function section. IP2DEC is the name of the function you're going to create and (ipaddress) is the argument that is required.
5. I put in the following code for my function. I know, it can be done more easily using more sophisticated commands, but I did it this way to illustrate:
   Public Function IP2DEC(ipaddress As String)
   'find the location of the first dot
   Dim firstdot As Integer, seconddot As Integer, thirddot As Integer
   firstdot = InStr(ipaddress, ".")
   seconddot = InStr(firstdot + 1, ipaddress, ".")
   thirddot = InStr(seconddot + 1, ipaddress, ".")
   'get the strings of each octet
   Dim firstoct, secondoct, thirdoct, fourthoct As String
   firstoct = Left(ipaddress, firstdot - 1)
   secondoct = Mid(ipaddress, firstdot + 1, seconddot - firstdot - 1)
   thirdoct = Mid(ipaddress, seconddot + 1, thirddot - seconddot - 1)
   fourthoct = Right(ipaddress, Len(ipaddress) - thirddot)
   'convert the strings to numbers
   Dim dfirstoct, dsecondoct, dthirdoct, dfourthoct As Integer
   dfirstoct = Val(firstoct)
   dsecondoct = Val(secondoct)
   dthirdoct = Val(thirdoct)
   dfourthoct = Val(fourthoct)
   'calculate & return the result
   IP2DEC = (dfirstoct * 2 ^ 24) + (dsecondoct * 2 ^ 16) + (dthirdoct * 2 ^ 8) + dfourthoct
   End Function
6. To save the function, go to File>>Save NameOfYourFile.xlam.
7. Now close the VB window to get back to your blank spreadsheet.
8. Call your function. In my case, in B2, I put '=IP2DEC(A1)'. This gave me the exact same value as what is in B1 but using a largely simplified formula.

Of course, if someone else opens the sheet without the add-in, they'll get a #Name error. Then you can show your Excel prowess by emailing them your add in.

You can follow the same procedure to add in other custom formulas. For example, i've always been annoyed that Excel doesn't have an IfBlank() function. Essentially, I want the equivalent of =If(IsBlank(A1),"something if blank","something if not blank"). This is the function i created:
The following function could be used to evaluate whether a cell is blank or not and return one of two values:
Public Function IfBlank(rCell As Range, whatiftrue, whatiffalse)
'
'This function checks to see if a cell is blank. If it is, whatiftrue is returned. If not, whatiffalse is returned.
'
If rCell = "" Then IfBlank = whatiftrue Else IfBlank = whatiffalse
End Function

The following function could be used to turn a binary number back into an IP address:
Public Function DEC2IP(ByVal LongIP As Double) As String
'
'This function returns an IP address from a long (integer) IP address
'
Dim i As Integer, num As Double
For i = 1 To 4
num = Int(LongIP / 256 ^ (4 - i))
LongIP = LongIP - (num * 256 ^ (4 - i))
If i = 1 Then 'if that's all return the result
DEC2IP = num
Else 'if that's not all add the current octet to the result and continue
DEC2IP = DEC2IP & "." & num
End If
Next
End Function

Enjoy!

See part 3 of this post here.