Glad to hear about a fellow Canon User.

I have an EOS A2E with an underwater housing that I use to take pictures in my big aquarium (the 250,000 gal Atlantic Coral Reef at the National Aquarium in Baltimore). Anyway, the Canon 50mm macro lens is a good lens, but like tdwyatt said, it is a little dark at f3.5. Another problem is that it only focuses down to a 1:2 macro ratio. This means that the image on the negative or slide is 1/2 its actual size. You can get an adaptor that will allow you to focus closer, but it costs more and cuts down on the light reaching the film. The cost for the lens is about $300 and the adaptor is about $200.
Sigma makes a 50mm f2.8 macro lens that will focus down to 1:1, which means that the image on the film will be life sized. That is the lens I have and I love it. Its focusing motor is a little slow and noisy, but it has gotten me some great shots. You can see some of them at my home page at
http://members.tripod.com/thlosey/diving_gallery.htm
I got my lens from B&H in new york (
www.bhphotovideo.com ). They are selling it for $239. You can also find this lens used on
www.ebay.com or
www.photo.net I would think it would go for $150-$200. If you want to spend a bit more, Canon has a new 100mm f2.8 macro lens with an ultrasonic focusing motor. It goes for about $600. Or you can get the older version without USM for around $400. If you want the very best, Canon has a 180mm f3.5L that goes for $1,400. I think photography can be almost as expensive as reefing!
Something else that you might want to invest in that will really improve your tank pics is a remote flash cord. Canon calls theirs the "Off Camera Shoe Cord 2" and it goes for $50 from B&H. When you use this cord with an external flash, you can put the flash above the tank and shine it down through the water. This keeps the flash from reflecting off the front of the aquarium and bouncing back into your picture. I would strongly suggest having someone else hold the flash for you so it doesn't fall in. Something else you can do to get better pics is to make a dark background for your tank. A black trash bag works great. You can also use a trash bag on the front of the tank for sort of a fish-blind. Just cut a hole the size of your lens and wait for your unsuspecting fish to swim into view.
If you decide that a new lens is too expensive, you can always get close-up filters or extension tubes. IME, close-up filters are a PITA because they drasticly reduce your depth of field (the amount of the picture that is in focus). Extension tubes are a little better, but they don't really work well with lenses wider than 50mm or zooms under 100mm.
I hope this helps you figure out what to get. Good luck with the pictures and be sure to post them!
Ted