Most people use 3D programs cos freehand animation takes too long. You can use existing units and alter each individual frame by cutting and pasting replacement arms and legs etc. from other units. The horse archer available on these forums is an example of this.
Whatever program you use, you will need Flickster (also available from eithe here on civfanatics or at apolyton), This program is the best cristmas present ever for civ designers

cos it allows you to cut the flc files used by civ III up in to PCX files that can be edited in paintshop pro or adobe photos shop etc.
From that point it is just a case of frame by frame animation.
Personaly I use a realy lame 3D program called Corel 3 Dream, that comes free with corel draw 8. I just make a model and render it (virtual photogaphy) from 8 directions 0 degrees(SE), 45 degrees(E), 90 degrees(NE), 135 degrees(N), 180 degrees(NW),
-45 degrees(S), -90 degrees(SW), and -135 degrees (W).
I render them at a high resolution and then resample them to about 8% in corel photopaint (not the best program for this cos it messes with the colours and makes it difficult to do the shadows (though it does allow semi transparent objects, so you can make smoke in front of the unit.