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!

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.

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.

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.

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.

 

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.

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

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

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

Auto-Complete

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

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.

 

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.

 

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