(This is Part 3 of a 3-part series on Basic Excel Macros. Part 1 is Creating & Saving Excel Macros. Part 2 is Editing & Deleting Excel Macros).

 

Personal.xlsb Items — Basic Excel Macros

At some point you may find that PERSONAL.XLSB for some reason isn’t opening automatically when Excel starts.  To solve this, first go to File and click on Options (or hit Alt+F+T), then select Add-Ins (or Alt-F-T-A), which opens the dialog box below.  Once in this dialog box, got to “Manage” on the bottom, select “Disabled Items” and click “Go.”  PERSONAL.XLSB is probably listed there – and if so, Enable it.

If you want to create a macro only for one specific workbook, you need to store it in “This Workbook” instead of “Personal Macro Workbook” (PERSONAL.XLSB) by selecting the appropriate item in the drop-down below: [Read more…]

(This is Part 2 of a 3-part series on Basic Excel Macros. Part 1 is Creating & Saving Excel Macros.  Part 3 will cove some issues with Personal.xlsb and how to add a macro to your Quick Access Toolbar.)

How To Edit & Delete Basic Excel Macros

If you want to edit or delete the macro, you first need to Unhide PERSONAL.XLSB by clicking on the item below in the View tab:

Then make sure PERSONAL is selected in the window below, and hit OK:

Once you hit OK, you’ll be in the PERSONAL.XLSB workbook.

Then click on the Macros icon below: [Read more…]

What Is An Excel Macro?

Macros are pieces of code written in VBA (or Visual Basic) usually designed to automate repetitive tasks.

First, A Warning About Excel Macros

At one point I built macros for things like my number formatting, and assigned a convenient keyboard shortcut.   I also did the same for some basic data cleanup and formatting for reports coming out of the General Ledger.  I thought I was brilliant. I was sadly mistaken as Excel macros (for me) have a major flaw: you lose the ability to Undo (on all open workbooks).  You can imagine how I felt, having 10 workbooks open, in the middle of experimenting with several of them, when I realized I couldn’t undo the experiments and destructive mistakes I had just made.  Sure, I had saved a few minutes by formatting my new file with the macro, and it sure looked pretty.  But I was in deep trouble with my other files.  I couldn’t get back to a working version of those files.  It’s because of stories like this that I am very hesitant to embrace macros. It would be great if Microsoft were to fix this limitation.

I do make one exception.  If I have zero workbooks open (or I’m not concerned about Undoing anything), and then I export a big data sheet into Excel, then I have a special macro that saves me time.  I use Ctrl+Q to add data filters and then bold and freeze the top pane.  This is how I like my data formatted and set up.  In this scenario I’m not risking anything – no other open workbooks, and nothing that I would want to undo in my open data sheet.

You may decide to use macros more often than me.  They can certainly save you a lot of time.  And they can be used for tasks that are far more complex than my simple example below.  Just be fully aware of the risk you are taking — and take the necessary precautions (for example, always check your file before you run your macro).

How To Create a Basic Excel Macro

How do you create a macro?  Pretty easy.   In the Developers tab, click on the “Record Macro” icon that you see below: [Read more…]