I was in this position several years ago. My family wanted to know why I was taking on a major task without extra pay, but I felt I didn't have any choice if I wanted to keep my job. I completed the project and didn't hear anything beyond a "Yeah, OK, it works" from management. Six months later, feeling pretty low, I got called down to the big shot's office where they gave me kudos and a modest bonus. Just being recognized made me feel so much better.
Now, I'm constantly sought to help with automation. And I hear "I didn't know Excel could do that" often. (Haven't we all?) But co-workers now call me before they even start on their tasks, to see if I have a better/more efficient idea. Knowing that I am valued not only by management but by my co-workers means the world to me. I know that if my situation changes and I have to find other employment, I've built a knowledge base that they never will be able to take away from me.
I've suggested to management that it's dangerous to have only one person here who understands VBA. (While it's nice to have them NEED me, it's a double-edged sword when I want time off.) They don't see a problem, though, and are perfectly content with all the knowledge being stored in my head. So I'll be their one and only in-house VBA guru, and they'll have to put up with the big head I've developed.