I've taken some nice hand-held macro shots with my Canon EF 28-135mm lens. This is my workhorse lens - I use it for everything from macro, to group photos, to portraits. It has the ultrasonic motor for fast quiet autofocus, although I use manual focus whenever in macro mode. It also has image stabilization, which compensates for camera shake when taking hand-held shots. This is a fantastic lens!
I took the butterfly and angelfish shots with this lens.
My true macro lens is a Tamron SP AF Di 180 mm.. This lens takes mind-blowing closeups. However, a tripod and shutter release cable are absolutely required since there is no image stabilization. I find the autofocus completely useless, so I always use manual focus. I haven't had any succcess using this lens with my fish, but it blows my other lens away for macro shots of flowers and bugs.
The greenbottle fly was taken with this lens.