The Macomb campus is in a lovely suburban location. It is right off a high way (M-59) which can get very busy around rush hour. But the location is nice as it is located between two malls, surrounded by restaurants and shopping centers. Also, there is no housing issue here...there are plenty of apartments/condos to choose from that are very affordable. The building is tucked away from the highway though in a "university" building, so it's pretty quiet. The "university" building has four buildings that other universities run classes through, such as Central, Wayne, etc. MSUCOM has it's own building that is very new-only five years old, so the facilities are very nice. There are ample quiet study spaces with tons of windows. It is surrounded some by forest and there are nature trails you can walk on when you need a break. There is plentiful parking and it is all free.
The Detroit campus is located within the Detroit Medical Center, which is pretty neat. MSUCOM has it's own wing that is very secure. There is 24/7 security right before you enter the area. Also, parking is right next to the entrance, however you have to pay for a pass. The DMC is a little smaller, not as many study spaces or windows. But it is a different atmosphere, as it is in the city, right across the street from Wayne State's Medical School, and in a medical environment. Living in the safe areas of Detroit is fairly expensive, but there are several suburbs about 15-20 minutes away that are safe, affordable and fun.
For non-traditional students or those who have had a big university experience before, I think either of these locations would be great. As a non-traditional student myself, I am choosing one of the two. Only 50 students matriculate at each branch, so there is a more "family" type of feeling for these locations.
I have yet to see the East Lansing campus, but I know there are more students who attend this campus and it is imbedded within the overall MSU community. I know the facilities are a little older, but there is more room to get involved with student government, etc. Also there is the simulation lab there as well. East Lansing is a pretty cool city, but it is very spread out as well.
Overall, it depends on the personal preference you may have when choosing the campus. I definitely suggest checking them out personally and see where you fit best. It may just surprise you.