I completely get it that by some standards of judgment the .40 is an unnecessary round, but the market says there is a demand for it. Maybe the controlling factor in answering the question is the frame of reference, as the round is either a bigger-than-necessary alternative to the 9mm or a smaller-than-necessary alternative to the .45. I actually like the idea of the .40 and have one (a 4006). I will soon be taking possession of a match-grade 9mm (a 952). I suspect that at competition distances the 9mm will perform better for me, but in a social situation in my small house with compact rooms, I don't imagine target reacquisition would be much of a problem with either gun. I would use either one.
If I had a 9mm already, I might not see the point in upgrading to a .40; and I think that for home defense (particularly in small-lot California), a .45 or 10mm might be overkill. (I'm not completely consistent in this, as one of my HD resources is a 1917 Army loaded up with a moon clip of standard 230 gr ball ammo.) If you like bigger holes and an energy edge, go with the .40; if you appreciate less expensive ammo and think that a little excess capacity makes no real difference, then the 9mm should be your choice. It has had nearly a century of use, and it's not like experience has demonstrated that it is an inadequate round.