rarely i’ve found a situation where i actually need a macro. when my teammates used them, they always ran into issues time later.
is not because macros are bad, but because sometimes one might confuse their use.
i don’t think i’ll manage to succinctly explain what i know about them but:
in cpp macros are usually used to generate code in a generic way. just snippets. on a higher level than the actual language. and so they are not constrained by things like “type”.
in bp they work a bit similar in that you can “reuse” chunks of code. it’s more like a copy/paste than a function. the code is executed locally. if you can use a function instead, i’d rather do that.
if it has to be a “static global function” you can use a bp function library.
irrelevant thought:
i think the for loop uses it because it is type agnostic, and have multiple execution pins. but i don’t understand why epic decided to implement it in than fashion instead of a k2node, specially since a for loop is such a basic part of a language and it’s super related to performance. 
a caveat is that a for loop will evaluate the input on every loop, so if you use a pure function (which is very likely) that function gets evaluated on each loop, so you might want to cache the value first (afaik).
as for a metaphor?
macros (at least in cpp) behave like a Find/Replace.
it will find all the occurences of the macro, and then paste the code in between, before the compiler compiles it (afaik).
unfortunately ue’s docs is lacking on explaining how they work and why.
luckly there are nice ppl on the forums What's the difference between Blueprint Macros and Blueprint Functions? - #10 by MulleDK19