We help IT Professionals succeed at work.

Data Model with Rules

Bright01
Bright01 asked
on
Medium Priority
334 Views
Last Modified: 2012-05-12
The EE Professionals have teamed to develop a really nifty and easy to use project mgmt. tool.  In order for it to work correctly we need a specific "rule" to apply. The most important rule is;

No two tasks may overlap with regard to "dates", when considered "in the critical path" or coded "C" in Column B.  What would be helpful is if a message box showed up with "Please pick a different date due to a conflict in the Critical Path".

That's it!

Thank you in advance,

B.   AutoDateFormatv9.xlsm
Comment
Watch Question

B,
As attached. There are various code additions, as below:
Functions:
1) dateRangeNotOverlaps - this is a generic function and can be used elsewhere and simply requires the old and new start and end dates. It may seem odd to check whether dates don't overlap but this is because there are 4 possible ways for a date range to overlap, but only 2 for them NOT to overlap, so requires fewer checks in the function;
2) overlappingCriticalTasks - this is specific to your requirements and simply creates a range to check and runs through all critical flagged task date ranges using the above function.
Routines:
1) criticalPathConflict - this is your error message to the user, and can be amended as you see fit;
2) Worksheet_Change - this has additional lines to call the various functions and routine above once a date is added to column 4. It will check both for dates entered for a new Critical Path and also if the user changes from "M" or "P" to "C" for a previously entered date range.
Regards,
 AutoDateFormatv9.xlsm
CERTIFIED EXPERT

Commented:
hi SubmersiveArmadillo,

I like it. Your approach is much neater than what I had considered a day or so ago when I first saw B's question, but I was distracted & never got it coded to post in the thread.

I know it isn't very necessary with the size of the sample dataset, but would it be valid to include an "Exit for" immediately after "overlappingCriticalTasks = True" in the "Function overlappingCriticalTasks"?

Rob
Rob,
Yes, that's a nice, and obvious, addition. I suspect that the data will never get to the stage where it matters as, in previous discussions we've had it seems the project sizes are unlikely to be that huge, but for just being generally better code I agree with you.
In the same way the code really needs to be tidied, optimised and ideally have a central error handler thrown on but I've tried not to over-engineer.
Regards
Philip

Author

Commented:
Excellent!  this worksheet has turned out to be a great little and simple way to "on the fly" create a project plan.  I also agree that if it gets complex, there are other tools such as MS Project....however, for use within a workbook, this is very good and will work well.

Much thanks to you Philip for your hard work here.

Best regards,

B.
Glad to have helped. If you have other snippets of code required, or general help with MS Office just let me know. I'm often hanging about on EE, or I'll be lurking on other Forums, else you can get hold of me through my own website (see my profile for a link).
Regards,
Philip
B,
I know this is now closed but I've been thinking about one of your original questions regards this thread - the idea of being able to collapse/expand down to a per week level, which I said couldn't be done by grouping and sub-grouping.
This original comment still stands but I do have a potential solution to this. I just need to have a think about how some of the other code would need amending to prevent a conflict but the general idea goes like this:
1) create a dynamic list of unique week numbers and include a top-level label of "All";
2) create an ActiveX Combo-box populated by this list (excluding blanks);
3) assign a macro to this list so that selection of a week number will auto-hide all but the selected week's entries;
4) update the initial calendar creation routine to update the Combo-box ListFillRange once the project calendar is created;
5) I could extend this to the months as well as the weeks, rather than using the current Grouping function, but that's entirely optional; the code is near identical anyway so there's no real effort to doing it this way once the first set is in place.

The two questions I have for you are:
1) Are you still interested in this feature?
2) Can you wait for a few days to get it?

Basically I'll be away for a few days from Wednesday (with my computer, obviously!) and have several hours to fill while sitting on trains going there and back, so I can do this for you sometime during the journey and post it up when I get back, if this is something you'd like to have.
Let me know.
Philip

Author

Commented:
Philip,

