As a frugal gamer, I don't have many of the newer systems, but I do have a pile of classic PC games that I occasionally have the urge to play again. In many cases, they're not simply still great games, but they have more, better content thanks to the joy of fan mods.
Civilization IV may be the king of moddable games. There are four different levels of modding for the game, from simple XML or fiddling with the game's map software, to more complex Python programming or difficult DLL editing. This means that most people can fiddle with something, but people who really want can really alter the game in massive ways - it'll still be a turn-based strategy game on some geography, but as one mod on the Beyond the Sword expansion proved, Civ4 can turn into X-COM.
Or Master of Magic, as mods like Fall From Heaven attempt to do; or Alpha Centauri, as mods like Planetfall attempt to do; or my personal favorite, Rhye's and Fall of Civilization, which adds several tweaks for historical realism and changing the focus of the game's challenge.
Chances are, people are going to be playing and modding Civ4 in excellent ways until Civilization V comes out, and perhaps even more after that. Check out CivFanatics for a pile of mods both big and small.
I'll still make the argument that Jagged Alliance 2 is the best tactical RPG of all time, so that's reason enough to replay it every now and then. But fans are still out there creating and tweaking content. Perhaps its most famous mod is called v.1.13, which gives the player hundreds of new options of how to play the game, as well as increasing realism and play-balance.
Behind Civ4, Rome:Total War is amongst the most moddable strategy games ever released. Units, buildings, and maps are all customizable, but the basic form of the game (and unfortunately, a rather weak AI) is hardcoded. While most of the mods are based around improving the focus of the game on Roman history, others go to different parts of the world, eras, or even Tolkein-based mods.
My personal favorite is Rome: Total Realism which takes the somewhat goody pseudo-history of the original and converts the game into something more detailed and, of course, realistic. A recent release I've been playing obsessively, Fate of Empires, focuses on the Punic Wars between Rome and Carthage, which was always one of the weaknesses of the game and its mods.
Most of the biggest and best-known mods for games are for shooters. I haven't been able to play too many first-person shooters in the last few years, so this isn't a great list for this. I did, however, recently start replaying this classic shooter/RPG, and discovered that there are several mods out there to add content, increase difficulty, and improve the graphics.
Knights of the Old Republic II was rushed out before its completion, a rather unfair fate for a potentially excellent game. Much of the plot and character development, particularly towards the end of the game, was unceremoniously removed. Modders have been diligently working on finding the removed content and replacing it, as well as adding skins, increasing difficulty, and adding weapons and armor.
The jewel in the modding crown has yet to be unveiled, however. A group called "Team Gizka" is working The Sith Lords Restoration Project, to add the bulk of the deleted content to the game. They've been working for years, but progress does appear to go on. I check it every couple of months, just in case.
Arcanum, like KOTOR 2, was a sadly crippled released before it was ready. Unlike KOTOR 2, Arcanum wasn't really all that good. Official patches helped to fix it, but eventually, a single fan decided to go into the game and open up new content and fix game-breaking bugs in the Arcanum Unofficial Patch. Arcanumwas one of the great disappointments of the post-Fallout PC RPG scene, so having it playable is a wonderful development.
I honestly don't understand why anyone with the option would play a massive RPG like this on anything except the PC. Not that I have anything against consoles themselves, but Morrowind's mods are incredible. The game starts out with incredibly ugly characters? Just add in Better Bodies and Better Faces. Mods adjust game balance, add new content, and basically turn an interesting, large RPG into a massive, fan-made experience.