Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

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.

• Define the Function:

Function CalculateIncomeTax(income As Double) As Double

This defines a function  named CalculateIncomeTax that takes a single argument  (income) and returns a Double. Double is short for ”double precision floating-point” and is typically used to store numbers that require a large range and/or precision, including decimals.

• Variable Declaration:

Dim Tax As Double

This declares a variable to hold the calculated tax.

• Specify If Statements:

If income <=13000 Then

Tax = 0

ElseIf income > 13000 And income <=50000 Then

Tax = 0.2 * (income - 13000)

Else

Tax = 0.2 * (50000 - 13000) + 0.4 * (income - 50000)

End If

The first If checks if the income is less than or equal to £13,000 and sets the tax to 0. The second ElseIf checks if the income is between £13,000.01 and £50,000 and calculates the tax at 20%. The final Else block calculates the tax for incomes above £50,000 using the respective rates.

• Return Value:

CalculateIncomeTax = tax

This assigns the calculated tax to the function name, which returns the value when the function is called.

• Function Completed:

End Function

This indicates that the definition of the function has been completed. The code that you should type into the module will be displayed below.

4.  Save Your Work

•  Make sure to save your Excel workbook as a Macro-Enabled Workbook (.xlsm format) to preserve the VBA code.

5.  Use the Function in Excel

•  In any cell in your Excel worksheet, you can now use your new function.

For example, if you want to calculate the tax for the first person, you would type the following in cell C2:

=CalculateIncomeTax(B2)

Press Enter, and Excel will return the calculated income tax based on the rules defined in your VBA function.

•  Now, you can apply the function to determine the tax that should be paid by the other individuals in the dataset.

Approach 2: Using Loops for Batch Income Tax Calculation

Alternatively, we can use a  loop in VBA to calculate the income tax for  multiple individuals at once.   This method allows you to process each individual’s income in a single procedure. Follow these steps to implement this approach:

1.  Open VBA editor

•  Go to the Developer tab in the Excel ribbon. Click on Visual Basic to open the VBA editor.

•  Click on Module 1. We will write new code in the section below where we created the function.

2.  Write the Loop Code

To code the procedure, we need to clarify what we want to achieve. We will use the range C2 to display the tax amounts owed by each individual. The first output cell is C2. To calculate the tax for C2, we will use the  CalculateIncomeTax  function that we just created. The input for this function will be the value in cell B2. Excel will pass the value of B2 to the  CalculateIncomeTax  function and return the result in C2.

Next, Excel will move to the following row, using the value in B3 as input for the function and returning the tax amount in C3.  This process will continue for each row until we reach the last individual in the dataset.

Let’s follow these steps to code the procedure:

• Define the Procedure:

Sub CalculateTaxesForAll()

• Define Variables:

Dim i As Integer

This line declares a variable i as an integer, which will be used as a loop counter.

Dim income As Double

This line declares a variable income as a double, which will store the income value for each individ- ual as a floating-point number (allowing for decimals).

Dim lastRow As Long

This line declares a variable lastRow as a long integer, which will hold the number of the last row in the income column with data.

• Specify the number of rows in the dataset

lastRow = Cells(Rows.Count, ”B”).End(xlUp).Row

This line finds the last row of data in column B(income values).   It starts from the bottom of the worksheet (Rows.Count gives the total number of rows) and moves up until it finds the last non- empty cell in column B. This value is stored in the lastRow variable.

• Define the procedure:

For i = 2 To lastRow

This line initiates a For loop that starts at row 2 (the first row of data) and continues to lastRow. The loop iterates through each row containing income data.

income = Cells(i, 2).Value

This line reads the income value from column B (the first column) of the current row i and assigns it to the income variable.

Cells(i, 3).Value = CalculateIncomeTax(income)

This line calls the previously defined CalculateIncomeTax function, passing the income value as

an argument.  It takes the result (the calculated tax) and writes it to column C (the third column) of the same row i.

Next i

This line indicates the end of the loop.  The loop continues with the next value of i until it reaches lastRow.

The code that you should type into the module will be displayed below.

3.  Run the Code and Review the Results

•  Click on the worksheet TaxCalculation2, then go to the Developer Tab.

•  From the ribbon menu, click on Macros, select CalculateTaxesForAll, and then click on Run.

You will now see the calculated taxes for all individuals in the dataset.