You can use Python to script Geoprocessing. With 10, you can also get access to mxd files, the contained layers etc.
What you cannot do: Build user interfaces. Instead VBA, you should do this with VB.NET or C# (which is a matter of taste between both programming languages). May be you want to have a look at the new Add-In Development possibillities when it comes to user interfaces.
Best regards, Stefan
