ECON10151 Lecture 6 Introduction to Visual Basic for Applications (VBA)Matlab

Java Python ECON10151 Lecture 6

Introduction to Visual Basic for Applications (VBA)

Nov 2024

Learning Outcomes

•  Be able to implement custom VBA functions within Excel to perform complex calculations

•  Be able to understand and apply VBA loop structures, allowing them to automate repetitive calculations across datasets

Introduction

This lecture introduces students to the power of Excel VBA as a tool for automating calculations and efficiently handling repetitive tasks in data processing. We will start with creating a custom VBA function, which allows us to perform complex calculations that Excel’s built-in functions might not support directly.  Building on this, we’ll explore how to use VBA loops to extend these functions across multiple rows, automating the calculation for an entire dataset in just a few steps. Writing the code yourself is not a requirement for this course; however, it is more important to understand the code and its meaning.

1 Set Up

The Visual Basic editor is located under the Developer tab. However, the Developer tab is not displayed in the Excel ribbon by default, but you can easily add it to access VBA editor.

•  For Windows Users

1.  Click on the File tab.

2.  Select Options.

3.  In the Excel Options window, choose Customise Ribbon.

4.  In the Customise the Ribbon section, locate the Main Tabs list and check the box next to Developer.

5.  Click OK to apply the changes.

• For Mac Users

1.  Click on Excel in the top menu.

2.  Preferences.

3.  Choose Ribbon & Toolbar.

4.  the Customise the Ribbon section, find the Main Tabs list and check the box next to Developer.

5.  Click Save to confirm the changes.

2 Introduction to Visual Basic for Applications (VBA)

Visual  Basic for Applications  (VBA)  is a programming  language developed by  Microsoft that  is embedded within  Excel and other Office applications.  VBA enables users to automate repetitive tasks, enhance data analysis, and create custom functions and solutions tailored to specificneeds.

Overview of VBA Editor

The V