(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…]