• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4679
  • Last Modified:

Subnetting in an Excel spreadsheet

Hi Experts
I am trying to create a spreadsheet that has all of my networks and subnetworks in it.   My goal is pretty simple, but I can't think of a way to easily do it--

Column 1 should have something like this:
/16
10.0.0.0
10.1.0.0
10.2.0.0
10.3.0.0

Now, I want to be able to click on the "+" sign to "open up" 10.0.0.0 with a heading to the right of:
10.0.0.0
10.0.128.0

Then, to the right of 10.1.0.0 it would be the same

10.1.0.0
10.1.128.0

and so on....

/18
10.0.0.0
10.0.64.0
10.0.128.0
10.0.192.0

Then, I woud use the group outline menu to collapse everything

My purpose is to easily see what subnets i've used and which ones are available to allocate.

10.0.0.0  USA
                        10.0.64.0  Unallocted
                        10.0.128.0 New York
                                         10.0.128.0  Bronx
                                          10.0.160.0  Brooklyn
                                                             10.0.160.0  1st Street
                                                                                                             
                        10.0.192.0 Los Angelos
10.1.0.0

I've attached a small example, but it took me a really long time to do this, there MUST be an easier way....

Thanks for the help....  OH one more thing.... I don't need a subnetting calculator -- I have several.  I need to lay out my current network so that I know what subnets are available.

example.xls
0
svillardi
Asked:
svillardi
  • 11
  • 10
1 Solution
 
bromy2004Commented:
Can you supply a small list on what your original data looks like?
0
 
svillardiAuthor Commented:
There is a little data in the example.xls I provided.  While the specific subnets aren't the actual ones I am using, the subnetting IS correct.

The columns (/16 for example) denote how many bits are to be used for the network and how many are available for hosts...  (this is all networking stuff).

Subnetting is how you cut up the "pie" of network addresses...  In fact - you could think of this AS A PIZZA PIE...

Let's say for example I cut the pie into 4 slices -- Each slice becomes a network.  And for the sake of argument, I consider mushrooms my workstations, printers, what have you.  Lets say 500+ mushrooms fit on each slice (there are 4 as of now).  They are all on the same slice (network).  One mushroom can talk to another fine.... (LOL).

Well, one of the kids' friends came over and now I have to take 1 of the slices and cut it in half.  So we have 2 smaller slices (each it's own network) and 250 mushrooms (hosts, devices, etc) on each smaller slice.  These poor mushrooms can't talk to each other any more, as they are on different slices  (networks)!  They're DOOMED and they don't even know it!!!  (LOL)

For every cut you add, you divide your total of pieces, then you can divide again and again and again...

In excel the hard part is labeling each "cut" and showing how they all come back together for one full PIZZA PIE!!!  

I can copy and paste the networks from my subnet calculator.  It's only text, Excel doesn't have to calculate anything.  (unless it can automatically create the series of networks.... 10.0.0.0, 10.1.0.0, 10.2.0.0, etc)........and then space them properly--  

there's one line between 10.0.0.0 and 10.1.0.0 until I cut 10.0.0.0 in half....
so....

10.0.0.0
10.1.0.0

becomes...
10.0.0.0
        10.0.128
10.1.0.0

It's not that the numbering is hard, it's that the spacing and the outlining is hard
0
 
bromy2004Commented:
I get something set up.
I'm not sure how long it will take, but I haven't forgotten.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
svillardiAuthor Commented:
Hi, I figured out a way to do it.  The spacing was the hard part.

I finally made a simple 2nd column and created a series from 1 - the last number (16,000 something).  Then I copied and pasted that at the end and sorted by that column. Therefore, I had 2-1s, 2-2s, 2-3s, etc...  which caused a space between each line.

I will still award you points if you come up with a way to do this better, as it's quite time consuming.
0
 
bromy2004Commented:
Whew!!
That was a much bigger challenge then i thought :D

I've come up with the attached macro.

Takes about .5 seconds to run for 10.0.0.0 to 10.10,0.0
And about a minute for 10.0.0.0 to 15.255.0.0 (About 23,000 lines)
that's with a half decent computer.

