Never the best thread to post on an Audi forum hah I had a 330d before I went for my A4 and to be fair it never missed a beat. Was one of the best cars I ever owned and cost me only £750 in none-service repairs for the entire 180,000 miles I owned it. Unfortunately I live on the outskirts of the Peak District so when it snows, it snows, A LOT! Although only 10 days a year it was the biggest pain in the rear and a lot if hassle. Having moved to a Quattro A4 it'd be hard to go back. I did contemplate switching the OH's A6 Avant in for a 5 Series Touring until it snowed a few weeks ago...in the 5 I'd have never of got out - in the A4 I drove up the hill no probs. If you're buying it for a weekend car then go for it in my opinion if you've got the money to play with - proper M cars will always have a place in any petrolhead. If you're buying for an investment I wouldn't be so sure..it's only the likes of the E46 CSL which hold their money - a bog standard one will lose money like any other car.
I had an E46 M3. Nice cars. Be wary of subframe issues, the power they produced caused them to form tears in the subframe. BMW were repairing them under a goodwill gesture if unmodified and under 10 years old. As for investment, I doubt it, the are not the same as the E30 which was iconic. The CSL on the other hand is an investment but their prices reflect this.
E46 M3 is a super bit of kit. Love driving my brothers as it's so much fun. Only criticism is it's a bit too refined and very easy to stray into licence losing territory. If it breaks it can be expensive. Having to pull the motor on my brothers after it threw a rod, which is almost unheard of.
I wouldn't call it an investment either. Just think of it as your w/e toy, keep it for years and the depreciation won't hurt so much.