Wow you're good.  I would be interested in this capability if it wouldn't be too much trouble.  I have no trouble waiting for that capability.  Let me know what I can do on my end to help out -- testing is probably good for me since I'm far more a user then a coder (regretably).  I'm a closet Excel techie but don't come close to what I've seen from the professionals at EE.  Love this community.

Thanks for asking.

B.
Not a problem (and we all started out as beginners, it's just that some of us started when the world was younger).
I'll play around and get something back to you by the weekend, if not earlier.
B,
The two sections of code for month/week selection have been written to replace the original piece of code where we looked at grouping by month but then were unable to group below that due to restrictions inherent to Excel. Instead it makes use of named ranges to hold values for the selected month or week and creates a temp range, and then hides/unhides as appropriate.

The drop-down boxes make use of some rather exotic formulae and dynamic ranges to ensure auto-population of these.

Obviously these changes have necessitated that we make changes in the main piece of code that constructed the project calendar, and this seemed an opportune time to break this out into a discrete entry point routine which calls the lower functions as required. There are some further changes that could be made to these to make better use of the error handling - at the moment I have only added individual error handling, as opposed to fitting fully-fledged central error handler, but frankly that gets way outside the scope of the original questions you posed on EE. I guess what I ended up doing was creating it based on your original request and writing the code in such a way to show how a custom add-in/application for Excel should be set up.

Since I had time to kill I have added some other fun bits of code, conditional formatting and useful tools so this may be more than you were asking for...but, as I said, I had time to kill. These new items include:

   * dynamic text help box - this will pop-up to provide information when a main header for either rows or columns is selected;
   * dynamic highlighter - this is called by using the up/down cursors and highlights the current row (by selection);
   * a red outline for the current date column;
   * dynamic drop-down lists* for:
      * task name;
      * resource (team or team member).
   * code to clear all tasks, add new rows with the correct conditional formatting, and also formula to auto-populate Stage Codes and Duration.

If figured since this is more extreme than the usual sort of response to questions posed on EE I may at some point have to turn it into a full-fledged article but that's a different story entirely.

Finally I removed the code for the automatic pop-up calendar on specified cells. This can now be called either by double-clicking in a cell or by clicking on the calendar icon when the cell in which you want a date is selected.

Obviously feel free to test it and feed back as appropriate.

* a fuller explanation of these is that they use data validation in a dynamic list to provide a drop-down for all existing values in the relevant field but also allow the addition of new values by simply typing into the cell.

Regards,
Philip

 smallProjectPlanner.xlsm
CERTIFIED EXPERT

Commented:
hi Philip,

I've only had a quick play & scan of the code in this latest version and it looks great :-)

Since I am a curious chap, I have a couple of questions:
- Should the "'Application.enableEvents = False" line be commented out in the "Private Sub Worksheet_SelectionChange(ByVal Target As Range)" of the "wksProject" sheet's code?
 - Why have you included the older syntax of "Sub Auto_Close()" procedure instead of including the single line of code in the "Private Sub Workbook_BeforeClose(Cancel As Boolean)" procedure?
(I went looking and found some possible reasons in http://www.mrexcel.com/forum/showpost.php?p=321016&postcount=13 & http://www.officekb.com/Uwe/Forum.aspx/excel-prog/48922/Running-a-macro-Upon-Closing )

Rob
Rob,
Mostly showing my age for the second part, and for the first because I was using it to test and meant to take it out entirely (as far as I remember).
I've got some client work to do this morning but will have a double-check later.
If you have anything you want to add, or enhance, please feel free.
Philip

Author

Commented:
SA.

Wow.  Very nice!  I only had a few min. this AM to run through this.... when you press the X button more then once it erases other parts of the model.  You may want to check that out.  I'll test it more later.

Thank you,

B.
And that's why I always use a tester! I make assumptions like "nobody would try to clear an already empty sheet..."

updated:  smallProjectPlanner.xlsm
CERTIFIED EXPERT

Commented:
hi Philip,

No, I don't think I can really enhance or add to the solution you have already offered. The following comments are mostly matters of my personal preference & I'm only listing them for your (possible?) interest. Your code works as it is so there shouldn't be any problems in ignoring them completely... ;-)

Anyway, here they are...

- I'm a fan of With statements so I would probably use a few more to group actions on specific objects. I haven't done much speed testing, but some people say that the use of With statements can be more efficient because the computer doesn't have to resolve the full object qualification for every line of code/time it is referred to. My main reason for using them is that I consider the code neater as there are less characters on the page & it helps to point out possible areas where code can be refactored.

- I've read that in computers these days "Integers" are internally converted to "Longs" before use, so I'd change as many uses of "Dim... as Integer" to "Dim... as Long". On a similar vein, I'd change both ...Row & ...Column variables to be Long because with the number of rows an Integer variable could generate an overflow error and there is no need for using a Double because a number of rows will never contain a decimal portion.

- re "'Application.enableEvents = False":
It looks like it could be quite useful to prevent recursive looping of the event macro, but if it's not needed, the error handling in that Sub can be re-written too.

- Just curious, what are the "Private Const msMODULE As String = ..." statements for, is it a precursor to centralised error handling?

- Actually, some (all?) of the error handling sections state:
ErrorExit:
    On Error Resume Next
    Exit Sub

Open in new window

I don't think the use of "On Error Resume Next" is necessary at this point in the code because once the code reaches this point, there are no further chances for errors to occur as it is immediately followed by "Exit Sub". When a sub is exited the error handling treatment returns to whatever was used in the previous/parent sub.
(when I read it initially, I thought that it may have been meant to be "on error goto 0" but on reflection I have changed my mind.)

- I prefer vbnullstring over "" since reading the points on this page (& others): http://www.aivosto.com/vbtips/stringopt.html#empty

- Can this code...
Sub DisableDelete()
    Cells(ActiveCell.Row, ActiveCell.Column).Select
    Application.OnKey "{DEL}"
End Sub

Open in new window

be re-written in the below form?
Sub DisableDelete()
ActiveCell.Select
    Application.OnKey "{DEL}"
End Sub

Open in new window



- I'd change the "magic numbers" that identify the number of rows from "65535" to something like "activesheet.rows.count - 1" (or even to a Constant which is declared once) & depending on what it is being used for, I may also change the "256" to "activesheet.columns.count". This makes the code more flexible for when it/if is saved as a different file format.

Rightio, that's enough idle chatter from me!

Rob
Rob,
As below:

With statements:
I’m not a fan, simply because I’m a miserable old curmudgeon and thus not a fan of anything! That said the use of With statements is more efficient and when iterating over large lists the speed difference is measurable. I did some tests on this just so I could have some numbers to throw out. Over 10,000 iterations the difference was averaging around 100 milliseconds a simple set, for the 50 or so I envisaged for this project we were down to a millisecond or so either way. So would I change it here? No, because I don’t think it’s even worth the tiny amount of time it would take.
Neater? Yes and no, and I think this sits comfortably alongside the argument as regards single line If statements, some people like them, some don’t. I tend to use With statements when they refer to long sequences, I don’t like to use them otherwise.
For instance, I could change examples like:
Private Sub Workbook_Open()
    bEvents = Application.enableEvents
    bAlerts = Application.DisplayAlerts
    CalcMode = Application.Calculation
    bScreen = Application.ScreenUpdating
    Application.enableEvents = True
End Sub

Open in new window

To:
Private Sub Workbook_Open()
    With Application
        bEvents = .enableEvents
        bAlerts = .DisplayAlerts
        CalcMode = .Calculation
        bScreen = .ScreenUpdating
        .enableEvents = True
    End With
End Sub

Open in new window

Basically this comes back to using my existing code library. Until I update segments of that this won’t change because I simply grab what I need and drop it in, only adjusting where I see a need to do so. Maybe one day I’ll update my code library.

Longs vs Integers:
Actually this is a very good point, and you're right that processors do internal conversions to Long from Integer, and back again. Excel, on the other hand, generally uses Double as it’s own internal and converts between Integer <> Double or Long <> Double. But, since we're not interested in floating point arithmetic, which has it’s own inherent problems, it’s best to stick with Long. In all honesty, since I also designed around this application only having around 100 lines of project tasks we could quite happily use Byte which only runs from 0 to 255 but who does that nowadays?
The truth behind a lot of the Integer use is that these are (mostly) routines I’ve been using for several years and haven’t got around to updating because I’ve simply not used them in situations where this would cause an overflow error. I’ll stick it on my list and stop being lazy ;-).

re "'Application.enableEvents = False":
Um, I commented it while testing and never got back to uncommented. My bad.

"Private Const msMODULE As String = ..." statements for, is it a precursor to centralised error handling?
Spot on. I was going to add central error handling and then decided not to at the time of putting it together. I’ve left it in so that I can come back to it another day and put this in. With regards to the code:
ErrorExit:
    On Error Resume Next
Exit Sub

Open in new window

This is actually an amended place holder, again for me to come back to and for the same reason. The standard form would be along the lines of:
ErrorExit:
	‘Cleanup code here
	myLowerLevelFunction = bReturn
	Exit Function
ErrorHandler:
	bReturn = False
	If bCentralErrrorHandler(msMODULE, sSOURCE ) Then
		Stop
		REsume
	Else
		Resume ErrorExit
	End If
End Function

Open in new window

Thus you’re right, there's no need for the On Error Resume Next, but because this is an anomaly it sticks out like a sore thumb for me. Now you see the disadvantage of me working alone as a coder. In many respects if I was working in a team this sort of code would be a problem. As I don’t, it isn’t, it’s merely a bad habit.

Use of vbnullstring over “”:
Yes, “” is bad. Either vbNullString or Len(String)=0 would be an improvement.  Not even going to argue about that one and that’s a good link.

Can this code…
Sub DisableDelete()
	Cells(ActiveCell.Row,ActiveCell.Column).Select
	Application.OnKey “{DEL}”
End Sub

Open in new window

…be re-written?
Yes, and for the same reason I mentioned before about me using old code that I haven’t revisited in years.

I'd change the "magic numbers" that identify the number of rows/columns…
So should I. I rarely use that form anymore since a lot of my work for clients is now done in either Excel 2007/2010, but because I was building on a piece of code provided by another EE’er I didn’t bother. Is that because I’m lazy? Yes, all programmers/developers are lazy - it’s why we’ll spend 3 hours writing a piece of code to save us doing 1 hour of manual labour ;-).

