Although the Subnets macro is written in Visual Basic for Applications (VBA), its logic and code
are similar to the logic and code you’d see in VBScript scripts. As Listing 1 shows, the macro
begins 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 Listing
1 shows, the Cells. Value property fetches the contents of the cell at “row, column”, which in this
case, is the first IP address in the list. The property passes this IP address to the GetSubnet
function, which is the heart of the macro.
In VBA, a Function procedure can be used on the right side of an expression and therefore can
return a value. The function’s name is used as a variable within the function; the value that this
variable contains when the function exits is assigned to the variable on the left side of the
expression back in the main routine. In this case, the GetSubnet function’s name acts as a
variable within the GetSubnet function, as callout C in Listing 1, in the main article shows. When the function
completes, this variable contains the desired subnet string. The desired subnet string is assigned
to the Subnet1 variable at callout A.
During the execution of the GetSubnet function, the IP variable contains the IP addresses. Using
a never-ending Do While loop, the GetSubnet function parses each IP address to find the subnet
portion. The extraction of the subnet is accomplished by using VBA’s handy Left and Right
functions, 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 the
number 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 the
code
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 the
rightmost period, you can subtract the offset value (i.e., the value that IPOffset contains, which
is 3 at this point) from the length of the IP address as a whole (which is 12). The result will be
the length of the subnet portion of the address. You can then use the Left function again to
extract 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 IPOffset
variable 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. When
the values of Subnet1 and Subnet2 are equal, the macro increments the Row variable by 1 and
calls the GetSubnet function to extract the subnet from the next address in the list. When the
values of Subnet1 and Subnet2 aren’t equal, the code at callout B in Listing 1, in the main article runs. This code
first activates Sheet2 and writes to the appropriate cell (RowWrite, 1) Subnet1’s value appended
with the string .0 to make it a proper subnet notation. The code then increments the RowWrite
variable by 1. Afterward, the macro shifts its focus back to Sheet1, gets the next IP address, and
repeats the process. When the end of the list is reached, Sheet2 contains a list of unique
subnets.
For more information about VBA, go to http://msdn.microsoft.com/vba. This Web site includes
links to download VBA language references and the VBA software development kit (SDK).