Steam will never ban your account for cheating. They only ever ban you with VAC, meaning you can't get on VAC enabled servers. You won't lose any single-player games.
As for the whole point of this thread, I find Steam to be a mixed bag. On one side, you have the convenience of not having to leave the house to buy a game, and no more CDs. You also have all your games together, and its easy to find new ones you like.
However, Steam also raises problems. If the Steam servers go down, even for a few hours like a few months ago, no one can log onto, and therefore play any Steam games, if they don't have the offline mode on their PC. And offline mode seems to, or at least used to be terrible unless you had started up offline mode, while you had a connection to the steam servers.
Also, I can't pay for stuff on Steam with a credit card due to the incredibly secure checks they do, even though I can pay for things on any other major retailer on the web. And then, if your credit card doesn't work the first time, you get accused of fraud for trying it again. Therefore, I'm forced to use PayPal, which is a godsend.
It seems that Valve has more bugs with steam than with a large majority of its games, and these can ruin the experience. Despite all this, I still use Steam and will continue to do so. I just hope that buying a game in the shops will never die. Steam makes it so impersonal, and the simplicity of getting a game seems to make it less rewarding. I love to have a shiny new box to look at, and a manual to look through, and Steam is taking that away from me.