Beyond Excel: VBA and Database Manipulation

Aug 30 2013   7:17AM GMT

Want a Free Gantt Chart App?



Posted by: Craig Hatmaker
Tags:
development
excel
ListObjects
Microsoft Excel
Tables
tutorial
vba

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.


DIY
You can build BabyGantt.xls yourself or download it 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, Moma Gantt is on the horizon.

Moma Gantt is better.  Moma Gantt is in the testing phase now.  It 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.

Papa Gantt is planned.  It will provide 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,

Moma Gantt Testers Wanted!
If you would like to be part of the MomaGantt.xls test team please email me at Craig_Hatmaker at Yahoo.com

 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: