I've almost certainly put more hours into Heroes of Might and Magic 3 than any other game. And I am not alone in this. I wouldn't say that it's the game that has received the greatest amount of love from the greatest amount of people over a long periood of time; Doom 2 definitely has it beat. However, it's up in the top 10.
What makes the game so great? In particular, what makes Heroes 3 so beloved when compared to other games in the franchise? For example, there are still people who play every single Civilization game, including spinoffs like Alpha Centauri and Call to Power. But it is hard to get a consensus on what the best one of those games is. I have a feeling like Alpha Centauri might edge out the competition just barely, but even Alpha Centauri supporters will generally praise innovations from things like Civ 3 and 4. On the other hand it's universally agreed that Heroes 3 is the best one. There are plenty of people like me who appreciate the rest of the series. I still play Heroes 1, 2, 4 and 5 and like elements of each of them more than Heroes 3. But when it comes to which is the best overall game, I can't give any answer but Heroes 3.
To begin with, there's no denying that Heroes 3 is simply a very well put together game. It doesn't hit some mythical level of balance where every option is valid. Eagle Eye and Learning are trash skills, spells like Hypnotize or Remove Obstacle are incredibly situational at best, and Inferno lags behind the other towns. War machines are of limited usefulness in most cases, and many artifacts can break the game. But on the flip side, a good player can use pretty much any town or hero to succeed, and there are so many possible broken strategies that you don't always use the same one every game. That is, there are some things that are obviously good and others that are obviously bad. However, the game as a whole is balanced in the sense that there are counters for everything and that you can always try something new. In many ways I find this more engaging than the modern game design of nerfing anything that gets too strong or buffing anything that gets too weak.
The basic game loop of Heroes 3 is extremely satisfying. Only the Civilization series can rival it for the "just one more turn" effect. You get a real sense of exploration at the beginning of the game as you feel out the map. Unlike Civilization, where you will mainly find empty terrain with some goody huts, in Heroes 3 you will find treasures, quests, special locations to strengthen your heroes, dungeons to clear out, etc. The end game will involve careful strategic maneuvering against enemy players. Throughout it all you will develop your heroes. It is extremely satisfying to start with a hero with a weak army who hardly contributes anything to his troops, and end the game with a master wizard who can shift the tide of battle with each spell, and whose leadership abilities make even weak creatures a nightmare to deal with for low level opponents. This is much like the rise in technology that you see in a Civilization game, except in Heroes you can lose your best hero in a climactic battle, or take out an enemy hero in the same way, making the key battles feel higher in stakes than anything I've seen in other video games.
Of course, everything that I've said in the last paragraph applies to the Heroes of Might and Magic franchise as a whole. So what's good about Heroes 3 in particular? Compared to 1 and 2 there is just so much more content. While I do enjoy Heroes 1 and 2 a lot, Heroes 1 has 4 towns with a total of 28 creatures and Heroes 2 has 6 towns with a total of 66 creatures. The complete edition of Heroes 3 has 9 towns with a total of 140 creatures. This especially matters when it comes to map making. There will be many times in Heroes 2 or especially Heroes 1 where you want to create some sort of fantasy scenario, and you realize that the game doesn't have the right creature to represent what you are thinking of, so you are forced to select something that's only kind of like what you want. This happens much less frequently in Heroes 3. And of course in terms of random gameplay, you don't get into the rut of using the same creatures as much as in the previous games.
That brings us to the random map generator. This didn't ship with the base game, but was added in the Armageddon's Blade expansion. (And as such, was not included in the HD Edition, one of many reasons that that version of the game sucks.) The rnadom map maker for HOMM3 has been praised as among the best. There was one of these for Heroes 2, but it only technically made workable maps. That is, it would spawn starting towns for players and make sure that no one was blocked by mountains or trapped on an island with no shipyards or portals, but beyond that there wasn't much care as to placement of monster and items. You could start with a stack of titans blocking your town; this happened to me in an actual hot seat game. This means that while you can technically win by very slowly building up your army from your basic income until it can beat the titans, you will have to spend so many turns doing this that the other players will have built up on the map and thus will be impossible to beat by your pathetic army. The heroes 2 map maker also has a tendency to put in a lot of empty space, and (if you have the expansion) to overuse expansion buildings like alchemist's towers, arenas or elemental confluxes (though thankfully it doesn't spawn the buildings that let you recruit ghosts.) Even if the monsters are fair, you might be lacking key mines or even such basics as a sawmill for wood, meaning that you will have to rely on expensive marketplace transactions.
The Heroes 3 random map maker, on the other hand, will do a much better job of filling up the map in a reasonable way. The monsters guarding the essentials will not be the toughest in the game; those will be reserved for things like guarding the high class artifacts. Since space is filled monsters will do a better job of actually guarding things; you're not going to see a stack of dragons in the middle of nowhere that you can just walk around three steps away from an ultimate sword that you can just pick up off the ground. Each starting town gets a full complement of mines nearby (though not always trivial to get to), though when you get further away from starting locations things are more up in the air. Roads are used to hint at where towns are. The result is that random maps are very enjoyable to play and still give you a sense of exploration, to the point where you could just play on random maps forever (and many people do.)
Of course, there are dozens if not hundreds of extremely well designed Heroes 3 maps that you can play to the extent of never needing the random map maker. Timothy Duncan is probably the all-time champ when it comes to story based Heroes 3 maps (Gold Heart, Sandor's Folly, etc.) I also quite like the pride series (Angelic Pride, Titanic Pride, Devilish Pride) even though the first one kind of got fucked over by Shadow of Death giving the broken Cloak of the Undead King to the necromancer enemies (which didn't exist at the time of the map creation.) There are probably dozens of Lord of the Rings maps alone.
Some of the great community maps are just good maps for exploration or well balanced skirmishes against other players. Others are like this, but with a theme. Many of the aforementioned Lord of the Rings maps are like this; some are just maps to kill all of your enemies, but with a map meticulously modeled after JRR Tolkien's maps of Middle Earth. One of official maps is the same thing, but based on the maps in Might and Magic 6. You also have things based on real world Europe, etc. However, where community maps really shine are the story based maps that have an intended development for the player and the AI. For example, in each of the three pride maps you will start with three heroes. One will be at a main town, which is impoverished and dificult to beuild up. Two will be isolated and must go through many trials before meeting up with the main hero. There will be places where heroes are scheduled to meet up, actions that you do which have effects on the AI (for example, the AI might attack a certain region in response to your progress, or in one map to proceed you must release a powerful enemy.)
What makes this amazing is that there were very little tools for the designer to work with, especially in maps designed for the original Restoration of Erathia version of the game. You have border guards, which are map elements that can be removed only once you have visited a corresponding tent, which can be used to guide the progress of the AI and human players. There are teleporters to get you to the right place (though precious few of them before the Shadow of Death.) There are Seer's Huts which will give you a reward for completing a quest like returning with a certain artifact or killing a certain hero. And that's pretty much it. Note that there is no scripting for the AI that is available to the player; you have to exploit existing AI behavior. Beyond that it comes down to things like cleverly restricting which skills heroes have (by giving them a full complement of 8 skills from the start, so they can't learn more), which spells are avaialble, etc. For example, at one point in Titanic Pride you need to get to a town which is only accessible by using the spells Fly or Dimension Door. But you can bet your ass that the map creator went to every single town in the game to make sure that those spells would not ranodmly spawn in any of them. You need to complete a certain quest to learn the spell, which allows the designer to restrict access to the town to a certain part of the story.
For an early example of AI exploitation, in that map you begin with a single Titan near a hero with many more angels who can easily kill you. But he must pass through a Lake of the Scarlet Swan location which will take all of his movement points, giving you enough time to board a ship. He will board the turn after you, but since that takes all of your movement points too, you can move away from him. After this he can quickly catch up in theory, since he has the skills to have far more movement points on water than you do. But the AI will prioritize collecting flotsam items for resources giving you time to get away (though if you greedily try to grab those items yourself, you will get killed.) None of this is hard coded; it was all designed by the map maker using a deep knowledge of game mechanics as well as plenty of play testing. In many of these maps you'll have these sorts of "pseudo-scripted" scenarios occur at points where you will be months (i.e. dozens of turns) into the game, meaning that playtesting would be non-trivial. You have to admire the dedication.
The ultimate lord of the rings map