I think a large part of appeal of macro photography is that the nearest subject is more than likely less than 2 feet away. There are literally billions of opportunities! That is one reason that my macro lens (an Olympus 60mm f2.8 because I use micro 4/3 and the Panasonic/Leica 45mm f2.8 is too expensive and slow to focus) is my most used lens (also because it’s one of the sharpest lenses in the m4/3 (or any) system)(sorry for bragging). Also, you could take a thousand pictures of some macro subject, or most anything in nature (trees* for example), and non of them would be the same, unless of course you did on purpose.
There are some turn-offs though. For example, macro lenses are usually pretty expensive, and you have to use either a tripod (although I think tripods help with composition, as they force you to slow down) or a flash in anything but the brightest light (and those can get expensive, too), because at very close distances the depth of field is very shallow unless you stop down to at least f8 (on full-frame, equivalent to f5.6 on aps-c, or f4 on m4/3, one of the advantages of the format if you shoot macro). Some of the expense of macro lenses can be off-set by using extension tubes or diopters with lenses you already have (diopters, being filters, are your only option beyond the macro mode on p&s cameras), but they both have their disadvantages: diopters, being filters, at least slightly degrade the image quality, and extension tubes, while they don’t have any extra optics in them, cause light loss because they increase the distance between the lens and tha camera. Macro lenses may be expensive, but they are usually one of the best lenses in their system, and are the best option for macro photography if you can stomach the price (though some entry level macro lenses can be pretty cheap, as lenses go).
In conclusion: although it can be expensive, macro photography is very fun (for me) and is probably the most interesting field of photography, as a lot of times, macro photos are of things we can’t see very detailed with our naked eyes.
*here is a link to a photoessay on trees by an extraordinary photographer: http://blog.mingthein.com/2014/04/20/photoessay-trees-revisited/