As idle chatter goes, thanks. The main reason I lurk on EE is so that I can get involved in discussions like this one - I sit and work in my home office and get out about once every couple of months, so it’s good for me to have someone make me think about my code (and force me to review my code library every now and again). Cheers.
Philip
CERTIFIED EXPERT

Commented:
Bright01,
I've taken this thread slightly off course but I hope you can appreciate some of the discussion :-)

Philip,
Lol, indeed, we are all lazy at heart - I have occasionally (often?) spent the three hours coding when it could be done manually in an hour. Of course, we can justify it by the facts that if the logic is right, there will be no "user mistakes" within the results, and that it will be much faster if it is ever needed again. Well, that's my story ;-)

With Statements, yes, it can be a personal preference. In fact, when I considered your example of the one line If statements (which I don't like*), I realised that I usually emphasise With statements more when I am teaching workmates. This is because I think it really helps in development/refactoring when we look at a code print out of recorded, written or "prototype" code and can see duplicated/redundant or even contradictory actions on the same objects. Also, when teaching I repeatedly question "what other lines of code relate to this object?" as an attempt to encourage more understanding of the excel object model & to create some logical order esp from recorded code.

"On error resume next"
Ahhh, I see. Yes, it does stand out like a sore thumb. I looked at it again and again before I decided that I just had to ask. As you can see from my previous post, I'm at the other extreme - in a team and having to share all my code.

"review my code library"
LOL!
Don't we all?
I keep a lot of my code in my personal excel file and it's starting to get a bit bloated - I do have good intentions but...

Rob

Explore More ContentExplore courses, solutions, and other research materials related to this topic.