Best Theme
I must say the Enterprise theme is the most likable to me. I never fast forward through the titles whenever I watch the episodes on video, whereas I did for the other series.
Here's what I think of the others:
TOS: Very short. Preferred the version with the woman singing.
TNG: Very likable, definitely says sci-fi. My favourite version is the Season 3 onwards version, that windy bit leading into the electronic-pipes is really soothing and is the calm before the full fanfare.
DS9: Liked all versions of that. The original was very calm and soothing. The newer, faster one was more action-packed and suited the revamped titles and stories, though when listening to it on CD it does get very loud at the end. As for the special rock version available on the soundtrack, even better, especially the guitar-solo in the middle (which always makes me think of the Defiant).
Voyager: Extremely adventurous and moving. Definitely suited the title sequence and was the only theme tune that stayed the same during the whole run of the series. As for the pop version, again even better. It's very uplifting and again features an unforgettable guitar solo in the middle which always makes me think of the different action sequences in Voyager such as the Voyager flying low through the Vaadwaar city or over the Tactical Borg Cube.
As for the movies, my favourite has to be the First Contact one. This one, in Jerry Goldsmith's words, is very "noble" and is along the same lines as Voyager and fits the theme of first contact.
The second best has to be the end theme for Generations, it's very stirring as well as being soothing in the middle.
The worst has to be the one for The Voyage Home, it's such a departure it's unbelievable! Like the rest of the soundtrack for that film, it's too farcical and Police Academy!