I too have wondered what was going through Decker’s mind at that moment. I’ve come up with a couple of explanations I like.
On Delta IV, Decker has a choice. Stay on Delta IV with the woman he loves? Or leave her for his Starfleet career to become a starship commander and a hero? He chooses the latter, but it’s a very difficult decision. It’s so difficult that he later suggests he wouldn’t have been able to do it if he had seen Ilia one more time to say goodbye.
He gets his command and his first mission is a biggie. All of Earth is at stake. This is exactly the kind of thing for which he left Ilia. He lives for it.
Then Kirk comes along, pushes Decker aside, and takes command of the mission.
Throughout the mission, Decker urges caution and restraint, while Kirk channels Farragut: “Damn the torpedos! Full speed ahead!”
By the time they come face to face with the Voyager probe, Decker realizes that Kirk took the right approach for this mission and that’s what has brought them to this point with a chance to succeed. If Decker had been in command of the mission, Earth would have been doomed. Taking his command away saved the planet.
This has all happened very quickly and Decker hasn’t had a chance to integrate the experience and deal with the emotional consequences. At this moment he sees himself as the “bad” captain who would have doomed the planet. He fears that others will see him the same way. Assuming they survive the mission, he can’t see himself commanding a starship with confidence.
There is an alternative. He can adopt Kirk’s “Damn the torpedos” approach (which, ironically, is something that appears to frighten Kirk in this instance). He can out-Kirk Kirk, take a bold leap into the unknown, and be the savior of Earth instead of the captain who would have doomed it. He may also be reunited with Ilia, although he can’t imagine what form this reunion will take.
At this moment, he sees it as a much more attractive option than trying to go on as a starship commander. If he chooses not to do it, he’ll be behaving in the same cautious and restrained manner that he’s feeling so bad about right now. So he goes for it.
It has to be done. If it’s not Decker, then it will have to be Kirk, Spock, or McCoy. He nobly chooses to take the responsibility on himself. His statement, “As much as you wanted the Enterprise, I want this,” is meant to reassure Kirk, and may not be true. As last words go, it’s more poetic than “This scares the hell out of me, but somebody has to do it.”