Despite having lots of different effects, Castles aren't that complicated. Just think of them as having a 2nd pile of Provinces in the game. If it's the kind of game where that's appealing to you, then imagine when you'd start greening in a game with 2 Province piles, and start buying Castles about 1 shuffle or 1-3 turns before that.
If you do the math, buying 8 Castles gives you 45 VP, plus whatever VP you gained from buying Grand Castle. 8 Provinces gives 48 VP. In principle, this shows that buying only Castles is competitive with buying only Provinces. In practice, no one will ever let you buy all the Castles uncontested. At minimum, expect competition on the $8+ Castles. Sprawling and Grand are almost always worth more VP than a Province, and players will buy King just to deny VP from the player who has more Castles.
Having more VP in the kingdom leads to all the follow-on effects you'd expect. More VP makes the game go longer, and longer games favors building your deck more. Many of the Castles help with this - all the Castles between $4 and $7 give you more than just VP. The two Action castles, Small Castle and Opulent Castle, are particularly important. Small Castle can quickly upgrade itself into a better Castle, whereas Opulent is important because it can produce a lot of money when players start greening. This makes buying either Crumbling Castle or Haunted Castle a bit of a risk, because it gives your opponent the first chance at buying Small Castle and Opulent Castle respectively. Of course, how big a risk this depends on how long you think the game is going to go. The sooner the game end, the less the Action castles matter. Buying Haunted Castle also makes it less likely your opponent hits $7. I often find that the risk of revealing Opulent Castle is worth the 2 VP + Gold + topdeck attack that Haunted Castle gives.
As for Humble Castle, it's a little weird. It gives VP for every Castle, and there's only 1 Humble Castle, which can make it look like it's worth fighting over. However, it's still a genuinely bad card to buy. At the end of the game, Humble Castle is usually worth 3 VP to 5 VP, and although that's above-average rate for $3, buying it early on is a huge opportunity cost. There are two cases where it's worth buying Humble Castle early. One is Shelters games, to turn your starting Hovel into a Copper. The other is Keep. In a Keep game, Humble Castle is always worth at least 6 VP. Even in this scenario, I don't like opening Humble Castle. 6 VP for $3 is very cheap, but opening buys are very, very important. On most boards I'd gladly give my opponent 6 VP if they were forced to open Copper.
Besides these considerations, Castles are mostly a tactical decision, not a strategical one. They aren't like Gardens, or Duke, or Fairgrounds, where you have to plan a bit to get VP out of them. Castles are just there, and whether the VP is worth it or not depends on where you think your deck and the game are at.