I know it's cheating, but I put my camera in the tank... makes for interesting pics, and easy close ups!
That aside I've found that quite often the fish are spooked by the prescence of the camera, either in or out the tank, so it's sometimes handy to put food near where you are taking the pic to attract/distract them...
Kate mentioned using a timer, I do too, but my camera has an interval setting, so I can set it to take a picture every 2-10 seconds for up to 15 minutes, as it's digital I often balance it on a log let it snap away while I'm not near the tank and the fish have time to get used to the camera being there. You get a lot of duff shots but occasionally a real goodun, especially if you combine it with leaving food in (or just out of) shot. Being digital, delete the rubbish ones and enter the good ones in the monthly photo contest... Not sure if thats what you meant Kate?
The other problem I have is with fast moving fish and autofocus, as by the time the camera has got itself in gear the fish is somewhere else! It's even worse if I try to focus it myself (I've just got a little digital jobbie, sadly not an SLR) I've found for nippy fish, focus the camera on something in the tank that the fish swim by often, or again use food, and wait till the fish are used to you being there and come past the frame, in focus. Then SNAP, you got one. Can lead to tired arms waiting and keeping still...
I agree that flash really helps, although beware reflections, either from glass or shiny fish, i've lost count of how many "fish of god" pics i've got from an ill placed flash!
Macro is brilliant, just play with it and see what you can get!
Hope that helps!