To me its not the mode that is important, but the settings themselves and ensuring that - at the given time - the settings at the best possible to expose the photo how I need it to be for the creative result I want at the end.
To that end I strongly second the Understanding Exposure book suggested earlier and reading it well. A photographer should aim to learn how to balance aperture, shutter speed and ISO together in any of the modes on their camera so that they are in an informed position - from there you can better select the mode to use, whilst retaining a creative control over the photos, rather than always relying upon chance and the cameras own internal mind.
Myself the mode I use depends on what and how I'm shooting, but as a typical rule:
Aperture Priority mode - my most used mode on the camera and typically my default; letting me control my aperture, and thus depth of field; whilst letting the camera balance the shutter speed - one must of course keep an eye on it to make sure its fast enough and if not, then use either a wider aperture or higher ISO.
Shutter priority - I use this less in general, but for me its main use is for when I specifically want a certain speed creatively. Normally this is when shooting something like propeller planes where you need a certain shutter speed to get the blades to blur - I also use it as a quick wildlife/action grabshot mode - ie speed set to 1/400sec.
Manual mode - typically when I'm shooting where flash is the dominant light source for the scene, and thus the meter won't be giving the best answers (because the camera meter can't balance for flashlight being added to the shot). As such this mode gets used the most for macro work.
Custom modes - on higher end bodies you have custom modes, which are based on the regular modes listed above, but allowing for specific tweakings (eg in custom settings). Thus far I've not used them as much, but I've a macro and action modes setup as custom modes for quick selection.
Auto - quick grabshot mode, though in all honesty Aperture priority mode gets used for this most of the time.
Program mode - never got used to using it thus I never use it myself.