I've done La Vallée Blanche on snorkel rather than SCUBA, using Moorea Moana Tours (technically one of the owner's special "Ladyshark Expeditions"). Pretty much a lock (at least in September) to see blacktip reef sharks up at the surface with gray reef sharks further down. Might get a couple sicklefin lemons on the bottom; unlike their Atlantic cousins they're pretty standoffish. We went over from Moorea twice. The first time we had at least four tigers that mostly kept to a distance (although one did swim under the boat right as I was going up the ladder); the second time we had an absolute firecracker of a 14-footer that "came in like a wrecking ball."
Moorea might be a worthwhile stop as it's only a 30-minute ferry ride from Tahiti; if you're going at this time of year the humpback whale season is on. That's definitely snorkel only (not sure if there are any regular dive sites you might spot them at; the bottom drops off fast around the island but we did get them inshore a few times). You might also spot pilot whales, dolphins (rough-tooth and spinner), and possibly oceanic whitetips (which will keep you on your toes for sure). Inside the lagoon on the northwest side of the island there's a sandbar where the stingrays get fed; doing it in the morning is relatively quiet (I've never been to Stingray City at Grand Cayman, but judging from pics and videos I've seen Moorea isn't as packed with tourists).