Page 6 of 16

December 8, 2007

Microsoft Project VBA - the Instr function

The Instr function is used to find out if one set of characters (string) is contained in another. It can be used in VBA macros (in Word, Excel, Project etc.) and also in Microsoft Project and Microsoft Project Server Custom Field Formulas. In Project this can be used to find out if a task name contains some special coding. For example perhaps you have used a special naming convention to separate time tracking tasks from other tasks and you need to roll them up separately. By using Instr you don't need to have the coding in a specific position. You can put it at the beginning, middle or end of the name and Instr will still find it.

Instr is also useful in string manipulation because it returns the position of the first occurrence of a string in another string. Using it in conjuntion with the left function you can strip out leading characters.

The InStr syntax is pretty simple:

InStr( [start], string_to_search_in, string_to_search_for, [compare] )

start is optional. The default is to start at the first character, but if you want to skip the first character then you can set it to another value. This could be useful for looking for the second occurance of the string you are looking for. You could feed in the result + 1 of another instr function to see if the string occurs again. Using this recursively you could count specific characters.

string_to_search_in is the string that will be searched.

string_to_search_for is the string to search for.

compare is optional. By default it does a text compare so most likely you can leave it out. The valid choices are: vbBinaryCompare, vbTextCompare and vbDatabaseCompare. I'm not even sure what vbDatabaseCompare is so don't worry about setting compare unless you are doing something a bit more advanced.

Here are a couple of examples:

  • InStr(1, "This is a time tracking task", "tracking") would return 16.
  • InStr("This is a time tracking task", "is") would return 3.
  • InStr(10, "This is a time tracking task", "t") would return 11.
  • InStr("This is a time tracking task", "abalone") would return 0.

From the examples, you can see when it CAN'T find what it is looking for it will return 0. This is an important point to remember. The typical test I'd use in a custom field formula would be to test the return from instr in an iif formula. Maybe something like this:

iif(instr("My haystack","needle")>0,"ouch", "zzzzzzzzz")

November 18, 2007

Las Vegas as an oyster

Las Vegas Sidewalk

I visited Las Vegas for the first time this last week and my first impression was that it is a city that works very hard to keep you inside. Finding the exit to a casino is an adventure in itself. Walking along the "Strip" with its broken pavements, escort touts flapping hooker cards and caged-in walkways only encourages you to stay inside. And they spend a lot of money on making the inside attractive. Much of it is not to my taste, but the Chihuly light fixture in the Bellagio is quite wonderful.

Chihuly Light Fixture

Like an oyster, the pearl inside is more attractive than the shell, and like an oyster there is some risk in consuming it.

November 8, 2007

Project Server 2007 Service Pack 1

So... the good news is when pushed for an answer in front of his boss, Mike Angiulo finally gave a date for the long awaited release of Microsoft Office Project Server 2007. The bad news is that the date is in March or April of 2008, more than a year after the release of Project Server.

