Faster At Excel

  • Home
  • Book: Excel Shortcut Bible

  • Shortcuts
  • Blog
  • Archives
  • Links
  • My Account
  • Login
  • About
  • Contact

Basic Excel Macros: Part 3 Of 3, Personal.xlsb & Adding A Macro To Your Quick Access Toolbar

October 25, 2016 by Terp Leave a Comment

(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.

save-excel-macro

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

Filed Under: Excel Macros Tagged With: Excel macros

Basic Excel Macros: Part 2 Of 3, Editing & Deleting Excel Macros

October 18, 2016 by Terp Leave a Comment

(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:

unhide-personal-xlsb

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

unhide-personal-xlsb-2

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

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

Filed Under: Excel Macros Tagged With: Excel macros

Basic Excel Macros: Part 1 Of 3, Creating & Saving Excel Macros

October 11, 2016 by Terp Leave a Comment

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

Filed Under: Excel Macros Tagged With: Excel macros

Post Categories

Tags

Alt Shortcuts Books challenges Charts comments cut copy paste data Double Click Excel Formulas Excel Keyboards Excel Keyboard Shortcuts Excel macros Excel Mouse Shortcuts Excel Shortcuts Excel Speed Tricks F-Key Shortcuts Filter Find & Replace Font format Format Cells Format Painter Formatting freeze panes Hybrid Shortcuts Menu Key Open Paste Formats Paste Special Paste Values Products Quick Access Toolbar - QAT save Selection and Navigation Sort

Recent Posts

  • 3 Ways To Convert Text To Numbers In Excel
  • 5 Quick Data Set Up Excel Shortcuts
  • Excel Shortcut Bible: Comprehensive Excel Shortcut Book
  • 7 Easy Excel Speed Tips
  • Basic Excel Macros: Part 3 Of 3, Personal.xlsb & Adding A Macro To Your Quick Access Toolbar

© 2016 Faster At Excel