[This is a much-reduced version of a longer, incomplete answer which I decided was not appropriate for SE.]
There are no cases where you must use a macro. Indeed, there are no cases where you must use a programming language at all: if you are happy to learn the order code for the machine you are using and competent with a keypunch then you can program that way.
Most of us are not happy doing that: we like to use programming languages. These have two obvious benefits and one less-obvious but far more important one. The two obvious benefits:
- programming languages make programming easier;
- programming languages make programs portable across machines.
The more important reason is that building languages is an enormously successful approach to problem solving for human beings. It's so successful that we do it all the time, without even thinking we are doing it. Every time we invent some new term for something we are in fact inventing a language; every time a mathematician invents some new bit of notation they are inventing a language. People like to sneer at these languages by calling them 'jargon', 'slang' or 'dialect' but, famously: a shprakh iz a dialekt mit an armey un flot (translated: a language is a dialect with an army and navy).
The same thing is true for programming languages as is true for natural languages, except that programming languages are designed to communicate both with other humans and with a machine, and the machine requires very precise instructions. This means that it can be rather hard to build programming languages, so people tend to stick with the languages they know.
Except that they don't: the approach of building a language to describe some problem is so powerful that people in fact do this anyway. But they don't know that they are doing it and they don't have the tools to do it so what they end up with tends to be a hideous monster stitched together from pieces of other things with the robustness and readability of custard. We've all dealt with such things. A common characteristic is 'language in a string' where one language appears within strings of another language, with constructs of this inner language being put together by string operations in the outer language. If you are really lucky this will go several levels deep (I have seen three).
These things are abominations, but they are still the best way of dealing with large problem areas. Well, they are the best way if you live in a world where constructing a new programming language is so hard that only special clever people can do it
But it's hard only because if your only tool is C then everything looks like a PDP-11. If instead we used a tool which made the incremental construction of programming languages easy by allowing them to be defined in terms of simpler versions of themselves in a lightweight way, then we could just construct whole families of programming languages in which to talk about various problems, each of which would simply be a point in the space of possible languages. And anyone could do this: it would be a little bit harder than just writing functions, because working out grammar rules is a little bit harder than thinking up new words, but it would not be a lot harder.
And that's what macros do: they let you define programming languages to talk about a particular problem area in a way which is extremely lightweight. One such language is Common Lisp, but it's just one starting point in the space of Lisp-family languages: a point from which you can build the language you actually want (and people, of course, will belittle these languages by calling them 'dialects': well, a programming language is only a dialect with a standards committee).
Functions let you add to the vocabulary of the language you are building. Macros let you add to the grammar of the language. Between them they let you define a new language in which to talk about the problem area you are interested in. And doing that is the whole point of programming in Lisp: Lisp is about building languages to talk about problem areas.