In Excel 2007 where do you store macros that you want to use on all sheets that you open?

Question

It looks like you can store it in a file called PERSONAL.XLSB and that will create "Global" (for all worksheets) macros.

This file is of course stored in your user profile (%USERPROFILE%\AppData\Roaming\Microsoft\Excel\XLSTART).

My file appears to be missing however. This is probably because my user profile is remoted to a network drive.

When I try to edit or create a global macro, I get a message about:

I think it might be because my PERSONAL.XLSB file is missing but I don't really know what to do about it.

Answer 1

I'm not sure if this is a "best practice" or not, but I created an Excel add-in (.xla file type) that has all of my custom formulas (creatively named "CustomFunctions.xla). I placed this in a mapped network drive and Excel connects to it each time it opens. I would guess something similar would work for you.

EDIT: Here's a great resource I use all the time: CPearson Excel Add-In

EDIT: From Chip Pearson's website:

"An XLA Add-In is a certain type of workbook that provides custom functions and/or tools that extend the basic functionality of Excel. An Add-In may contain User Defined Functions (UDFs, see Writing Your Own Functions In VBA) that provide calculation functions specific to your own area of interest or business, functions that extend beyond the normal calculation capability of Excel. An Add-In can also provide tools to manipulate the data in a workbook. Indeed, an Add-In can be written to do nearly anything you want. If it can be done manually, it can be automated with an XLA Add-In."

He has a lot more to say about the topic. His website is absolutely worth bookmarking if you're going to do much in Excel.

Answer 2

You are correct about the file. personal.xlsb in the startup folder is the Excel equivalent (roughly) of normal.dot in Word. You can store macro's and static data, even colour pallets.

Check in Options as to where Excel is expecting the startup files to be, they may have been moved.

Also, go to the View toolbar and see if "Unhide" is grey'd out. If it isn't, click on it and unhide the personal.xlsb workbook, you will then be able to edit the macro.