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 gts 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.