Topics Covered in this Tutorial:
- Introduction to VBA
- 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.
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)
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
Run a Macro
Understanding the Interface
Opening the VBA Interface
Developer > Visual Basic
The three windows that we require for our upcoming tutorials are Project Explorer, Code Editor & Immediate Window.
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.
View > Code (F7)
Double Click on an object (Sheet / Module) in the project explorer to open the relevant code window.
View > Immediate Window (Ctrl + G)
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]
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)
Step Into – F8
You can run through the code line by line by pressing F8.
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" 4) Press F5 to run the program from VBA editor 5) Switch Back to Excel to check the values