I myself just love this option in games.
But then again, I'm an avid RPG fan. Really awesome weapons make a game truely fun (See the ones in Resistance: FOM on PS3). The game itself was lacking, but the fun stuff with the weapons made the experience great none the less.
And what's better than a great weapon? The same one only stronger

It can evolve gameplay, or at least make your killing easier/funnier.
Also a game that had awesome weapons and ditto upgrades: Painkiller.
And the first FPS-es had very funny and original weapons as well. I'm thinking of Blood, Red Neck Rampage etc.