In a tablet they would be more handy than ever because typing is more difficult than with a full-fledged keyboard.
What caused me to be less enthusiastic about this idea is that it was said to be primarily needed for typing in an xterm. I don't want it for typing in an xterm; I want it for typing in Notecase or other cool programs that we already have that could clearly be enhanced by macros.
There is, for example, an excellent web-based mind-mapping program called Mindmeister. It works pretty well on my N800, but a few key-hits are not available because they involve Ctrl keys or other keys the N800 doesn't have. So, I rarely use it on my N800. Macros would probably transform the Mindmeister experience.