I will second Kyles recommendation on the sony L style cybershots. I have the f717 and will probably go for the 828 soon. Macro shots are very simple and awesome. I recommend getting some closeup lenses for shooting frogs. It allows you to take a really tight shot without being 1" from the subject. Those lenses also seem to help me as I don't have to bury down so far in the tank, when I do my lenses fogs up pretty easily.
I would say those are your best choice for a point and shoot, if you want an SLR thats a whole new story (then again you probably wouldn't ask if you were in the market for an SLR)