General off-topic discussions.
User avatar
dieselboy
Post Whore
Posts:
2731
Joined:
Tue Aug 05, 2008 6:36 am
Certs:
CCNP, CCNA Voice, SMB Select, Linux+

MS Excel programming

Thu May 03, 2012 8:38 pm

I've not really had much experience with excel other than basic school-level stuff. I dropped IT as a subject back in school in favour of Geography and Electronics.

These past few weeks I've been feeling the benefits of Excel and it's seemingly infinite uses for things like IP spreadsheets, IP allocations. For example, I've made a sheet where you input an IP subnet and it will fill out a table and give you space to enter your allocations. It only works for /24 subnets just now because I'm a noob. I'm working towards a spreadsheet that you can enter an IP address and mask and it will create a variable length table. The front page is all hyperlinks and states the subnets in use, VLANs, their names and descriptions. Separated by location. You can chuck pretty much anything in there. Latest addition is GRE tunnel configruations.

Ultimately, I would like to get to the point where you could enter the allocated subnet on the front page (or rather pick from the un-allocated), the sheet will then create a new book, completed with the IP addressing and give you blanks to input your stuff.
Will be some time though.

Anyone have any fancy sheets for documenting things?

Another sheet I designed last week is a QoS designer.
You basically give your codec, number of simultaneous calls, contingency calls per hour, any video conferencing stuff. Also taken into account is control traffic, IP faxes etc. This then works out the Priority Queue and a second queue for video.
This sheet has been pretty useful. It's allowed me to "play" with numbers to suit.
Meanwhile, back in the city...

User avatar
Vito_Corleone
Moderator
Posts:
9850
Joined:
Mon Apr 07, 2008 10:38 am
Certs:
CCNP RS, CCNP DC, CCDP, CCIP

Re: MS Excel programming

Thu May 03, 2012 8:41 pm

Cool. I haven't gone that deep. I mainly use mine to generate configs. Sometimes I hand them out to customers too. Saves me work in the end.
http://blog.alwaysthenetwork.com

User avatar
dieselboy
Post Whore
Posts:
2731
Joined:
Tue Aug 05, 2008 6:36 am
Certs:
CCNP, CCNA Voice, SMB Select, Linux+

Re: MS Excel programming

Thu May 03, 2012 8:58 pm

Vito_Corleone wrote:Cool. I haven't gone that deep. I mainly use mine to generate configs. Sometimes I hand them out to customers too. Saves me work in the end.


Interesting :) I hadn't thought of that
Meanwhile, back in the city...

User avatar
eaadams
Post Whore
Posts:
2611
Joined:
Fri Mar 11, 2005 10:26 pm

Re: MS Excel programming

Fri May 04, 2012 3:01 am

The subnetting stuff can be done with cell formulae, but for the serious stuff are you writing VBA macros?

Aubrey
The illiterate of the 21st century will not be those who cannot read and write, but those who cannot learn, unlearn, and relearn. Alvin Toffler, "Future Shock" 1970

User avatar
expaddy
Post Whore
Posts:
1598
Joined:
Wed Jun 17, 2009 11:28 am
Certs:
BSc computer network administration, CCNP, MCSE

Re: MS Excel programming

Fri May 04, 2012 4:52 am

What about a look at those?
Good Luck,

David

User avatar
dieselboy
Post Whore
Posts:
2731
Joined:
Tue Aug 05, 2008 6:36 am
Certs:
CCNP, CCNA Voice, SMB Select, Linux+

Re: MS Excel programming

Fri May 04, 2012 8:57 am

eaadams wrote:The subnetting stuff can be done with cell formulae, but for the serious stuff are you writing VBA macros?

Aubrey


You're the second person to recommend VBA today. Over beers I told a colleague what I was doing for a project I'm working on and he said I should be using VBA. Without googling I think VBA is Visual Basic? I havent played with that it about 10 years when I was fresh in college. But, it would probably be useful. As with most of the things I do, the simpler and cleaner the better.
Meanwhile, back in the city...

User avatar
eaadams
Post Whore
Posts:
2611
Joined:
Fri Mar 11, 2005 10:26 pm

Re: MS Excel programming

Fri May 04, 2012 9:49 am

Almost; VBA = Visual Basic for Applications, i.e., it is the macro language for all the MS Office apps. That said it has many of the features/objects of real VB, the main difference is that you can't build standalone apps with it - the VBA modules have to be at least associated with, if not embedded in, an Office app file, e.g., an Excel worksheet. But is very powerful once you move beyond thinking about just macros.

