VBA Tutorial 1: Setup + Interface

Topics Covered in this Tutorial:

  • Introduction to VBA
  • Setup
  • Macros
  • Understanding the VBA Interface

Introduction to VBA

What is Excel VBA?

VBA stands for Visual Basic for Applications. VBA is a combination of Microsoft’s programming language (Visual Basic) with Microsoft Office applications (Excel, Word, Powerpoint, Access). YES, you can use VBA with even Word and Powerpoint.

With VBA you can write your own codes and automate certain processes and calculations.

Should I use Windows or Mac?

VBA works best with Windows. Though Microsoft Excel on Mac supports VBA, you will not be able to access the full range of features available on Windows. You can still follow the upcoming tutorials on Mac. The latter part of this series (Web Scraping) cannot be done on Mac.

Setup

Accessing the Developer Tab

To be able to use VBA in Microsoft Excel, you need to enable the Developer Tab.

For Windows: File > Options > Customize Ribbon > “Check” Developer
(Check gif below for reference)

Tutorial1 01 Setup.gif

Macros

Macros allow you to record your actions and playback later. They are an intermediate solution to automate processes for those who do not know VBA.

Record a Macro

Tutorial1 09 Record Macro

Run a Macro

Tutorial1 10 Run Macro.gif

Understanding the Interface

Opening the VBA Interface
Developer > Visual Basic

Tutorial1 02 VBA Editor.jpg

The three windows that we require for our upcoming tutorials are Project Explorer, Code Editor & Immediate Window.

Project Explorer
View > Project Explorer (Ctrl + R)

A workbook is represented by “VBAProject” in the project explorer. Each project will have both Microsoft Excel Objects and Modules.

  • Microsoft Excel Objects: Created automatically whenever you create new worksheets
  • Modules: Right click on project explorer (any space) > insert > module

A code can be written in both Microsoft Excel Objects & Modules. Code written in Sheets can disappear when Sheets are deleted, moved. Short codes can be put into Workbook & Worksheets. Big codes are generally written in modules and called when necessary in respective Worksheets or Workbooks.

Code Editor
View > Code (F7)

Tutorial1 04 Code.jpg

Double Click on an object (Sheet / Module) in the project explorer to open the relevant code window.

Immediate Window
View > Immediate Window (Ctrl + G)

Tutorial1 05 Immediate Window.jpg

Immediate window is an output platform that lets you test your program code. We will use this window in subsequent tutorials.

TRY THIS OUT! - Using the Immediate Window
?Now [enter]
 Tutorial1 07 Immediate Window Now.gif

Running Codes from VBA Editor

Run – F5
You can run through the entire code by placing your cursor in the code and pressing the play button (shortcut: F5)

Tutorial1 12 F5.jpg

Step Into – F8
You can run through the code line by line by pressing F8.

Tutorial1 13 F8.jpg

TRY THIS OUT! - Editing Macro Codes
1) Go to VBA Editor
2) Open Module1 from the Project Explorer
3) Change values "1", "2"..."5" to "5" ... "2", "1"

Tutorial1 11 Edit Macro.gif
4) Press F5 to run the program from VBA editor
5) Switch Back to Excel to check the values

Next: Tutorial 2 The Basics

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s