Not to be nasty, but depleted uranium is not the hardest substance known. That remains, AFAIK, diamond.
The interesting thing about DU is that it is very dense, just like some noders. This makes it an excellent material for projectiles, because as we know:

Kinetic energy = 1/2 m * v2

Where v is velocity and m is mass. So, if you have a weapons system like a Vulcan machine gun and want to make it more powerful (more precisely: want more kinetic energy to be delivered on the target for every projectile), one way is to make the ammo DU.

An additional interesting property of DU is that it is pyrophoric, which means that small scraps of it burn. Thus when a DU shell enters a tank it ignites everything flammable. Including launch charges for the tank gun.
DU is not very radioactive, because all the radioactive stuff has been taken out of it (otherwise it would not be depleted, would it ?). It is just a material which happens to be a byproduct of uranium enrichment (for fission purposes), and to have desirable mechanical qualities (for a balistic weapon, of course).
A good substitute for DU is tungsten (wolframium), which happens to be more expensive for the US.
The health effects of exposure to DU are not clear yet.