We use it to teach fundamentals of OO programming. You don't need a specific development environment or the APIs, etc, etc - you just need Excel with macros and the VBA editor enabled. What is cool is that on the code understanding side you can record a macro, view the generated VBA code and then edit it to suit. For example, we get students to record their setting up of a 3 column table of data, sorting it, and generating a chart from it. They then examine the code, replace the keyboard entered constant values with variables, use Inputbox object to assign values to those variables, and so on,. They end up with a generalised macro which they use to enter values, sort the data, set the chart parameters, etc, without the user entering a value into a cell and using the menu. A VBA macro can also be set to be invoked as soon as the file is opened so the user is presented with input, dialog and message boxes and never see the worksheet.

Aubrey
The illiterate of the 21st century will not be those who cannot read and write, but those who cannot learn, unlearn, and relearn. Alvin Toffler, "Future Shock" 1970

User avatar
dieselboy
Post Whore
Posts:
2731
Joined:
Tue Aug 05, 2008 6:36 am
Certs:
CCNP, CCNA Voice, SMB Select, Linux+

Re: MS Excel programming

Sun May 06, 2012 8:56 pm

Interesting. Is this recording function part of Excel or is it an add-on of some kind?
Meanwhile, back in the city...

haccduder
Ultimate Member
Posts:
592
Joined:
Sat Oct 09, 2010 2:13 pm
Certs:
CCNA

Re: MS Excel programming

Sun May 06, 2012 10:09 pm

dieselboy wrote:
Ultimately, I would like to get to the point where you could enter the allocated subnet on the front page (or rather pick from the un-allocated), the sheet will then create a new book, completed with the IP addressing and give you blanks to input your stuff.
Will be some time though.



Maybe we're a bit spoiled in that we have a couple web developers working for us, but they've written us a web page in php that does all this. I think if you have more than a couple network guys changing the IP maps, it's the only sane way to do this without dropping 10K on a solar winds toolkit.

User avatar
dieselboy
Post Whore
Posts:
2731
Joined:
Tue Aug 05, 2008 6:36 am
Certs:
CCNP, CCNA Voice, SMB Select, Linux+

Re: MS Excel programming

Sun May 06, 2012 10:20 pm

A PHP web page seems a bit oo-er.
None of the companies I have worked for want to spend 10k.
Meanwhile, back in the city...

User avatar
eaadams
Post Whore
Posts:
2611
Joined:
Fri Mar 11, 2005 10:26 pm

Re: MS Excel programming

Mon May 07, 2012 1:55 am

dieselboy wrote:Interesting. Is this recording function part of Excel or is it an add-on of some kind?
Nope - it comes standard with Excel, since Office 97 iirc. Search Help for "record a macro".
File > Options > Customize Ribbon > click Developer
Then View ribbon > Macros to Records macros. The stop button is down by the "ready" prompt in the button left of the worksheet.
When finished go to View ribbon > Marcos > View macros and then "Edit"which opens up the VBA environment.

Aubrey
The illiterate of the 21st century will not be those who cannot read and write, but those who cannot learn, unlearn, and relearn. Alvin Toffler, "Future Shock" 1970

ph01n11x
New Member
Posts:
20
Joined:
Wed May 02, 2012 9:37 pm
Certs:
CCNA

Re: MS Excel programming

Sat May 12, 2012 4:58 am

haccduder wrote:Maybe we're a bit spoiled in that we have a couple web developers working for us, but they've written us a web page in php that does all this. I think if you have more than a couple network guys changing the IP maps, it's the only sane way to do this without dropping 10K on a solar winds toolkit.



I haven't been working with Solarwinds tools that much.

Were you referring to those tools?

IP Address Tracker
http://www.solarwinds.com/products/free ... s-tracker/

Subnet Calculator
http://www.solarwinds.com/products/free ... lator.aspx

IP Address Manager
http://www.solarwinds.com/products/orio ... s_manager/

The first and second are free to use

ph01n11x
New Member
Posts:
20
Joined:
Wed May 02, 2012 9:37 pm
Certs:
CCNA

Re: MS Excel programming

Sat May 12, 2012 6:26 am

Have you considered using / contributing to an exisiting open source project? It can potentially be faster / more interesting than doing it on your own.

I've never used the below tool myself, however, please have a look.
http://sourceforge.net/projects/iptrack/

It's not written in VBA though :) Personally, I see it as a plus, VB supplied with MS Office isn't the best programming tool, I think.

'

Return to Forum Lobby

Who is online

Users browsing this forum: No registered users and 12 guests