I mean, is it just me, or is it just romanticizing the whole, "the girl isn't interested, but that just means that she really is," idea? Y'know, where it's assumed that a protesting girl "wants it," which takes "dost protest too much" into sinister territory. It makes me wonder if the guys over in Japan have such poor luck getting together with girls...
And that's not it. To me, the behavior itself seems like a mental illness, because it's self-contradictory and thus self-destructive in nature, and the one afflicted by it usually doesn't notice or understand what's wrong. The person they behave that way toward are usually mentally impaired as well, because they usually don't notice what their behavior means (even though it's not unusual for another character to get it, who might drop hints or say something outright). And no matter how you look at it, it's just unhealthy behavior all over.
I know it's often used for laughs, but it's become too trite for my jaded mind to not take seriously. It completely ruins whatever romantic element it may be a part of, because suspending my disbelief for it insults my intelligence. I just can't see the appeal of tsundere (not that I ever have), and I don't think I'll ever understand why anyone else would.
Basically, to me, if it just came down to evaluating its intended purpose, I think it fails as a comedic, dramatic and romantic device. It's partly because of overuse, but mostly because it makes no sense as long as it isn't perceived as some kind of mental problem. Which is why I'm not surprised it shows up in the harem sub-genre so often, because the setup for something like that often doesn't make any sense to me either. (I'm not saying that it couldn't, but I've yet to be convinced of it if the social environment is anything like our own.)