Rajiv Bhardwaj - Official Blog

Excel based IP Functions – Presentation

Here is a presentation to explain the usage of IP based functions.

Installing Excel Addins

To install an add-in to Microsoft office excel.

1.   First move the add-in file (“.xla” or “.xll” ) to default add-ins folder. Which is usually “userfolder”\Application Data\Microsoft\AddIns.

“userfolder” is the folder with your username.

For windows XP, it is in documents & settings folder. So you will find the addins folder at

C:\Documents and Settings\[UserName]\Application Data\Microsoft\AddIns

where username is your computer user name.

For Windows Vista & later it is usually

C:\Users\[User Name]\AppData\Roaming\Microsoft\Addins

In most cases you can find correct folder by typing %AppData%\Microsoft\Addins into the “Run” and hitting enter.

You may also have a separate folder for Add-ins, in that case you can copy the addin file to that folder.

2.  For Excel 2003 or older, go to addins menu by selecting Tools->Add-ins.

3.  Then choose the desired add-in from list.  Add-in will appear in the list if you have copied it to the right Add-in folder as described in step 1.

4. If Add-in is not displayed in the list then you can use the browse button to locate the Add-in File.

For Office 2007 or later:

1.   First move the add-in file (“.xla” or “.xll” ) to default add-ins folder. Which is usually “userfolder”\Application Data\Microsoft\AddIns.

“userfolder” is the folder with your username.

For windows XP, it is in documents & settings folder. So you will find the addins folder at

C:\Documents and Settings\[UserName]\Application Data\Microsoft\AddIns

where username is your computer user name.

For Windows Vista & later it is usually

C:\Users\[User Name]\AppData\Roaming\Microsoft\Addins

In most cases you can find correct folder by typing %AppData%\Microsoft\Addins into the “Run” and hitting enter.

You may also have a separate folder for Add-ins, in that case you can copy the addin file to that folder.

2. Click on the Office button in top left corner.

3. Then choose excel options.

4.  Select  “Add-ins” , choose Manage “Excel Add-ins” and click Go.

5.  Then choose the desired add-in from list and click OK.  Add-in will appear in the list if you have copied it to the right Add-in folder as described in step 1.

6. If Add-in is not displayed in the list then you can use the browse button to locate the Add-in File.

7. If Add-in is correctly installed then it may show a relevant message.

8. Once installation is complete then you can see the “compatible” add-ins in the Add-ins tab of office ribbon. (Optional)

9. There you can select the properties and extra functionalities of the installed Add-in. (Optional)

Last 2 steps are optional and may not be available for all Add-ins.

IP_Mod: IP Modification Function

IP_Mod:  This is the primary function of RBIPFx Excel Add-in. It modifies the IP addresses.

Syntax: RB_Mod_IP( IP address, optional Step, Optional Part)

Parameters:

IP Address: As the name suggest is IPV4 IP address which you want to modify.

Step: It is the number by which you want to increment or decrement the given IP address. It is optional and default value of this parameter is 1.

Part: It is the part of IP address you want to modify. As IPv4 address as 4 parts separated by “.”.

i.e For IP 10.11.12.13, Part 1 =10, part 2=11, Part3 =12 & Part4 =13.

It is also optional parameter & default value of this part is 4. i.e. last part (Class C subnet)

USAGE: Following are some of the ways, this function can be used.

i)  You can increment the IP by one just by using the IP address parameter. i.e.

RB_IP_Mod(“10.11.12.13″,1) will give 10.11.12.14.

RB_IP_Mod

As i have set the value of Step to be 1 by default so RB_IP_Mod(“10.11.12.13″) will also do.

Dragging this function in excel column will give you a series of IPs.

ii)  But if you want to increment the IP with bigger value then you need to use the parameter STEP.

e.g

RB_IP_Mod(“10.11.12.13″, 10) will give you 10.11.12.23.

iii) If you want to create a new IP subnet then you may want to increment the 3rd part of the IP directly. You can do that by using the Part parameter.

e.g

RB_IP_Mod(“10.11.12.13″, 10, 3) will give you 10.11.22.13

iv)  Many people use IP address with subnet maskbits (e.g. xx.xx.xx.xx/24) included. This function can handle that too. It automatically detects the subnet mask separator (“/”) and uses the same format for output.

e.g

RB_IP_Mod(“10.11.12.13/24″,10,3) will give you 10.11.22.13/24 as output.

Please note that it recognizes only “/” as separator.

v)  If you want to decrement an IP address instead of incrementing then you can do that too by just giving a negative Step Value.

e.g RB_IP_Mod(10.11.12.13, -5) will give 10.11.12.8

Error Handling: It can handle basic error situations.

if you input a step value more than 255 or less than -255. Function will return an error.

e.g.

RB_IP_Mod(10.11.12.13, 258) will give “#NUM” as return. This error is inline with basic excel errors as it signifies that wrong value of number is being used.

I initially had pop-up message for such situations but it becomes quite irritating if we have a large sheet full of IP addresses.

One more situation which I have captured is that sometimes input is well within range but the resultant IP is out of range. It also generates error in such situation.

e.g.  RB_IP_Mod(10.11.12.13, 245) will give “#NUM” error as resultant IP 10.11.12.258 is not the valid one. For me this error functionality is a real life saver as I tend to forget this “255” limit.

For procedure to install the add-in check this post.

Installing Excel Addins

To download the  add-in, go to download page.

Please note that initially this function was named RB_IP_Mod but in the latest version is named IP_Mod.

Excel based IP Functios: RBIPFx

After I got tired with manually modifying IP address in excel, I decided to give a try to creating some kind of formula.

I got some success with inbuilt “left” & “right” functions. e.g. by using something like this to increment the IP address at position A1.

LEFT(A1,FIND(“.”,A1,FIND(“.”,A1,FIND(“.”,A1,1)+1)+1))&RIGHT(A1,LEN(A1)-FIND_

(“.”,A1,FIND(“.”,A1,FIND(“.”,A1,1)+1)+1))+1

But soon it became quite cumbersome because of 3 reasons.

1. Every time I wanted to use this formula I had to change the reference cell ID “A1″ manually. It defeated the original purpose of automation.

2.  It didn’t work well for changing the Class A or B IP Subnets. I mean updating any of the first three “parts” of IP address.

3. As I started working on some IP hungry nodes.  Handling of huge number of IPs became almost impossible by this way.

Don’t have much experience with macros & all but still was able to create a set working function.

I have complied all the IP address related functions in Add-in format which can be downloaded from this page. Although, .xla format is not fully portable as excel sheets created by one user cannot be used by another user without updating the links. But it’s the easiest way to transfer reusable functions.

This Add-in RBIPFx (Rajiv Bhardwaj’s IP functions) include following functions in present version (0.1).

1. RB_IP_Bits2Mask

2. RB_IP_ErrChk

3. RB_IP_Mod: Click here for details.

4. RB_IP_NextSub

In the recent version i have added some new functions and also removed the ‘RB_” prefi:

1. IP_Bits2Mask
2. IP_ErrChk
3. IP_Hosts
4. IP_IP2Mbits
5. IP_Mod
6. IP_NextSub
7. IP_Bcast
8. IP_Count
9. IP_IsExist
10. IP_Mask2Bits
11. IP_Subnet

For procedure to install the add-in check this post.

Installing Excel Addins

Also please see the presentation at following link

Presentation

« Older posts

Copyright © 2016 Rajiv Bhardwaj – Official Blog

Theme by Anders NorenUp ↑