In calm weather you can photograph using a tripod in much, much lower light than what you can do handheld, so the two methods can actually complement each other nicely.
For me, when I use a tripod, I like a long macro lens the best. 150 mm is okay, but the longer the better. Often (not always!) a short macro lens is very limiting on a tripod. I would avoid it, if at all possible. For larger subjects long telephoto lenses are even better, provided they can focus close enough. I use a 300mm f2.8 all the time for larger subjects, often with a teleconverter. This is not for ease of use, but rather it is to get a smooth background. Of course one doesn't need to invest in such fast and heavy glass, just to shoot floral subjects as a zoom will do too! I just use what I happen to own.
A geared head is also great as it allows great precision when adjusting the camera. Most people start buying a poor quality tripod and head, and then need to buy something better. This includes me.
Actually I have 3 tripods, The Berlebach wooden mini tripod is my favorite, but sometimes it is just too short, so then I use a Manfrotto. Unfortunately the one I got has a center column... avoid center columns at all costs if you ever want to use your tripod low to the ground.
The last one is a huge wooden beast, sturdy, but nothing for general macro use.
Starting over I would buy just one tripod, that goes low to the ground, without a center column and that you can adjust easily. If it has fiddly leg locks it is no good.
Of course there is a time for everything, handheld, tripod, flash. Why limit yourself when you can have it all?
Not really macro, but because the subject of larger subjects as roses has come up:
Iris Bottle Rocket
Nikon D300 + 300 f2.8 + 1.4TC, 0.4 seconds, f/9, ISO 200, Tripod, because who can handhold this rig for 0.4 seconds and get it sharp? Yes you can rise the ISO, but that lowers image quality.
Bumble bee at 3x, focus stack of two images, hand held with flash.
As magnification goes up and especially if the subject is moving a little, then handheld and flash is the way to go. It allows much faster adjustment than a tripod ever can. The use of flash and focus stacking allows enough depth of field even at this high magnification. Natural light is not the way to go here, nor is tripod.
Anyway, just my thoughts. Never understood why we have to use just one method to photograph.
