As many people have pointed out, macros do not offer the same range of functionality that VBA does. It's kind of interesting that some people even think that Macros were some sort of pre-VBA functionality that Access had. This is actually false - both macros and VBA were introduced in the same Access version (do you know which?).

Macros were thought as kind of a more structured, entry-level way to code in Access, while VBA had the steeper learning curve but had the full range of functionality on its hands.

So, why should you use macros?

I'm not even going to make the cheesy comment that anyone that ever used the DoCmd functions (e.g. DoCmd.OpenForm) were really using macro actions. Because, you know, that's actually not 100% true: the DoCmd commands were exposed at the same time to macros and VBA - they are different entry points to the same infrastructure.

I think the answer lies in the corollary: Don't Write Code Unless You Absolutely Must. And that is coming from someone that writes code pretty much all day long.

I believe that there is a use for macros (besides the obvious cases where you don't have an option, such as the AutoExec macro), and that use may very well be when you need to do something very simple (programmatically speaking, e.g. open a form) but you don't want to have the IT police freak out when they see VBA code.

(Even further, as Erik hinted here, in Access 12 macros gain some interesting functionality and can be used in a sandbox mode that doesn't require signing. And that's just one new thing - there are plenty more in the macro world.)