Beyond Excel: VBA and Database Manipulation

Aug 30 2013   7:17AM GMT

Want a Free Gantt Chart App?

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Introducing a new approach to Gantt Charts in XL – BabyGantt.xls

BabyGantt.xls
There are quite a few XL based Gantt Charts out there.  They fall into 2 main categories: Bar Charts and Conditional Formatting.

Bar Charts
This type creates a stacked bar chart with the first series being the duration from project start to task start.  This series’ fill color is set to “No Fill” so it appears that the second series – the actual task – is floating by itself.  It is a clever and effective means to small Gantts – about 30 or less tasks.  It can leverage XL2007 and later’s improved aesthetics.

Conditional Formatting
This type uses spreadsheet cells and conditional formatting to make colored cells into Gantt Chart bars.  This method can accommodate many more tasks but also creates a lot of formulas and calculations.

Introducing BabyGantt.xls
BXL’s Baby Gantt uses shapes and VBA.  It has the advantage of Conditional Formatting’s “many tasks” without the formula overhead. It also shares the Bar Chart’s improved graphics capabilities.  But best of all, there’s no setup – at least – not for the chart.

All Gantt Charts require tasks and dates. But that’s all Baby Gantt requires us to enter.  We don’t need to add and configure charts or enter and autofill formulas across massive ranges.  The VBA in Baby Gantt reads our task entries and generates the graphics on the fly. And because it is Excel, we can use formulas to calculate task start and end dates.

BabyGantt.xls can be downloaded from my DropBox.  Here is a link to the instructions:
https://dl.dropboxusercontent.com/u/13737137/Projects/Gantt/BabyGantt.pdf

The instructions include:

User Instructions – Step by Step guides on how to enter your project with examples
Download Instructions – Links to a fully functioning, totally free, unprotected BabyGantt.xls
Technical Documentation – All of the code with explanations and all data validation entries with screenshots.

Baby Gantt is a good solution for small projects but its real purpose is to teach VBA developers how to manipulate shapes with VBA.  So all of the code is exposed and trimmed to be as easy to decipher as possible.  Unfortunately, the stripped down code also makes it a bit fragile.  For those ready for a more robust solution, see: Moma Gantt.
Mama Gantt is better.  Moma Gantt includes all of Baby Gantt’s features but in a more “Production Quality” state.  It leverages VBA’s classes and adds controls to prevent users from inadvertently doing things they shouldn’t.  Like Baby Gantt, it is intended as a teaching tool for VBA developers looking to take their skills to the next level with VBA’s classes.
https://dl.dropboxusercontent.com/u/13737137/Projects/Gantt/MamaGantt.pdf
Papa Gantt is best.  It provides more project management tools such as work breakdown structures, assignments, durations, task types, and more.  It is intended as a “poor man’s” alternative to full featured project management programs.
https://dl.dropboxusercontent.com/u/13737137/Projects/Gantt/PapaGantt.pdf

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: