I am one of those odd people who are struck by these sorts of moods, unfortunately, I am barraged by them so often that I rarely ever finish any of them. The ideas are quite simply huge, grand, and I can describe quite a few of the feelings that accompany them.
Pretty much all of these are computer based, because I grew up on a computer. I learned how to talk from a computer. No, really.
The first one that I felt driven to complete was a mouse motion based command input system, ala Black and White or Opera, around ~4 years ago, but overlays on top of Windows itself. It works, and I finished it, as it was part of a class I had in college. Since then, I and other people have helped develop something like four different styles of gesture based input systems for mice, and while they aren't so uncommon any more, I still enjoy pondering them.
Quite simply, it is an obsession. I would, and still spend many hours per day thinking about various projects that I have. If I did not spend eight hours a day already writing code (also known as working), and if I was not so enamoured by gaming, I would exploit these 'strange moods' more.
My second one was a game that utilized this gesture system, called Duel. It was composed of two wizards facing off in what I would consider a wizard's duel. It utilizes the gesture system to cast spells using multiple different types of mana, and multiple different symbols for attacking, defending, etc. For instance, one mage may draw an inverted T using red (aggressive) mana, and it launches a fireball. The other mage would counter using a ice shield (a blue O). That ice shield could be broken with a lightning bolt, but if the mage grounded herself, would cause no damage. Etc, etc.
By and large, I have found that it is somewhat easy to transmit these strange moods to other people, and get them as excited about something as you are. Though some people are quite simply uninterested in them. They seem to lack, or have a much smaller 'builder's tick'.
Actually performing the action is a different matter. The motivation for one of these obsessions has to be triggered, usually through just sitting down and doing it for an hour, or thinking about it for at least an hour. Distractions are still possible, I think those can be heeded or dismissed based on the person's discipline, and hense isn't applicable to these moods. Once the motivation has been triggered, the obsession fed a little, it just grows and grows until 12 hours later, you realise that you need to sleep.
The third one, codenamed Kolasi, is something I still can't talk much about, but it combines a lot of these odd one-off ideas. It is positively huge though, and I am not disciplined enough yet to execute it. So far it has been going mostly nowhere for four years. Doesn't stop me from wanting it though.
In my experience, these grand projects have to be tempered with the learning of skills and discipline, otherwise they can and will fall by the wayside, and simply become a lost obsession, which sucks, because the desire never goes away, but the motivation is never there.
The other part that is dangerous is the scope and vision of these projects. If you can have singular responsibility over it, things become MUCH easier.
Quite simply, I would love for other people to be able to help me with these projects, but as any designer knows, one of the hardest things is getting other people's vision to align with your own. Its often easier to just create it one's self. I think that is why many people choose the one person approach. In this day and age though, it is becoming more and more difficult to one-man projects. I am pretty sure none of us would mind having some rockin graphics for DF, but I think the complexity of the game was a result of the sacrifice of graphics.
One thing that may be often misrepresented is exactly how difficult it is to carry out these strange moods. Quite simply, anything can get in the way of them. It is an infatuation, yes, but often times it can't get out of the hobby realm. I applaud the people who have the discipline to complete their strange moods in less than years of time while maintaining a job and whatnot. I feel too many obligations to my flesh and blood friends, body, and whatever silly entertainment that I choose to indulge in to execute too many of these strange moods.
Though one thing is for sure. I do think about going hermit and cranking out masterpieces. I sit here, and I can think of at least 3 more coding projects that I would love to complete.