How the Subnets Macro Works

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(, 1)
Right(, 2)
Right(, 3) 

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

Left((Right(, 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 This Web site includes links to download VBA language references and the VBA software development kit (SDK).

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.