The wildcat formation is effective only in limited situations, and in particular relies on:
- Skilled players
- Good O-line play
In 2008, the Dolphins famously ran the wildcat against the Patriots, demolishing a team that was considered a major (12.5 point) favorite - and hadn't lost a game other than the Super Bowl in two years. It worked - but it worked because of the surprise factor, and because the Dolphins had two players well suited to playing the wildcat. Ronnie Brown was shown capable of passing occasionally, and he was a good pairing with Ricky Williams in terms of skillset.
They also lost the next matchup to the now-prepared Patriots by 20 points - and after that year, never again really had much success with the Wildcat.
Why do teams not do this very frequently now? Partially because it's less of a surprise - but mostly because it's morphed into other things in the NFL, and because passing is for the most part better than running.
In fact, running is mostly only useful to establish the play action. See for example this FO guest article - teams with competent play-action QBs should run/pass in a 50:50 ratio, but teams with less competent play action QBs should theoretically pass 100% of the time. The wildcat takes this away - it means you're basically running a "run" offense. Okay if you don't have a capable quarterback, I suppose.
Why do colleges do this? Because there aren't very many truly good QBs - in the past, in fact, it was extremely rare to find QBs who can throw effectively downfield; think Nebraska in the 1980s-1990s. There are so many colleges, but so few good QBs, that most colleges had to have a run-focused offense. Nowadays that's much less true as you have more training programs aiming to produce QBs in younger kids.
The NFL does have its own version of this, though: it's the Run-Pass Option! That's basically the same as the Wildcat, where you have two or more capable runners - but one of them is also a very skilled passer (the QB). Patrick Mahomes is perhaps the best current example of this, but it's been around in various forms for a while; Randall Cunningham didn't play in an era where RPO existed, but he certainly ran plenty.