Tips on using this forum..

(1) Explain your problem, don't simply post "This isn't working". What were you doing when you faced the problem? What have you tried to resolve - did you look for a solution using "Search" ? Has it happened just once or several times?

(2) It's also good to get feedback when a solution is found, return to the original post to explain how it was resolved so that more people can also use the results.

How to create an EXCEL GANTT CHART using VBA

1 reply [Last post]
Mohamed Ghafir
User offline. Last seen 4 days 15 hours ago. Offline
Joined: 29 Sep 2017
Posts: 5
Groups: None

I'm a planner in the UK, and been working as a planner for just over 2 years in few different rail projects.

and one of the main challenges is that most of the engineers were NOT reading the P6 PDF programmes, 
even if they did, they really didn't pay attention to the details.

because everytime I talk with the engineers to get updates on their activity, 
them sound like they haven't read the programmes and don't know the dates that they should be aiming for,

and eventually one of them told me it's because the programme is too big,
there's too much text. 
too many columns.

the Ghantt Chart looks complicated because the activities are not sorted in order, 
even if we sorted by finish date the problem wasn't really solved
because there's a feedback loop between multiple disciplines, 
so they will have to go up and down from page to page to know what's the predecessor/successor of a certain activity. 

Doing filters in P6 can diffinately help, but it doesn't solve the problem completely, 
it still requires searching back and forth to follow a sequence of activities.

So after a discussion I found out they don't care to read the programme, 
They ONLY want to know what are the activities they need to focus on in the next 1,2,3 or 4 weeks.

That's IT!

I knew excel may have the solution, because you can easily
highlight a set of columns and look at the activities that lie in the shaded area.

so I searched google for Excel ghannt charts, and I found a bunch of templates,

I used many of them, but non offered anything better than what we have in P6
This is when I discovered Excel VBA,

I started dabbling with it and saw the huge power in this software,
basically, what you can do is limitless! HOWEVER.

It required to either hire a Developer to create a VBA code and template for you,

or learn how to code and do it myself.
I choose to do it myself and took multiple courses on the subject just to make this idea work.

After alot of trial and error and help from some really talented developers, 
I finally got the software that has all the features I want and MORE.

**With a click of a button, it can do all of the following

1) as you can see above to create kind of a visual horizontal sequence of activities on excel (instead of 1 activity on each row)
2) It Automatically COLORS activities based on their status

Completed = Blue
Green = Not Started & Not Critical
Orange = In-progress & Not Critical
Red = Critical activity (Not Started or In-progress)
Pink = Near Critical (you can define the near critical)

3) It positions the activity start and finish date (it's very accurate)
4) You can change the dates columns to be daily or weekly as show below

  • 1 day per column,
  • or 1 week per column (see below)

 

Since using this, all engineers have been looking and engaging with the programme at every meeting (Finally)!

I'm planning to give this for free to the planning community,

PS: there are extra features (really good & time-saving extra features) that I will talk about later, if I see some interest.

If this is something you are interested in, please let me know and I will give you the free version that I talked about above and I will be very happy to know I made a contribution.

Otherwise I'll be the only user of this tool

Replies

Zoltan Palffy
User offline. Last seen 17 hours 1 min ago. Offline
Joined: 13 Jul 2009
Posts: 2573
Groups: None

Mohamed

Since you are planning to give this for free to the planning community

I would like to try this program. Can you please send this to me along with any instructions.

If you want I can help to promote this program.

I have sent you a message here on Planning Planet with my eamil address