Return to VBA Code Examples

VBA – Shortcuts for the Visual Basic Editor

This tutorial will show you a variety of really useful shortcuts in VBA

Use Alt + F11 to open the VBE

Usually to get to the Visual Basic Editor, you need to click the Visual Basic button on the Excel ribbon. However, you can Press Alt + F11 instead!

vba shortcuts Alt F11

Quick keys in the VBE

CTRL + R– shows the Project Explorer.

CTRL + G – shows the Immediate Window.

F4 – shows the Properties Window.

F2 – shows the Object Browser.

F5 – runs the Procedure you are in.

F8 – allows you to step through code in Break mode.

Recording a Macro

One of the best ways to learn VBA is to record a macro, and then analyze the code. It is also a very quick way of writing code, but the recorder will record EVERY keystroke, so you may need to edit your code to remove redundant lines.

Lets examine the macro below recorded by the macro recorder, and see how we can make it more efficient.

Sub TestFormat()
'
' TestFormat Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
ActiveCell.FormulaR1C1 = "Applese"
Range("D3").Select
ActiveCell.FormulaR1C1 = "Pears"
Range("E3").Select
ActiveCell.FormulaR1C1 = "Peaches"
Range("C4").Select
ActiveCell.FormulaR1C1 = "12"
Range("D4").Select
ActiveCell.FormulaR1C1 = "14"
Range("E4").Select
ActiveCell.FormulaR1C1 = "16"
Range("C5").Select
ActiveCell.FormulaR1C1 = "20"
Range("D5").Select
ActiveCell.FormulaR1C1 = "25"
Range("E5").Select
ActiveCell.FormulaR1C1 = "26"
Range("C6:E6").Select
Range("E6").Activate
Selection.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThick
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("C4:E6").Select
Selection.NumberFormat = _
"_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
Range("C3:E3").Select
Selection.Font.Bold = True
Range("C3").Select
ActiveCell.FormulaR1C1 = "Apples"
End Sub

Now look at the code below which will achieve the same result

Sub TestFormat()
' Keyboard Shortcut: Ctrl+Shift+T
   Range("C3") = "Apples"
   Range("D3") = "Pears"
   Range("E3") = "Peaches"
   Range("C4") = 12
   Range("D4") = 14
   Range("C4") = 16
   Range("C5") = 20
   Range("D5") = 25
   Range("E5") = "26"
   Range("C6:E6").Select
   Selection.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
   Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
   Selection.Borders(xlEdgeBottom).LineStyle = xlDouble
   Range("C4:E6").Select
   Selection.NumberFormat = _
   "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
   Range("C3:E3").Select
   Selection.Font.Bold = True
End Sub

By cutting out a lot of redundant code, and amending recorded code where possible, we can make the macro much more effective and easy to read.

Using the Personal Macro workbook

If you want a macro to be able to be used within all your Excel files, you can store the macro in the Personal Macro workbook – this workbook is hidden and is open all the time in Excel, making any macros stored in there, global macros.

If you do not see a Personal Macro Workbook in you VBE window, record a macro and specify for it to be stored in the Personal Macro workbook.

vba shortcuts personal macro workbook

Indenting Code

When you indent your code, you immediately make it easier to read and for someone else to follow.  To indent multiple lines of code, you can select them, and press the Tab key.

Similarly, to outdent the code, press Shift+Tab and the code will move to the left again.

vba shortcuts indent multiple no comment

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! automacro

Learn More!!

Commenting Code

Adding comments to your code is another way to make it easy to read and navigate.  You add a comment to the code by putting a single quotation mark at the beginning of the line as shown below.

vba shortcuts indent multiple

Writing in Lower Case

VBA uses Proper Case in code.  If you write in lower case, and it doesn’t change to Proper Case, you can quickly see where you have made a mistake.

vba shortcuts range

 

Using Intellisense

Intellisense pops up when you are writing code and gives you a list of all the Properties, Methods and Events that are available to the object you are working with.  It normally pops up automatically after you type a period when you are typing your code.

vba shortcuts intellisense

You can also force it to show up by pressing Ctl+j.

vba shortcuts force intellisense

Similarly, when you are using functions in Excel that take arguments, these normally pop up automatically.

vba shortcuts function intellisense

You can force them to show up by pressing Ctl+i.

VBA Programming | Code Generator does work for you!

Auto-Complete

You can use the Ctl+Space key combination to use auto-complete when writing code.

vba shortcuts auto complete

In the example above, all the Properties, Methods, Events and Variables beginning with str will appear in the list for you to select from.

Option Explicit and Compiling Code

Making sure you have Option Explicit at the top of all your modules ensures all your variable are declared and prevents you from making spelling mistakes in the variable names.  If you have Option Explicit on, and you compile or run your code, you will get an error if a variable name is not recognized.

vba-shortcuts-compile

 

The Immediate Window and Debugging

The Immediate Window (you can switch it on using Ctl+g) allows you to debug your code and find the value of variables while you are in this debug mode.

vba shortcuts debug print

 

Sub TestVariables
'declare the variable
    Dim strName as string 
'populate the variable
    strName = "Anne Smith"
'use F8 to step through the code and send the  variable value using debug.print to the immediate window
   Debug.Print strName
End sub