Hmm well I used add-ons alot in WoW.
For me they fall into 2 distinct types.
1. Vanity addons. These add-ons allow you to customise the look and feel of your interface, or provide you with information after the fact. Good examples of this type of add-on would include things like Titan panel, which is a customisable strip at the top of your screen which can be adjusted to show things like money/time/location etc. It has no effect on the game put makes life a little easier. Raid frame UI's would also fit into this category, as would utility bar mods, minimap mods etc. Equally, add-ons like DPS/HPS meters would fall into this category. They only provide information about what has already happened, and allow you to examine your own play style and ability both individually and vs the group. This type of mod I don't have a problem with at all, and should be encouraged.
2. Combat enhancement add-ons. So this is the second genre of mods that are available. These mods directly affect how you play the game, either by giving you a cue to do something, or directing your play style through instructions. An example of this type of mod is DBM (Deadly Boss Mods) which is a boss encounter mod that provides you with on screen cool down counters for major boss abilities, as well as flashing warnings and audio alerts for many abilities. Additionally mods which provide shot/spell/ability rotation instructions, or timers would fall into this genre. Equally, quest mods which direct you through quests would fall here. This type of mod directly affects how you play. These mods instruct your game play rather than letting you learn and discover the game for yourself. I am very anti this type of add-on.
I don't believe that the question of add-ons is a simple one. Some are good and some are bad. For me personally I want the ability to customise my UI, to get data feedback how and when I want it, and to be able to compare and examine my performance in both solo and raid environments. I don't however want to be led or instructed by add-ons. Not only do they spoil the game experience for gamers, but lead to encounters becoming overly complicated in order to combat the effect of mods guiding groups through encounters.
TLDR = Yay to some. Nay to others.