Right now, in the Denver area, the scuba area at the Aurora Reservoir is my favorite. There's a twin engine Cessna at about 28', a few fish here and there, tons of crayfish. Water was 66 deg F last week, with viz ranging from 1' to 7' (and seems to settle down faster than at Chatfield), depth is around 25'-30'. Costs $10 for entry to the Res. and another $10 for the key to the scuba area (if you want to drive your gear to the beach and unload instead of carting it all from the parking lot to the beach).
Chatfield Reservoir has a scuba area, and it's cheaper to get in. Costs $7 (I think) for a State Park day pass, and there's no extra charge to pull right up to the dive area. Scuba area is a pond adjacent to the actual reservoir. A few fish/crayfish, some objects (water heater, toilet, shopping cart, old sailboat on a trailer) to see, depth is about the same as Aurora. However, there are a lot of classes that meet there and a Newfoundland rescue training group that use the area and once the (already limited) viz gets blown out it takes a long time to settle down. Most of my log notes here say something to the effect of, "saw a crayfish on descent and that's the last thing I saw". I've been in the main reservoir to dive but it's colder, darker, and I don't trust the training/knowledge/experience of the boaters (yahoos) running around above me.
Never been to the aquarium to dive, I just can't justify the cost they charge to do it.