First off, @Eminem is right and using Explicit (and Strict) will save you headache in the long run. I always use it and I recommend others do too.
But you asked if there were any scenarios where turning them off is useful, and there are. Otherwise, why would the options even exist, right?
If you are working on a small project that you know you're going to throw away at the end (i.e. just testing a concept, or making a one-off converter) then not having to declare variables saves a small amount of time, and who cares if you can't maintain it later, you're throwing it away.
Plus, if you're testing something, you may be changing variable types repeatedly, and having to go back to the top and re-declare every time can be a pain.
And if you're coming into VB from a language that doesn't even have explicit variable declaration, you might want to put off the variable declaration until you're more comfortable with some of the other differences.
But I've found that I never really throw anything away, and I am frequently going back to re-purpose some "throw away" I wrote years ago. The time I save making that easy outweighs (for me at least) the extra effort of declaring (or re-declaring) my variables.
I've also found that variable Type and Scope errors account for a huge fraction of bugs I encounter, anything the IDE can do to help me out there is much appreciated.