Free Org Chart Add In
Post date: Feb 1, 2015 3:52:52 AM
End UsersCreate Organization Charts or any Hierarchical Chart from XL data with the click of a mouse.
Developers
Learn about recursion and using Types as function return values.
Why I Did This
A LinkedIn forum poster asked for an XL macro to create an org chart from his employee database. My first instinct was to Google Excel Org Chart VBA and send the links. I was shocked to discover this had not been done to death already. In fact, I found more tools for Visio or third party programs than any mentions for XL. The few XL entries I did find require manually typing employee information.
Really?
Almost every modern company has an HR system and those HR systems have an employee master that lists each employee and who they report to. Even MS Access’ demo database, Northwind, has an employee master exactly as I described. Any company of moderate size needs an org chart. So why hasn’t this been done before especially when it is not that tough to do.
A Recurring Theme
If you are not into programming and only want use this add in, stop. Don’t read any more of this section. On the other hand, if you are a developer, you may find this interesting.
I guess this hasn’t been done because the easiest way I know to crack this problem is through recursion and many VBA developers don’t know what that is, or view recursion as too risky.
Recursion happens when a routine calls itself. This can be dangerous because, without care,recursive routines fall into infinite loops consuming memory and crashing systems. Think of recursion as fire. Both are dangerous and both are useful. Just handle each with care.
The Org Chart problem cries out for recursion. Its basic flow is:
1. Select an employee.
2. Call routine to AddNode(Employee)
3. Which puts employee on chart
4. If employee has no reports, return.
5. Otherwise, for each report call routine AddNode(Report).
AddNode calls itself to traverse down the command chain. When it can’t go down any further, it returns back up through the chain of AddNode() instances that called it.
Summary
Try the demo. Use it to learn how to apply it to your organization’s data, and, perhaps, learn about recursion.
