As part of the same project I wrote this python script for, I created an Excel/VBA script to allow our team to quickly and consistently input all of the data required for the VPN hardware we were shipping out to over 450 locations.  The output of this Excel spreadsheet would later serve as the input to the Python script I wrote, and combined they are working out very well.

Why Bother?

Before I dive into the Excel/VBA code I’d like to give a little bit of background on my thoughts on why this was worth getting into.  From my experience, it’s fairly easy to come up with the configuration for a single site, or even a couple of sites.  You have the time to verify everything is correct, put everything in by hand, and really dedicate the time to check everything is how you want it.  This gets more difficult as you scale in size.  Even at 10 or 20 sites you start to increase your margin of error for a typo here or there, or you might run out of time and not be able to check everything as well as you would like.  Once you start to get into hundreds of devices to configure it makes things that much more complex.  It’s now very difficult, if not impossible, for a single person to manually configure each location and requires a very large amount of time dedicated to a single project.  If you take the time to automate a process, whether it is with a script, Excel, or some other combination of tools you can reduce the number of human errors as well as reduce the time and resources that would otherwise need to be dedicated to the project.

The problem

The project that led to this particular Excel VBA script required shipping out VPN hardware to over 450 locations.  Each VPN appliance was shipped to us by the carrier and was already assigned to a specific site.  There were a few different unique pieces of information, all in different spreadsheets that all needed to be tied together:

1. Spreadsheet including Site ID and MAC address of VPN appliance
2. Spreadsheet including Public IP address information from various broadband providers
3. Spreadsheet including Internal IP addresses and identifying the type of configuration each site would get

The different types of configurations for each site were important as they dictated what equipment and information would need to be set up for each site.  The three options were:

1. Broadband as a primary connection with a cellular USB as the backup
2. Combination of broadband and T1
3. T1 with cellular backup

If you had a site that fell under ‘option 1’ then it required entering in the public IP address information for that site, as well as keeping track of the cellular SIM ICCID and IMEI numbers.  If you had a site that was under ‘option 2’ you would only enter in the public broadband IP address, but would not need to package any cellular USB sticks.  If you had a site with ‘option 3’ then you would not have any broadband IP information to enter, but would need to package a cellular USB stick and record that information down.

Doing any of the above manually would be very labor intensive, flipping between multiple spreadsheets to check the type of setup the site would have, figure out which information to record and ship out. So, we automate.

VBA

Prior to this project I hadn’t written a VBA script since middle school.  I ended up having to re-learn a number of things to write this script but in the end it was worth it.  It has a lot of similarity to a pivot table, with some added extras.  The idea behind the script is this:

• Every VPN hardware appliance has a barcode on the box that includes the MAC address of the device.  We can use a barcode scanner to scan that box and do a lookup of the MAC address in Spreadsheet #1 which will give us the site ID we are working with.
• Then do another lookup of that site ID in another spreadsheet and pull the type of configuration(Broadband,Cellular,T1 combinations)
• Prompt the user for the appropriate required information, depending on which type of configuration the site will get.  For example, if the site will need Broadband and cellular, then display the fields for broadband IP address and cellular information.  If the site will be getting a T1 with cell backup, don’t prompt the user for any broadband information.
• At the end of the script, tell the user which instructions to package with the device before it gets shipped out.

Some screenshots

Here’s some screenshots of what the tool looks like when run with a Broadband/Cell Site:

1) Start off by scanning the barcode of the VPN Appliance

2)Prompt the user for the appropriate information so it can be saved to a database.

Detected this was a broadband/cellular site. Prompt the user for the appropriate information.

3)Present the user with all of the necessary information for that store so they can enter it into the VPN gateway.

Present the Broadband IP address information to the user for this specific site so they can enter it into the VPN hardware appliance.