Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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:

OK Dialog which reads: "Cannot edit a macro on a hidden workbook.  Unhide the workbook using the Unhide command." and a macro window which reads: "Macro name: PERSONAL.XLSB!SplitSingleNameFieldIntoMultiColumn"

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

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
What's an .xla file? (that must be one of those new ones they've added since I quit messing with MS Office about 5 years ago...) –  leeand00 Aug 3 '12 at 20:15
1  
No, XLA files have been around for a long time but most people will never use them directly. They are add-in's rather than spreadsheets. As such they are really library files. –  Julian Knight Aug 3 '12 at 20:57
    
The main problem with using XLA's for me at least is that they are not usable on locked-down machines whereas macro's generally are. –  Julian Knight Aug 3 '12 at 20:58

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.