There are 4 Constants in the Macro (PointxMin and Max) change these as you see fit
They are the first two Byte Min/Max

at about line 40 is L1Step=GetBits...
You can either hardcode the Steps in there or change "Sheet1" to the sheet with them in there.
(I'm refering to  /16=256, /17=128, /18=64, /19=32)

It does the grouping as well, although that takes longer than the macro in extremely high numbers (like 10.0.0.0 to 15.255.0.0, 20 Seconds for the values, 40 for the Grouping)

i think i've covered everything.

Attached is also the workbook i was using.

Option Explicit

Sub ListNetwork()
Const Point1Min = 10
Const Point1Max = 10
Const Point2Min = 0
Const Point2Max = 15

'Counters
'These values actually do the counting
Dim L0      As Integer
Dim L1      As Integer
Dim L2      As Integer
Dim L3      As Integer
Dim L4      As Integer

'Step Values (256,128,64,32)
Dim L1Step  As Integer
Dim L2Step  As Integer
Dim L3Step  As Integer
Dim L4Step  As Integer

'Row and Column Counters
Dim i       As Long
Dim j       As Integer

Dim ws As Worksheet
Dim cel As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Set ws = Sheets.Add
On Error Resume Next
Sheets("SubNetting").Delete
On Error GoTo 0
ws.Name = "SubNetting"

L1Step = GetBits(Sheets("Sheet1").Cells(1, 1))
L2Step = GetBits(Sheets("Sheet1").Cells(1, 2))
L3Step = GetBits(Sheets("Sheet1").Cells(1, 3))
L4Step = GetBits(Sheets("Sheet1").Cells(1, 4))

If L1Step = 0 Or L2Step = 0 Or L3Step = 0 Or L4Step = 0 Then: MsgBox ("Missing Bits"): GoTo Finish
'Set initial Values
j = 1
i = 1

''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'i=i+1  Sets the new row
'j=j+1  Sets the column, This value with go from 1 to 4 as needed
'Sheets(2).Cells(i, j).Value = Join(Array(10, L1, 0, 0), ".")   Sets the value on the sheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''


'Loop through point 1
'i.e. 10.0.0.0 to 11.0.0.0
For L0 = Point1Min To Point1Max

  'Loop through Point 2
  'i.e. 10.1.0.0 to 10.10.0.0
  For L1 = Point2Min To Point2Max
    ws.Cells(i, j).Value = Join(Array(L0, L1, 0, 0), ".")
    j = j + 1
    i = i + 1
    
      For L2 = 0 To 255 Step L2Step
        ws.Cells(i, j).Value = Join(Array(L0, L1, L2, 0), ".")
        j = j + 1
        i = i + 1
        
        For L3 = L2 To L2 + L2Step - L3Step Step L3Step
          ws.Cells(i, j).Value = Join(Array(L0, L1, L3, 0), ".")
          j = j + 1
          i = i + 1
          
            For L4 = L3 To L3 + L3Step - L4Step Step L4Step
              ws.Cells(i, j).Value = Join(Array(L0, L1, L4, 0), ".")
              Application.StatusBar = Join(Array(L0, L1, L4, 0), ".")
              i = i + 1
            Next L4
            
          j = j - 1
        Next L3
        
        j = j - 1
      Next L2
      
    j = j - 1
  Next L1
Next L0

For Each cel In ws.UsedRange
  If Application.WorksheetFunction.CountA(Range(cel.Offset(columnoffset:=1), Cells(cel.Row, 5))) > 0 Then
    cel.EntireRow.Group
  End If
Next cel

'Add the extra Columns for your names
Columns("D:D").Insert Shift:=xlToRight
Columns("C:C").Insert Shift:=xlToRight
Columns("B:B").Insert Shift:=xlToRight


Finish:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = ""

End Sub

Private Function GetBits(Marker As String) As Integer
Select Case UCase(Marker)
  Case "/16"
    GetBits = 256
  Case "/17"
    GetBits = 128
  Case "/18"
    GetBits = 64
  Case "/19"
    GetBits = 32
End Select
End Function

Open in new window

EE-Subnettings.xls
0
 
svillardiAuthor Commented:
Thank you very very much... I tried it quick and somethings not right, but I probably wasn't doing it correctly or the macro settings on excel aren't functioning correctly yet.  I will have to play with it more.  Here's what I need to know for the next post:

How do I change the subnets to the actual ones I use?
Your instructions are way too advanced for my knowledge of vba.  (I don't know for example, what constants are.

What I would like, if possible are some kind of fill in boxes:

Network:  (the largest subnet given to us -- like 10.0.0.0, or 10.50.0.0)
The number of bits for that subnet:  (/16 or whatever)
Press the button:  Collapsible table!

And...... what happens if I delete parts of the table which have no ranges assigned?

For example:

10.0.0.0 /16
0
 
svillardiAuthor Commented:
continued.....

For example:

10.0.0.0 /16
        ..........lots of subetting.....yada....
        10.0.1.0
        10.0.2.0
        10.0.3.0....etc..

So lets say the sales division has 10.0.1.0 and gets subnetted out.
          1st floor gets 126 hosts (10.0.1.0), ...
          2nd floor gets 126 hosts (10.0.1.128)....

Now, what if 10.0.2.0 doesn't get used?

Does that screw up the macro?

THANKS AGAIN!!!!  (I will award all these points shortly after we iron-out how I use this great tool!)
0
 
bromy2004Commented:
Before I get more details, keep in mind that i'm not normally working with networks/subnetting, so i may use incorrect lingo.

How would you determine from where the subnetting begins?
In your original example you had /16 on Point 2 (10.1.0.0 the 1)
In this sample it moved to Point 3 (10.1.2.0 the 2)

For the "Largest Subnet Given to us", whats the lowest for that?

"Now, what if 10.0.2.0 doesn't get used?"
So you would use
10.0.1.0 - Sales
10.0.2.0 -
10.0.3.0 - Management
Is that right?
I could try to have a list of IP's to skip, but i think that will complicate alot of things.
Are there alot of these skipped ranges?
0
 
bromy2004Commented:
one more,
is it likely for the IP's to go over 2 number series'
10.0.0.0 to 10.255.255.255
or
10.0.0.0 to 15.255.255.255
0
 
svillardiAuthor Commented:
Well, we are getting deep here...  into subnetting.

For MY use, I am interested in subnetting everything from 10.0.0.0 - 10.0.255.255.  This would be a class B network, which is everything in the first two octets:  10.0.0.0 with a subnet mask of 255.255.0.0

(See, this generally is a bad example, because the 10.0.0.0 subnet is a Class A subnet, not a Class B subnet)

But, for the sake of argument, it could be ANY Class B subnet:  100.100.0.0 - 100.100.255.255 or 254.254.0.0-254.254.255.255

But you know, if you've gone this far, you might as well make it for every situation, that way others could use it too.

You ask about a network not being used... That's the WHOLE point of doing this, that is to EASILY see what "piece of the pie" is still available...

Right now, I was given everything vertically:
10.0.1.0 Sales
10.0.2.0 Marketing
10.0.2.128 Shipping
10.0.3.128 Accounting

Mixed in are different hosts...

10.0.3.33-10.0.3.34 VLAN70

So it's hard to figure what I have left....  This little example shows me that 10.0.3.0 (which is not listed), is further subnetted to /30, with only 2 hosts per subnet.  This would give me a WHOLE BUNCH of subnets:

10.0.3.0 /30
10.0.3.4 /30
10.0.3.8 /30

So when you have pages full of this stuff it's harder to see....

Thanks for listening to me babble, I hope I answered your question.

Thank you again for your willingness to help.

0
 
bromy2004Commented:
OK, I'm getting a better picture for this.

Let me know if I've missed anything
You will supply the first 2 Octets (10.1.0.0) and the last 2 will always go to 255 (10.1.0.0 to 10.1.255.255)

How would you determine what Octets to subnet to what level?
In xl how would you enter this?
o1
o2
o3
o4 /30
???

If this macro Generates a sheet with the Subnetting and levels etc, would you need to look through the tree each and every time to fill it in?
i.e.
Macro Generates
10.0.1.0
             10.0.1.0
             10.0.1.128
and you would need to fill in the names?


Wouldn't it be easier to create a database sheet in excel with the IP addresses and names?


IP Start            IP End            Oct1 Name      Oct2 Name      Oct3 Name      Oct4 Name
10.0.64.0            10.0.127.0      USA                  Unallocated            
10.0.128.0      10.0.159.0      USA                  New York            Bronx      
10.0.160.0      10.0.191.0      USA                  New York            Brooklyn            1st Street

And use the macro to calculate from this?

I'm trying not to create a macro  that causes you more work in the long run.
0
 
svillardiAuthor Commented:
OK, we're getting a bit off track here...

Subnetting requires taking a look at a binary equation of 32 bits and determining how many bits are being used for  the network and how many are available for hosts.

Unfortunately, right this second I don't have the time to explain it to you.

Go to solarwinds.com and download their free subnetting calculator.

Play around with different amount of bits and masks and you will see begins and ends.

Yes, I would look through the names.

Your table is WAY off.... (VERY appreciated though -- effort wise!!)
0
 
bromy2004Commented:
Ok,
i see i'm getting over my head.

As i dont have the necessary networking/subnetting knowledge and i'm struggling to understand how it works so i'll just skip the details.

What did you need changed from my Macro submission?
0
 
bromy2004Commented:
svillardi,

Any more changes needed to the macro?
0
 
svillardiAuthor Commented:
Bromy2004,

I am going to have to get back to you.  I didn't get your previous message until I got your last one, and I am on my way to classes.  I will answer your question tonight.

Best regards!
0
 
svillardiAuthor Commented:
Hi again Bromy2004,

Well, I am not sure what to do at this point.

If you can start your macro at the 16 bit level and finish at the 31 bit level that would be enough.  I would need a place to put my subnet in, where the macro would ask me for input.  I would be able to type 10.10.0.0 or 56.67.0.0 or 1.1.0.0 ---- and end at 10.10.255.254, or 56.67.255.254, etc.

Is that any help?

It would be GREAT if you could ask for the starting network and the number of bits it represents and have Excel do the subnetting with the outline.

Asking for too much?  If this is too difficult or involved, I am willing to simply award the points for effort and give up.....

OR....

How about this:  If I give you columns in a worksheet, could you somehow automatically convert the columns into an outline???  That would work too, I think.
0
 
bromy2004Commented:
I can set the start and finish addresses, but when bits get involved is when i start struggling.

The spreadsheet would be helpful,
i can then do a slightly different macro.
0
 
svillardiAuthor Commented:
Bromy2004,

I can do that, but it won't be spaced properly to do the outlining (which is the whole problem).

I think the easiest way is for you see the subnetting itself is to install a subnetting calculator.

Are you willing to do that?
0
 
bromy2004Commented:
Ok.
What program should i get from Solar Winds.
Please make sure it can handle a trial or a freeware alternative.
as well as steps on how to get the output.
0
 
svillardiAuthor Commented:
Google Subnet Calculator.

It's freeware.  Just put in some yahoo address or something.  They do not actually send a confirmation via email, but they will try to contact you to buy stuff.  I just used an old yahoo address and got right in.

When you install it, pick an IP address (like my samples) and choose the number of bits.  Start with 16, which will coorelate with 255.255.0.0 (8 bits in the first octet, 8 in the second, 0 in the third and forth) subnet mask.  You can even use the IPs most use:  192.168.0.0.

Then click on calculate subnets....  you will see how many subnets you can create and the amount of hosts per subnet.  When you start at 16 bits, you will see a very small amount of networks, and MANY MANY hosts per network.  As you increase the bits (17, 18, etc) you will see that the amount of subnets increase and the amount of hosts per subnet decrease.  Let me know what you find.

THANK YOU!!

We use 16 bits - 31 bits.
0
 
svillardiAuthor Commented:
Thanks for the help, although I have decided that this is too involved to continue.  Maybe some other time.  I do appreciate the efforts and quick turnaround.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now