Could you please give me some examples on how to achieve it? I would like to be able to maintain just one global macro so that any changes to it are immediately available to all workbooks referencing it.
I don't think you can use the personal.xls for general consumption. YOu will have to use an addin (not too sure about this). If you have the addin on a network, then each user will have to enable it in the tools, addins. To reference things from a macro, go tools, references and bring in the addin. It can then be a normal call.