I tend to use modulation quite carefully and deliberately in my writing.
If you have a captive audience in a film, TV, or game – or song, even – I believe ample time exists to establish tonality and use modulation as another dramatic tool. It opens a whole dimension of possibilities. I don't think listeners are desensitized to it at all.
Here are a couple of my favourite examples of using modulation within a melody to make things more exciting. These are simple transcriptions with chords marked at the top (with key signatures and double bars to emphasize the modulation).
In Goldsmith's STAR TREK TMP, at the end of the "A" section (bars 16 & 17 of this transcription), we modulate from Bb
Mixolydian to D Mixolydian with the chords F (V of Bb) to D. The melody bridges across with the common tone of the two chords, A. The bass also descends to D with an Eb passing tone.
Then, for the second variation of the "B" theme (bars 24 & 25), he modulates from D Mixolydian to Eb
Lydian with the chords C (VII) to Eb. Again, the melody carries the common tone, G, across this modulation.
These are sudden, exciting modulations that use
chromatic mediant relationships:
https://postimg.org/image/5j3arod37/
In James Horner's KRULL, something very similar happens. Again, the common tone carries across a sudden, chromatic mediant modulation in bars 6 & 7. In this case, the common tone E, carries across the chords E (I) & C, where C is now V of the new key (F major). Then, in bars 8 & 9, we modulate from F to D using the chords F & A (where A is again V of the new key):
https://postimg.org/image/ttrakdw0l/