When Waluigi was first introduced, I did think it was rather silly but thinking about it later, it made sense to me that a mirror version of Mario (Wario) should have a brother also. Personally, I firmly in the Waluigi camp and try to use him as much as possible in any games he is in as long as it isn't a big handicap.
When I play with my friends, we all know Waluigi is my guy. Believe it or not, it is the voice that sells me on the character same reason I dislike Luigi. Waluigi has a dishonest sound to his voice. He reminds me of a cliche 1930's gangster - "Wahhh. The coppers got nothin' on me, see? I gave them the ring around the rosie and flatfooted it to Chicago. Waaaaah."
I love his "You Lousy" taunt in Mario Party games. So perfect to use when another player has just acquired a star and the shimmering sound effects have just ended. I love to use that brief silent to voice my displeasure at the current turn of events with a "You Lousy" being all you can here.
Camelot seems to have the most fun with him. In their opening videos for their Mario Sports games, Waluigi always has gets the best gags. The Toadstool Tour opening video where he has an easy putt and keeps missing and his reaction ("Wah, I hate this game!") is a true to life experience we can all relate to. In the Mario Party games, Hudson seemed to catch on to this and gave him some great showboating poses for when he wins his Mini-game battles. He also seems to be the most upset at losing than any other competitor. I love that competative spirit he displays.
As someone else posted, he has a smarmy quality to him. I think it's the mustache. You can easily get a bumbling Inspector Closeau vibe with him as well as that Snidely Whiplash feel of villainy. Waluigi looks like the kind of guy that would sell out his mother to save himself. The kind of guy that the Police threaten in a back alley to get info from and then, after getting the info, they throw him in the slammer anyways because they just don't like him. A small-town with much grander aspirations he cannot fill no matter how much he tries.
Plus, he was the villain for Dance Dance Revolution: Mario Mix's story. So, he has some legitimacy to being included regularly in with the other Mario Cast.