How the Subnets Macro Works

Although the Subnets macro is written in Visual Basic for Applications (VBA), its logic and codeare similar to the logic and code you’d see in VBScript scripts. As Listing 1 shows, the macrobegins by declaring a few variables, defining their types, and assigning them some initial values.The macro then instructs Microsoft Excel to focus its attention on Sheet1. As callout A in Listing1 shows, the Cells. Value property fetches the contents of the cell at “row, column”, which in thiscase, is the first IP address in the list. The property passes this IP address to the GetSubnetfunction, which is the heart of the macro.

In VBA, a Function procedure can be used on the right side of an expression and therefore canreturn a value. The function’s name is used as a variable within the function; the value that thisvariable contains when the function exits is assigned to the variable on the left side of theexpression back in the main routine. In this case, the GetSubnet function’s name acts as avariable within the GetSubnet function, as callout C in Listing 1, in the main article shows. When the functioncompletes, this variable contains the desired subnet string. The desired subnet string is assignedto the Subnet1 variable at callout A.

During the execution of the GetSubnet function, the IP variable contains the IP addresses. Usinga never-ending Do While loop, the GetSubnet function parses each IP address to find the subnetportion. The extraction of the subnet is accomplished by using VBA’s handy Left and Rightfunctions, which return a certain number of characters from the left and right side, respectively,of the text string you pass to them. You specify the text string as the first parameter and thenumber of characters to return as the second parameter. For example, the code

Right(192.168.1.25, 1)Right(192.168.1.25, 2)Right(192.168.1.25, 3) 

returns 5, 25, and .25, respectively. By combining the Right function with the Left function in thecode

Left((Right(192.168.1.25, 3)), 1) 

you can find the period (.) that marks the end of the IP address’s third octet. After finding therightmost period, you can subtract the offset value (i.e., the value that IPOffset contains, whichis 3 at this point) from the length of the IP address as a whole (which is 12). The result will bethe length of the subnet portion of the address. You can then use the Left function again toextract the subnet from the IP address.

The Subnets macro assumes that you’re dealing with a 24-bit (standard Class C) subnet.However, you can easily adapt the function to return a 16-bit subnet by setting the IPOffsetvariable in the function to an initial value of 4 rather than 2.

After getting the subnets of the first and second addresses, the macro compares the two. Whenthe values of Subnet1 and Subnet2 are equal, the macro increments the Row variable by 1 andcalls the GetSubnet function to extract the subnet from the next address in the list. When thevalues of Subnet1 and Subnet2 aren’t equal, the code at callout B in Listing 1, in the main article runs. This codefirst activates Sheet2 and writes to the appropriate cell (RowWrite, 1) Subnet1’s value appendedwith the string .0 to make it a proper subnet notation. The code then increments the RowWritevariable by 1. Afterward, the macro shifts its focus back to Sheet1, gets the next IP address, andrepeats the process. When the end of the list is reached, Sheet2 contains a list of uniquesubnets.

For more information about VBA, go to http://msdn.microsoft.com/vba. This Web site includeslinks to download VBA language references and the VBA software development kit (SDK).