Agreed with Reefdive. Especially with macro photography where the depth-of-field becomes vanishingly small it's critical that you bring the light to allow you stop down your lens to attain the depth that you need to have the shrimp/fish/whatever in complete focus.
As for setup, I've found that you can either lay the flash flat directly on the glass top of the aquarium (if you have one) with the bounce card pulled out. That'll let you bounce the flash down. I've also been able to bend the flash and balance it over the edge of the aquarium.
Overall, I've found that lighting will improve your images by leaps and bounds once you have the basic lenses covered. It's a worthwhile to learn skill!