At the risk of inflamming the discussion even further, I have to say that I LOVE my Steam. I can't imagine gaming without it, in fact.
I used to be a die-hard CD/DVD guy - I had to be dragged kicking and screaming into Steam (it was Empire:Total War that did it - even though it came on DVDs, it HAD to install and use Steam, which INFURIATED me at the time!).
At first, I was really annoyed by Steam. What is this stupid overlay that runs over all my games?!? Oh wait, I can shut it off... ok then. And what's this?? It's automatically updating my games?? Well, that's actually really awesome 99% of the time, but it sucks for that 1% exception... but it turns out, I can turn off automatic updates, on a game-per-game basis (and enable beta patches on a game-per-game basis too - cool!)
But the BIGGEST wake-up call happened when I started checking out the store, just for fun, and hit the Steam Summer Sale... WOW. I was like a kid in a candy store, but the entire store was 50-80% off! I picked up a ton of games... I think I ended up getting about 13 or 14 games for $100... a steal. And these were quality games, that's I'd seriously considered buying (and I still hunt the big Steam sales - I picked up Batman Arkham City - Game-of-the-Year Edition, for $7.50 the other day... score!)
And I'll second the opinion stated above by nfitzsimmons that a major advantage is convienience when you re-install your computer. This is something that's (thankfully) not as common as during the Win98 days, but I still need to completely wipe and re-install Windows every 18 months or so. Being able to just load Steam, and tell it to download, install and patch all of my 200+ games all by itself is worth a LOT to me.
Having said that, I know that some people will never touch Steam with a 10-foot pole, and that's fine. But I just thought I'd give my 2 cents as to why I think Steam is among the most valuable sofware on my computer.
(BTW - I was a long-time Trainz user - I bought the Original Auran version back in 2002, I think? - but I had put it aside for many years. The Steam sale convinced to get back into it - for 15$, I got Trains 2012 and Murchison 2, which is amazingly brilliant, btw).