This is about achieving a mood in a game (or in a movie or book or whatever).
A basic rule of aesthetics is getting one desirable emotional effect generally means letting go of another desirable emotional effect. Like: the epic mood and the zany mood don't usually peacefully coexist--Kermit the Frog is good and Achilles is good but having Kermit show up in the middle of the Iliad would fuck the Iliad up and make it something else. And we're all familiar with the great "Should I allow my player to name his/her PC Jimbob the elf?" question.
Now what we're specifically gonna deal with here is: the moods that are especially accessible by using the fantastic--the nonreal. That is: magical or sci-fi or otherwise surreal elements.
Here's a scale:
So there are 4 categories of mood here lined up from least to most serious.
The basic idea is: during any one specific moment in a work you can get any 2 adjacent emotional effects simultaneously fairly easily and if you're real good you can get 3 adjacent effects in a specific moment. You can't get nonadjacent effects without the one in between. So: no horrific-funny without also weird and no heroic-weird without horrific. You can also loop around and get heroic-funny (generally in the form of "we-like-the-hero-and-root-for-him/her-because-s/he's-funny"--a swashbuckley mood) but heroic-funny-weird and funny-heroic-horrific are stretching it and are pretty hard to pull off all at once. Again, we're talking emotions/moods achievable in a specific moment--like Hellboy comics are sometimes horrific-heroic and sometimes heroic-funny, but no single moment is heroic-funny-horrific all at once because the emotions involved somewhat contradict each other.
In a long work you can get all of them, but it's a challenge. In an actual session of a game you can get all of them comparatively easily (you have hours to fuck around and drift through moods)--but in a game product effectively articulating even one dominant mood is hard--what with the rules and details mundanifying your every step--much less 2 or 3.
The reason for this is because all these moods require a different point of view toward what's going on. "Funny" requires a degree of detachment (or hostility) toward whatever its object is, "heroic" requires investment in its success--attachment to the object (note also that "heroic" doesn't just mean "containing a hero" it means authentically feeling that blood-pumping I-hope-this-guy-wins-and-that-guy-loses emotion), "weird" requires a level of confusion about judgment, and "horrific" requires deciding something's threatening.
In the example I used several ways at looking at the fantastic idea of "part-man/part-bat".
So way over on the left we have the image that pops into our heads when someone makes the familiar observation that "Batman is a guy who goes around dressed like a bat". Like any fantastic conjunction, the idea can be made to sound silly. We are all well aware that anything in D&D can be made to sound goofy if you just say it right: "Elves are like people but with pointy ears and better". The fantastic is incongruity and incongruity can always be made funny.
Now in the middle is simply "weird". Simply weird is hard to achieve, because it means it's genuinely in the middle: an incongruous element which is neither frightening nor funny or is both frightening and funny. The hard part is that different people will see the thing presented as "weird" in different ways. Like that middle picture of the bat with the horn-ears might scare some children but might strike some adults as hilarious and strike others as just, simply, "weird". Weird in its pure form represents a collapse of moral judgment altogether--if we are sure we can just laugh (funny), we know it's harmless, if we are sure we should just run (horrific), we know it's harmful.
The vast majority of things in the world of entertainment which are described as "weird" end up sliding off toward also being "funny" or "horrific". When they do neither it's impressive (classic Surrealism occasionally achieves this) and when they do both it's impressive. For a master class on the emotional multivalency of "weird" see the films of David Lynch--though which parts are funny and which are scary and which balance perfectly being neither but still weird will vary from viewer to viewer.
It's an aesthetic disaster when something intended to funny-weird just comes out scary-weird (see: clowns and small children) or vice versa (see: low-budget horror movies or the Fiend Folio). The important point is just as how--when considered from a historical point of view--"weird" represents a fulcrum between fantasy and sci-fi--when considered from a mood point of view "weird" represents a fulcrum between "so out-of-place-it's-funny" and "so-out-of-place-it's-scary".
Now we get over into horror, and the horrific is not funny. Mating a human with a bat is definitely a scary idea. (And most incongruities can be made scary--maybe even rabbits with unicorn horns). Horror movies can be funny sometimes, but the moment of horror--if that emotion is to be maximized--cannot be at all funny in that moment. If you are going full-on for as-scary-as-scary-can-be, then levity has to be banished. The funniness (on reflection) of the talking rabbit-head in Donnie Darko or the guy in the bear suit in The Shining can't be in our minds during their scenes or else the effect is ruined--we need to be so emotionally close that the detachment necessary to see how essentially funny these things are is short-circuited. A good sign you are really scaring people is that all jokes made fall flat and seem weak. Black humor can work with horror, with weirdness as the translator, creating a vertiginous, giddy, creepy funny that probably won't send you running from the theater, but might give you nightmares--see: Eraserhead. This is more the realm of the disturbing than the shocking.
Now at the far end of the seriousness scale is the heroic. A man with characteristics of a bat is more than a human, is able to achieve what other humans can't. The heroic mode is even more serious than the horrific because, basically, the humorous undercuts and limits the heroic to a huge degree. The jokes have to be very specific: If the jokes are at the expense of the hero then the hero is no longer sympathetic, if the jokes are on the villain then it no longer seems like a credible obstacle for the hero and if the jokes are on the world then it risks seeming like a place not important enough to save. As said above, almost the only way to effectively have heroic-humorous is if it's the hero telling the jokes, which puts you in a pretty specific genre quickly (Spider-Man/Pirates of Caribbean/Ghostbusters territory--"lite") which makes it hard to get to the weird.
If a heroic story tries to go the long way around and tries to meet the weird by going through funny either the weirdness or heroicness has to be sacrificed. Like the timewarp Bill Murray faces in Groundhog Day never really feels emotionally "weird" and the adventures of Barbarella never seem emotionally "heroic" (you might like her but you don't really root for her). Either the weird part makes you more interested in seeing crazy stuff happen (regardless of what it means) and so not become invested in the heroism or the heroic part makes you more invested in judging the rightness and wrongness of things through the haze of humor to feel the eerie burn of weirdness.
Agent Cooper and Pee Wee Herman both achieved a certain level of funny-heroic-weird--if we like them and want them to win, it's because they're charmingly funny and weird. And when we experience this charm, we are unafraid--when Agent Cooper is credibly threatened by horror, he narrows down to simply heroic. And when people say Pee Wee was scary--well, they weren't talking about horror movie scary.
Also, the heroic can't blend with the "simply weird" without going through the horrific. Why? Because the heroic requires moral certitude--a hero (as opposed to a mere protagonist) in this sense is someone we can get behind and what they're doing is something we believe in doing and most importantly--the emotional "high" of seeing heroism (rather than simply knowing a character is the "hero" and going along with it because it's in front of us and we want to see some action) requires the audience to share this moral certitude. We cannot thrill to the slaying of The Emperor if we like the emperor. The aesthetic effect of the "simply weird" is, on the other hand, achieved by it purposefully not telling us what to think. Horror is the translator. We can only root for Dr Strange when he faces Shuma Gorath if his weirdness is frightening. If it's not, we'll just sort of be baffled by the situation and not emotionally sure enough to want Strange to defeat him.
When totally achieved the feeling of weird-horrific-heroic is (and should be) fundamentally emotionally unsettling. We are rooting for the hero to destroy something s/he and we do not totally understand. When Jeffrey defeats Frank in Blue Velvet it doesn't feel exactly right--it's like he's destroying part of himself, when Elric kills a freakish monster we aren't totally sure he has any more right to live than it does, when Lovecraft's protagonists escape the terrors in his stories we kind of don't care--the terrors are more interesting than the characters.
Which is all to say the certainty of the truly heroic and the uncertainty of the truly weird coexist as uneasily as the attachment required for the heroic and the detachment required for the humorous.
A good example of how this all works together in games is the mythos of the original Warhammer: It hung out largely in horror-heroic, but could migrate over to horror-weird (Realms of Chaos) and over to funny (orks--and the general black humor throughout) but funny-heroic just couldn't authentically happen. When the orks fought the space marines, either you have to feel like the space marines are the butt of the joke (no longer convincingly heroic--emotionally speaking) or the orks are just one more inhuman abomination (the Space Marines not getting any of their jokes). Another example: sometimes the Joker is funny--but as soon as Batman comes around, he isn't any more (though he is often, in these moments, weird and horrific) and if he is, then Batman suddenly looks impotent. You can have all these "notes" but not at the same time, and not without considerable effort.
Which is all to say: Morality and mood are tied together. Certain effects become almost impossible in the presence of others and so, as always, you have to realize that people who want one are going to have to make less of a big deal of the other. Deal.