Like you I imagine, I've been playing online games for a long time. I even ran a half dozen TFC / Natural Selection / CounterStrike / Half-Life Deathmatch / etc. servers for three or four years. I never found cheating to be common except for CounterStrike. For some reason that game attracted cheaters like crazy. The other games, not so much. Cheating wasn't just uncommon - it was rare.
When PunkBuster and similar products became popular it was amazing how much better I became compared to other players when playing on a protected server. (o:
VAC has, in my opinion, done a very good job overall of keeping up with the cheating crowd. I can't remember the last time I came across a player that I suspected of cheating - and having had to do detection manually by watching player behavior, I'm very confident in this.
There's a few things you can look for manually when looking for cheaters.
Your typical aimbot is easy to detect. Jump into spectator mode or whatever and pick the first person view for the selected player. Instead of the smooth movements a typical player will have, you'll see the player's aim snap to positions on a screen. It's rare to see these anymore because detection is so incredibly easy.
Driver hacks to provide see-through textures, or model hacks that have a long cross through them that extend through walls, are also pretty easy to detect by watching the player. Is someone across the map and scoring head shots through walls? Does he always seem to know where the enemy is? He's using one of these.
The interesting cheat is the second one (wall / model hacks) which allows one to see opponents behind objects, because it's not a mechanical advantage like an aim bot; it's a strategic advantage, an information advantage. It doesn't change the ability of the cheater to aim more accurately; it changes the cheater's behavior. A player without the cheat information will act as if the opponent is not there; a player with the information will.
So, you'll see tactical advances / retreats, shots fired / grenades thrown, etc. that would not occur in normal non-cheating game play. Yes; there will always be the person who gets the lucky what-the-hell shot. That happens.Sometimes more than once. What you need to look for is a consistent pattern over time that cannot be attributed to simply being "good", having a better overall strategy, or having an unusual play style.
I bet that with enough information collected it would be possible to detect this kind of behavior and flag individual players for follow-up manual inspection. It would be a fascinating bit of research, really.
Resource hacks are very dead these days, as information about resources (ammunition carried, money earned, life amount, etc.) are all stored server-side for most games. There's no way for the client to fiddle with that data.