Macro or extreme telephoto - basically any time you need to use a tripod - it's best to use MLU.
With my 20D, I would occasionally worry myself when I forgot to turn it off after a macro shoot... because next time I used my camera days later I'd go to take a shot and everything would just go black and it would take me a few seconds to work out what I'd done wrong.
I'm hoping I won't have the problem quite so bad on my 40D though, cos when I've had some more play time with it I'm going to set one of the custom modes to use MLU so it's easier to remember to turn it off!
Another reason to use it on some Canon DSLRs (e.g. 400D) for such shots is that it'll automatically reduce the self-timer to 2secs (instead of 10secs) which makes the timer a less boring alternative to buying a remote shutter release.