Haphazardly Posted Excel Information and Other Stuff

Work Breakdown Structure Numbering in Excel

I’ve never heard of Work Breakdown Structure (WBS) before, but I’ve definitely seen it. It’s that type of numbering I see in lawerly type documents. It looks like this:

Jeremy has seen it before and wrote an Excel macro to number an Excel list based on indentation.

It seemed to work well for me. Well, I didn’t read the instructions first which put me in an infinite loop. But once I broke out of that, read the instructions, and reformatted my data, it worked. In that regard, I’d add a If r > Rows.Count Then Exit Do in there just for good measure. Of course, I would use class modules to do this, but then Mike Alexander would just make fun of me for it. :)

Posted in Uncategorized

Post navigation

21 thoughts on “Work Breakdown Structure Numbering in Excel”

I had a go at something similar: a generic tree-walker that could do different actions. The particular case was creating a directory tree corresponding to the indented list. However, you could easily generate nested numbering. I used an instance of a linked List class to hold the path.

Nested subsection numbers can be done with formulas. It helps to define an array referring to a sequence of integers from 1 to something reasonable, in this case 32 should be sufficient. So something like the name nums referring to the constant array {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32}.

If the topmost entry were in cell B2 and had no indentation, enter 1 in cell A2. For subsequent entries in column B, the column A formulas would be like so (here the formula for cell A3).

I wrote something similar for automatically indenting rows; that is, using the Outline feature of Excel. I work with a lot of Bill of Materials files, and many of these are multi-level explosions. Management likes indented files so that they can collapse and expand rows at will. I wrote this macro to take advantage of a “Level” column and a given data table.

This code is extremely fast, even with 30,000+ rows of data. I haven’t updated this code in a while, it hasn’t been rigorously tested, and it only works on the ActiveSheet, but it could be easily modified to meet people’s needs (I will eventually do that myself). Hopefully, it will do someone some good.

Dim bGroupFlag AsBoolean‘ a Boolean flag signaling that a gathering has been detected and will be groupedDim lCount AsLong‘ temp integer variable for counting and loopingDim iCurLevel AsInteger‘ Current level; used to convert text to numberDim iStartLevel AsInteger‘ Start level; used to convert text to numberDim iMaxLevel AsInteger‘ the maximum value in the Level columnDim lCurrentRow AsLong‘ the row number of the current rowDim lGroupLevelThreshold AsLong‘ the minimum level a row must have in order to be grouped (Excel can only group 8 levels deep); not hardcoded to 8 so that one could group from 15 – 20 for exampleDim lNumOfRows AsLong‘ the row number of the last row in the active sheetDim rngLevelInfo As Range ‘ the range of level informationDim rng As Range ‘ temp range variable

ErrorHandler:‘ An error is reached if level information cannot be interpreted. In such case,‘ the macro removes outlining and notifies the user. MsgBox (“An unexpected error occured. It is likely that your “ & _“level column contained text rather than only numbers. “ & _“The data will be restored to its original form.”)

Just a point of clarification… A WBS is not the numbering system itself. WBS is a project management system designed to capture 100% of the project scope by defining milestones as nouns(deliverables) with specific costs, not verbs(tasks/actions). And then, there is a terribly long list of WBS do’s and dont’s. The genesis of the system was in the 50’s and it is now required for virtually all military projects.

Thank you very much for sharing your WBS outlining code (I’m sure this is especially appreciated by those of us who are relatively new to VBA)! I know this is an old thread and perhaps you wont know about comments made today, but I thought it worth a try.

I too had problems pasting this into a standard code module. It seems that there are extra characters in the paste above (i.e. “>” in the first Do Loop “Do Until (lStartRow >= lNumOfRows)” & “&amp” in the MsgBox calls). I don’t see “gt” or “amp” as declared variables, so I am assuming these are simply pasting errors. And when I remove these characters in the editor, the code will compile. However, as documented in your developers comments, you moved function “ResetLastCell” to make it a separate function called by function RemoveOutlining (shown at the bottom of the pasted code above). Would you be so kind as to please provide the function “ResetLastCell” too. Of course, this would be greatly appreciated.

If my assumption about removing characters “>” & “amp” above was incorrect, it would be nice if you could post (or email me, if you prefer) an example workbook with this code working. Having a working WBS outline in Excel would make my work life a lot easier and would give me another useful VBA example to study and learn from.

Sergio: I switched to a better code rendering plugin for comments, but Matthew’s comments was before the switch. I just cleaned up the escape characters in his comment so you should be able to copy and paste it successfully now. Sorry for the inconvenience.

Since I made that post last year, I’ve modified the code to make it faster and more robust. I’ve created an example workbook that contains all the outlining VBA code. I also included buttons and comments to help you debug the code and help learn how to use it and how it works. If anyone would like a copy, please email me at pflugs30{@] gmail (dot] com.

I use this code nearly 10 times a day to outline Bill of Materials data, and several of these workbooks contain thousands of rows. This outlining code is fast, robust, and well tested in both Excel 2003 and 2007. I hope it helps you, too. Have a great day!

Update: I’ve had a lot of interest in this code, and I’m glad it’s helping folks out. I would like to clarify that the code doesn’t look at the WBS code to do the outlining; rather, it requires a numeric level value to group on. That is, “1? has a level of 0, but “1.2.1.4? has a level of 4. You can calculate the Level from a WBS that uses a period (.) as its delimiter by using the following Excel formula:

The hyperlinks is a neat idea. If you’re going to do that, I would not use a FIND function, though. That has the tendency to be very slow, especially for large datasets. I would instead give each row a unique integer ID value and then create a “ParentID” column to store the ID value of the row’s parent. Then, you can replace the FIND function with an index function like INDEX or OFFSET which will calculate much faster and scale quite nicely.

I am a newbie to building functions and macros for Excel, and for that matter VB. Could you please give me an example of the parameter values for (ByVal lLevelCol As Integer, ByVal lStartRow As Long)? I am a bit confused. For the example above, would lLvelCol be 1 and lStartRow be 2? When I attempt to run this I am getting some really strange values.

Here is a good work breakdown structure example, not in excel but in a visual representation. The key is to breakdown the work/project into manageable activities and tasks. http://ygraph.com/chart/1430