Bottom line: Learn 18 tips & shortcuts to save time with writing VBA macros for Excel or other Office applications. Skill level: Intermediate 18 VBA Macro Tips & Shortcuts At the beginning of last year I wrote a popular post on. I decided to continue the tradition this year and write a post on tips & shortcuts that will save you time when writing VBA macros.

These are some of my favorite tips, and also some favorites from the,, and my. If you're not familiar with VBA yet, but always wanted to learn, then checkout my. Obviously there are a TON of shortcuts & tips for VBA. So please leave a comment at the bottom of the post with your favorite tips. This way we can all learn from each other!

🙂 Download the PDF Here is a printable PDF version of the article. (842.9 KB) Page Index • • • • • • • • • • • • • • • • • • #1 – Alt+F11 to Open the VB Editor The VB Editor is the application we use to write macros and create userforms. It can be opened by clicking the Visual Basic button on the Developer tab in Excel. The keyboard shortcut to open the VB Editor in any Windows version of Excel is Alt+ F11. The shortcut in the Mac version is Opt+ F11 or Fn+ Opt+ F11. If you don't see the Developer tab in the Ribbon, click the image to learn how to enable it: The Fn (Function) Keys on Laptops If you are using a laptop keyboard then you might also need to press & hold the Fn key before pressing F11.

The function keys on laptops are typically multi-use keys, and require the Fn key to be pressed to activate the function keys ( F1– F12). Some laptops have a Fn Lock feature that makes the function keys primary, which means you won't have to press the Fn key when pressing F1-F12. Checkout my article on the to learn more. #2 – Store Your Macros in The Personal Macro Workbook The Personal Macro Workbook (PMW) is a great place to store the macros you use often. This workbook opens in the background every time we open Excel, making our macros easily accessible.

We can also create custom toolbars with buttons to run our macros. I like to think of the PMW as our Excel tool belt that can save us time with task we do every day. For example, I have an on a new blank sheet. We can store this macro in our PMW and assign it to a button in the ribbon or keyboard shortcut, and then run it any time on any open workbook.

*Excel 2016 for Mac uses the function keys for common commands, including Copy and Paste. For quick access to these shortcuts, you can change your Apple system preferences so you don't have to press the FN key every time you use a function key shortcut.

Checkout my to learn more. There is also a. #3 – Ctrl+Space to Auto Complete This is definitely one of the keyboard shortcuts I use the most in VBA. When we are typing code, Ctrl+ Space opens the Intellisense drop-down menu that contains a list of matching objects, properties, methods, constants, and variables. To use the Ctrl+ Space shortcut in the VB Editor: • Start typing a line of code like ActiveCell. • After typing the first few letters, press Ctrl+Space • You will see a list of all VBA words that start with Act.

• Press the Up/Down arrows to select the word • Then press Tab or Enter to complete the word. There are two major benefits to this shortcut: • It saves time with having to type long words and variable names. • It prevents typos because VBA is completing the words for you. These benefits can be a HUGE time saver when debugging your code.

#4 – Intellisense for Worksheets We typically also see the Intellisense drop-down menu after typing a period (.) in the VB Editor. However, sometimes it doesn't work. One common case is with the Worksheets property. If we type Worksheets('Sheet1')., we do NOT see the Intellisense menu. This can be frustrating and cause you to think that Intellisense is not working. The reason it doesn't work is because the Worksheets property can contain reference to one or multiple sheets.

Depending on the reference, the properties and methods will be different for each case. It would be great if Intellisense was still intelligent enough to recognize this, but it's just one of those things we have to live with There are two ways to get around it and see Intellisense for worksheets: • Use the CodeName of the worksheet we want to reference. This is my preferred method for referencing worksheets because the code will not break if a user changes a sheet name. • Set the worksheet to a Worksheets object variable first. Then when we type the variable name followed by a dot ( ws.), the Intellisense menu will appear. #5 – Use Comments Liberally We can add comments to our code that help explain what each section of code does.

To create a comment in VBA you type an apostrophe at the beginning of the line. Once you move the text cursor off the line, the text will turn green. The green text makes it easy to distinguish comments when reading code. VBA completely ignores comment lines, and you can add as many as you'd like. Commenting your code is somewhat of a controversial topic. Some developers believe that properly written code should speak for itself, and that you don't need to add extra comments.