Details and a critical (but I'm hearing accurate...) write up of the announcement and the Project Conference can be found here

I hope that Mr. Angiulo will exceed expectations and deliver early. It would be a celebration for applause.

November 7, 2007

I'm faster

This jelly fish spends its life in a glass box in the Monterey Aquarium.

I spend my life in a box that has fabric panels that you can stick pins in.

The jelly fish gets more admiration.

But it is basically the same feeling of weightless otherworldliness we must both experience when swimming at night.

Miscellaneous notes on aquarium photography:

  • Many museums do not allow tripods or monopods.
  • Go mid-week when there are no crowds.
  • Hold the camera close against the glass to avoid reflections.
  • Don't use flash on Jellies. They look best back-lit.
  • Use the fastest lens and camera speed (ISO) that you can.

September 22, 2007

Golden Glow

Golden Gate Bridge at Night

I heard my blog doesn't look as good as it used to so I'll post a few pictures while you are all waiting for my next post on formatting your gantt chart.

This is the Golden Gate Bridge in fog taken on my way home from a company dinner a few weeks back.

August 29, 2007

Views and Tables in Microsoft Project

There is often some confusion about the difference between a view and a table in Microsoft Project. This should help clarify the difference between the two.

A view consists of a number of potential elements:

A screen (the right side typically, but may include splits. Also includes any special formatting for that screen such as modified barstyles etc.)
A table (the data displayed on the right side
A group (optional)
A filter (typically "All tasks" but may be anything you want)

Different views may access the same table or group or filter or screen but may use a different combination.
Modifying a table will affect all views which use that table.
For this reason, and because I frequently add/remove columns, it is good practice to build a specific table for each view. For example you might build a presentation view which uses a table and filter customized to show only the elements you want to present, or a print view etc.

The data displayed in the table or view does not belong to the table. It is present in the file and is only displayed in the table, so deleting columns in the table does not delete that information. Deleting a row however will delete the information.

This information is the same for all versions of Project, with the exception of Project 98 and earlier which did not have grouping.

August 7, 2007

10 pieces of advice to a new blogger

I wrote this email to someone who is considering starting a blog.

Building your blog:

If no one reads your blog you will give up on it eventually. There is no point writing something that no one reads. So you need to have some way for people to find it. Search engines are the main way to get traffic. This requires that you have something worth reading on your site first.

85% of the people who arrive at my site found it through Google. Maybe 5% use Yahoo and 1% use MSN. The rest are from links here and there. This article gives some pointers on making your blog search engine friendly:

Here are my top ten pieces of advice:

  1. Include your blog URL in your newsgroup signature and if you have already solved a problem then point people to the in-depth article about it.
  2. Don’t submit your site to the search engines! Rumor is that they put you in a sandbox for a few months if you do so. If you have good content you can get people to link to your site and Google will follow those links and index your site. The more links the more important Google thinks your site is. But go slow on getting other people to link to you. Steady growth is rewarded. Fast growth looks like spam to the search engines.
  3. Don’t go too wild with categories. I think I have too many, but after a while you have too much stuff to bother reorganizing.
  4. Keep focused on one main topic which you care enough about to spend time on. I throw a lot of different sorts of crap in my blog and I don’t think it does much to help me. I noticed I was posting enough about other things that I created a second blog ( to handle miscellaneous postings. But then I also created a few other blogs I completely abandoned so don’t go overboard.
  5. Don’t bother creating daily or weekly or monthly or yearly archives. They will confuse the search engines and no body cares about them anyway.
  6. Don’t expect much for 6 months or so. Traffic is roughly proportional to the amount of content you have. The more you write, the more it grows.
  7. Keep articles short. Attention spans for online reading last about a single page at most. Unless you are a brilliant writer, most people just want an answer to their question or some new knowledge. They won’t finish reading something that is too long. You can always break long articles up into a series of smaller ones.
  8. Start any post with a attractive and relevant description. The first sentence often shows up in the search results so don’t be writing things like “I was at the coffee shop with Blaise and I spilled a frappuchino on my pants” and then launch into how to set up a workflow in Sharepoint. The Sharepoint readers will be disappointed and so will the coffee on the pants fetishisti.
  9. If there is something you have taken the time to research and understand, share it. For example, I’m probably putting this list on my blog.
  10. Ignore this advice and try to have fun with it. I disregard any of these rules if they interfere with my enjoyment. The rewards of blogging are subtle so if you don’t get enjoyment from the process, then you will soon realize you are wasting your time.

August 6, 2007

OpenProj First Look


Projity is introducing a new free MS Project compatible scheduling program which they bill as "a complete open source desktop replacement of Microsoft Project". I have tried the beta briefly ( and I like what I see. While it looks very much like a clone of MS Project, it strips down some of the complexity from the interface and makes some common tasks easier (viewing a stacked resource graph for example). It opened my MS existing MS Project 2003 .xml files with no visible problems - though even MS Project has some quirks in handling .xml files, so it will pay to double check this before using for a real schedule.

I think this is the first MS Project viewer which will really make inroads against MS Project. This is true for a number of reasons:

  • It is free
  • Look and feel similar to Project (maybe too similar... but let the lawyers decide)
  • File compatibility
  • Ability to edit and create schedules
  • Cross-platform compatibility (Mac users will rejoice after being denied the latest versions of Microsoft Project)

The developers of OpenProj appear to have come from Scitor (now Sciforma) which was known for their "Project Scheduler" software, the latest version of which is PS8. PS was always an also-ran in comparison to project because of Microsoft's millions of installed versions of Office and associated enterprise licensing. By focusing on compatibility and on Linux/Mac and by providing the software for free, Projity may have a better chance of initiating cracks in the marketplace. The ultimate goal for them must be in project hosting or selling enterprise-level features (multi-project support etc.).

On the other hand, I think that desktop scheduling products are fairly dormant. Microsoft has been moving away from them by adding things like server-side scheduling and the ability to create and maintain projects through Project Web Access. Meanwhile, new features on the desktop are very slow in coming.

The next battleground will be online scheduling and by providing a full featured client for free, Projity is signalling their readiness to fight. It should be interesting to see how things go. Competition in this space will drive Microsoft harder and that should be good for everyone.

I'll try and get to a more in depth review when I have a chance.

August 3, 2007

Coming Home

Moving Walkway San Francisco Airport

One thing I like about airports is moving walks. This one is in the connection between the International Terminal and the International Parking Garage at SFO. It is the last of the airport that I see when coming home.

July 27, 2007

how to refinance my house loan when house price dropped

It seems like the eventualities I was discussing a couple years back (see housing bubble stories here) have finally come to pass. A reader arrived here today wondering "How to finance my house loan when house price dropped". It is a difficult question. At a certain point when the money you owe is more than the house is worth no one is going to be happy about lending you money unless you have a good income stream or some other assets as collateral.

Many of the companies which were in the business of extending loans for the full value of the property with no income verification have imploded and lending standards are tightening. The entire "sub-prime" lending world has disappeared. This is happening at the same time as some of the teaser rate loans are undergoing their metamorphosis into instruments with a fairly high rate and correspondingly high monthly payments.

So the simple answer is talk to your lender. If it looks like you are going to go bankrupt or default on the loan they have some motivation to work things out with you. If they aren't willing to talk to you it is unlikely anybody else will be.

July 26, 2007

Project Professional 2007 File Lockout bug

Just a friendly warning that using the red X in the upper right corner to close a project professional plan which is on Project Server may lock you out of the project altogether. This bug was present in Project Professional 2003 but was fixed in a service release. Looks like it is back with this version. For now, be sure to use "File / Close" to close a project.

I usually don't post much about bugs, but since this one can be avoided it is worth noting.

July 25, 2007

All your models are belong to us

I was writing a note to someone about the use of PERT and Monte Carlo simulation in Microsoft Project and it made me think about how willing people are to accept simple models.

As Box and Draper said "All models are wrong; the practical question is how wrong do they have to be to not be useful*" (frequently paraphrased as "All models are wrong. Some models are useful"). The margin of error in almost any schedule is going to be in the range of several percent. Building a probabilistic model of the schedule will help give an idea of the range, but even that sort of model has a margin of error.

My point here is to not get carried away with increased precision in the model beyond the point where it exceeds the limit of accuracy**. Schedules are probably only accurate to 2 significant figures so there is no need for five digits beyond the decimal point for % complete.

The biggest holes in the schedule are not going to be from rounding, but rather from things which were not considered or hand-offs which were not properly made. Modeling those events is difficult if not impossible, the answer is to avoid them in execution through proper planning and keeping track of dependencies.

* Ref: George Box and Norman Draper, Empirical Model Building and Response Surfaces, John Wiley, 1987, pg. 74

**Accuracy refers to how close a measurement is to the "true" value. Precision is how finely we can discern differences between values. You can have a highly precise measuring device which is always inaccurate. In a pinch always choose accuracy over precision.

July 19, 2007

Swanson's uh... W.L King's Unwritten Rules

Cleaning out the queue of old articles that I never got around to publishing:
This set of rules was "unwritten" by Raytheon CEO Bill Swanson, but it turned out that it he cribbed a number of them from W. L. King's 1944 book "'The Unwritten Laws of Engineering."


1: Learn to say, "I don't know." If used when appropriate, it will be used often.
2: It is easier to get into something than to get out of it.
3: If you are not criticized, you may not be doing much
4: Look for what is missing. Many know how to improve what's there; few can see what isn't there.
5: Presentation rule: When something appears on a slide presentation, assume the world knows about it and deal with it accordingly.
6. Work for a boss to whom you can tell it like it is. Remember, you can't pick your family, but you can pick your boss.
7: Constantly review developments to make sure that the actual benefits are what they were supposed to be. Avoid Newton's Law.
8: However menial and trivial your early assignments may appear, give them your best effort.
9: Persistence or tenacity is the disposition to persevere in spite of difficulties, discouragement or indifference. Don't be known as a good starter but a poor finisher!
10: In doing your project, don't wait for others; go after them and make sure it gets done.
11: Confirm the instructions you give others, and their commitments, in writing. Don't assume it will get done.
12: Don't be timid: Speak up, express yourself and promote your ideas.
13: Practice shows that those who speak the most knowingly and confidently often end up with the assignment to get the job done.
14: Strive for brevity and clarity in oral and written reports.
15: Be extremely careful in the accuracy of your statements.
16: Don't overlook the fact that you are working for a boss. Keep him or her informed. Whatever the boss wants, within the bounds of integrity, takes top priority.
17: Promises, schedules and estimates are important instruments in a well-run business. You must make promises — don't lean on the often-used phrase: "I can't estimate it because it depends on many uncertain factors."
18: Never direct a complaint to the top; a serious offense is to "cc" a person's boss on a copy of a complaint before the person has a chance to respond to the complaint.
19: When interacting with people outside the company, remember that you are always representing the company. Be especially careful of your commitments.
20: Cultivate the habit of boiling matters down to the simplest terms: the proverbial "elevator speech" is the best way.
21: Don't get excited in engineering emergencies: Keep your feet on the ground.
22: Cultivate the habit of making quick, clean-cut decisions.
23: When making decisions, the "pros" are much easier to deal with than the "cons." Your boss wants to see both.
24: Don't ever lose your sense of humor.
25: Have fun at what you do. It will be reflected in you work. No one likes a grump except another grump!
26: Treat the name of your company as if it were your own.
27: Beg for the bad news.
28: You remember 1/3 of what you read, 1/2 of what people tell you, but 100% of what you feel.
29: You can't polish a sneaker.
30: When facing issues or problems that are becoming drawn-out, "short them to the ground."
31: When faced with decisions, try to look at them as if you were one level up in the organization. Your perspective will change quickly.
32: A person who is nice to you but rude to the waiter, or to others, is not a nice person. (This rule never fails).
33: Never be afraid to try something new. Remember, an amateur built an ark that survived a flood while a large group of professionals built the Titanic!
Postscript: The qualities of leadership boil down to confidence, dedication, integrity and love.
My postscript would include honesty just in case your integrity fails you...

July 2, 2007

Microsoft Project 2003 and 2007 Cross Version Compatibility

There are many questions about which versions of Microsoft Project and Microsoft Project server are compatible. Here are the general rules:

  • Project 2000 connects to Project Central
  • Project 2002 connects to Project Server 2002
  • Project Professional 2003 connects to Project Server 2003 and Project Server 2002
  • Project Professional 2007 connects to Project Server 2007

No other connections are possible.

Project 2007 was redesigned in a number of ways to make it more efficient and improve performance. These changes included changes to the database schema and to the method that data is transferred. ODBC was abandoned in favor of Web services. This means that compatibility between the two versions was broken completely

One nod that Microsoft made to ease the pain was that they did make it possible to install the 2003 version and 2007 version side by side on the same machine. This applies to both Project Professional and Project Server.

It is important to note that Project 2003 knows nothing about 2007 so you should always install 2003 before you install 2007 or 2003 may overwrite the 2007 installation or do other bad things.

Project 2007 can read from all versions back to Project 98, but can only write back in Proj 2000 or later. It can read from ODBC but not write back to it. This means that any .mpd databases will have to be converted to stand-alone files or stored in Project Server if you want to continue working with them.

June 25, 2007

Error Error in Windows Vista


I got this error message today using Windows Vista. It is a new one to me. I'm wondering if there is an error report for errors in error reports. After tomorrow I'm back on WinXP and am looking forward to it. It is probably a case of "the devil you know" more than anything, but my Vista experience has been less than "WOW!"

In a way, the ultimate OS is one which is invisible. Vista is far from that. WinXP was much more understated and hasn't really let me down so I see no need for gratuitous change. Sometimes, as Mies said, "Less is More".

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16


Creative Commons License
This weblog is licensed under a Creative Commons License.
Powered by
Movable Type 3.34