Over the years I have run some 100 modules (but there are thousands) and the only one I have run more than once is the original Tomb of Horrors, I liked it and different sets of players liked it. I am not saying it is the best ever module as I do not think it translates to D&D 3e very well. I am very interested in DMing the Lair of the SpiderQueen, as it reads very well. Just need to see if my current group of players can get up to the level required.
Ah, a Mystara fan. That's a great list. I have to admit that I could never figure out what to do with Where Chaos Reigns (save a world you've never encountered before and care nothing about from the borg?) or Twilight Calling (The worlds are pretty cool, but the Carnifex aren't really detailed enough to care about.)
In my mind Castle Amber is only knocked out of first place by Talons of Night. Talons is an exciting, varied high level adventure. It's got:
Riddles, mystery, intrigue, and high level diplomacy that actually matters.
A sea battle against a warship full of undead.
"The Spiders Web," a game within the game which carries thematically through the entire adventure.
Araneans
Nightshades
Creepy shapeshifting spider monks
A knock-down drag out fight against a vampiric monk and his legion of undead followers.
An ancient spider god, cast out of the heavens for unspeakable crimes.
A showdown with said god in her web, over a miles deep pit.
A whirlwind journey across a plane which is turning you into an undead monstrosity.
If you need them, a fantastic set of pregenerated characters.
I've never played this adventure, but all of my friends swear by it. They've played it more than once (apparently it's that good that it still holds up when you know what's going to happen). I'm not sure if it was a module or an adventure in a Dungeon magazine. Sorry.
The Desert of Desolation series, the original Ravenloft, and the lost Isle of Castanimir (it was damn confusing for the second module I ever played).
But my favourite would have to be the Ghost Tower of Inverness (C1, I think). How the characters end up in the north of Scotland is beyond me, but I really enjoyed playing and running it.