When I first got into gaming and couldn't play games properly cheats were great. But I would often miss out on the actual game! Although I replayed AOE the other day and the campaign is ridiculously hard.
Now, I don't really care for them. Mods have replaced the only interesting cheats (such as the civilians with guns and rioting in GTA) which actually change the way the game was played. The problem is that there wasn't a great variety of cheats. So big heads was fun, nailing people to walls was fun, anti gravity was fun, God mode was fun but I've done them now and I don't really need to do them again.
However, cheats that made the game harder would be fun. Actually they should just put the ability to seriously edit the game once you've finished it. One shot kills from the enemy, 10x as many enemies, the entire game is at night, limited ammo pickups, that kinda thing. It would seriously give games some replay value. Although the extremely hard modes basically do that now anyway.