I think it''s a fine way to implement middle to high quality music in a game. You will hear heated debates wherein people will tell you there is no purpose behind MODs any more as MIDI''s flexibility increases, but at that point it really comes down to taste and the ability of your composer.
The various MOD formats present you with a nice way to deal with special triggers and such, by writing in your own effects to the file, etc, and most of the middleware sound engines fully support MOD formats anyway, so it is quite up to you.

Well, a MOD song is as good as you make it. Higher sample quality, more variety of samples(ie, multisampled instruments, as opposed to one sample for your entire guitar track for instance) make for very high quality music when paired with an intimate knowledge of your MOD format and your tracker(utilizing effects and good tracking style.) Of course, the file becomes proportionately large at that point, so at a certain point you may as well export it to .wav and convert to MP3.

An interesting thing, the BASS audio engine(www.un4seen.com) supports an interesting format called .mo3, which allows you to have your samples compressed within your mod file, if I remember correctly.