Excel/VBA

An Introduction to Data Analysis and Programming

Author

Breno A. Beirigo

Syllabus (2025)

Welcome to Introduction to Data Analysis and Programming with Excel and VBA!

This course is designed to provide you with the fundamental skills to process, analyze, and visualize data using Excel and to develop elementary programming skills with VBA (Visual Basic for Applications).

In this book, you will find all the information you need to succeed in the course.

General Information

  • Course code: 202400550
  • Number of EC: 2 EC
  • Language of instruction: English
  • Offered by: Bachelor Industrial Engineering and Management (IEM)
  • Lecturer: Breno A. Beirigo

📘 Content

This course introduces students to problem solving and automation using Excel spreadsheets and Visual Basic for Applications (VBA). Excel provides a familiar environment for working with data, while VBA is a built-in programming language that allows users to automate tasks and build simple tools within Excel.

Students learn to think computationally by applying key concepts such as decomposition, pattern recognition, abstraction, algorithm design, and evaluation. They practise cleaning and organising data, building charts and pivot tables, and writing clear instructions in code to carry out tasks automatically. The course uses real-world datasets from a variety of domains. Through practical exercises, students learn to build useful applications and develop structured thinking that supports effective problem solving in technical and analytical contexts.

Key topics include:

  • Excel for data analysis
    • Importing, exporting, cleaning, sorting, and filtering data
    • Using formulas to analyse and transform data
    • Creating charts, pivot tables, and histograms for visualisation
  • Programming with VBA
    • Algorithm design and structured programming
    • Writing procedures and functions to automate tasks
    • Handling input and output
    • Manipulating spreadsheet ranges and cells
    • Using variables to store and manipulate data
    • Using data types and scope to manage data
    • Using built-in functions
    • Using operators for calculations and comparisons
    • Using conditionals and loops to control program flow
    • Working with arrays: static, dynamic, one-dimensional, and multi-dimensional
    • Macro recording and form-based user interfaces (buttons, forms)
    • Debugging with the VBA editor

🎯 Aims

By the end of the course, students will be able to:

🧠 Computational Thinking

  • Decompose complex problems into smaller, manageable steps and design clear, step-by-step solutions.
  • Identify patterns in data or processes to generalize solutions and improve efficiency.
  • Apply abstraction to focus on essential elements of a problem, connecting data, logic, and user needs in coherent workflows.
  • Evaluate and refine solutions, such as code or AI outputs, to ensure accuracy, functionality, and usability.

📊 Data Analysis in Excel

  • Organise and clean data using sorting, filtering, and other basic tools in Excel.
  • Use Excel formulas to analyse and change data.
  • Combine formulas to solve problems and automate tasks.
  • Create charts and summaries to clearly show patterns or results.

💻 Elementary Programming in VBA

  • Build logical routines using programming elements like variables, conditions, and loops.
  • Write code that reads from and writes to Excel spreadsheets.
  • Organise code into small parts (subroutines and functions) to make it easier to understand and reuse.
  • Create user-friendly dashboards and forms to help users interact with the data.
  • Use the VBA editor to find and fix errors in the code.

📝 Assessment Scheme

📊 Course Assessment Scheme

This course is assessed through 3 two-hour paper-based multiple-choice exams. Each exam includes questions on both Excel and VBA programming.

The questions are designed to assess more than just memorisation. They focus on your ability to apply, analyse, and evaluate code and spreadsheet logic. Questions may involve:

  • Tracing code and predicting outputs
  • Identifying and correcting errors
  • Comparing solutions for clarity or efficiency
  • Interpreting Excel formula behaviour
  • Making design decisions in realistic scenarios
  • Identifying the best solution among multiple options
  • Integrating code snippets into larger programs

Test Weighting

Your final result is based on a weighted score of the three exams, E1, E2, and E3 (each 0–100 points). Calculate it as:

\[ \text{Weighted Score} \;=\; 0.20\times E1 \;+\; 0.30\times E2 \;+\; 0.50\times E3 \]

Each exam covers all material discussed up to that point, with emphasis on the most recent topics.

Grading and Passing Criteria

Your final grade is calculated by

\[ \text{Final Grade} =5.5 \;+\;\bigl(p - c\bigr)\times\frac{4.5}{\,t - c\,} \]

where
- \(p\) = weighted score
- \(t = 100\) (total possible points)
- \(c = 0.6\,t = 60\) (cutting score)

To pass the course you need a final grade of at least 5.5, which corresponds to a weighted score of at least 60 percent.

Note

If you skip an exam it counts as zero for that exam. You can still pass provided your weighted score is at least 60 percent.

Resit

If your final grade is below 5.5, you may take a three-hour paper-based multiple-choice exam covering the entire course material. The resit grade replaces the grades of all three exams, and your final grade will be calculated solely from the resit score using the same formula above.

Important

The exam is not time for learning, but for demonstrating your knowledge. The time pressure is part of the exam, and you should be prepared to answer the questions within the time limit. Therefore, it is essential to understand the concepts and practice the exercises to succeed in the exams.

In Osiris, the value of \(\text{Final Grade}\) is presented as a decimal number with one decimal place and follows the rounding rules presented in Table 1.

Table 1: Rules for rounding grades to the nearest integer value. n represents the integer part of the grade.
Condition Output
If n ≠ 5:
Grade ≥ n.00 and < n.25 n.0
Grade ≥ n.25 and < n.75 n.5
Grade ≥ n.75 and < (n + 1).00 (n + 1).0
If n = 5:
Grade ≥ 5.00 and < 5.50 5.0
Grade ≥ 5.50 and < 6.00 6.0

Resit Exam

The resit exam will cover the topics of the entire course. It will last 3 hours and will be scheduled at the end of the course.

Exam Question Style

The exam questions will be based on the content of the lectures, exercises, and assignments. Therefore, it is essential to understand the concepts and practice the exercises to succeed in the exams.

The multiple-choice exams will be in the style of:

  • “What does this function do?”
  • “What is the output of this formula?”
  • “What is the result of this operation?”
  • “What is the correct way to implement this feature?”

See the examples below for the different question styles.

NoteExample of Excel Question

Given the following Excel formula:

=IF(A1>10, "Yes", "No")
  1. What is the output if A1 is 5?
      1. Yes
      1. No (Correct)
      1. Error
      1. None of the above

Note: The syntax of the IF function in Excel is IF(condition, value_if_true, value_if_false).

NoteExample of VBA Question

Consider the following VBA code:

Sub Example()
    Dim x As Integer
    x = 5
    x = x + 1
    MsgBox x
End Sub

Sub TestExample()
    Dim y As Integer
    y = Example()
    MsgBox y
End Sub
  1. What is the output of Example?
      1. 5
      1. 6 (Correct)
      1. 7
      1. 0
  2. What is the output of TestExample?
      1. 5
      1. 6
      1. 7
      1. 0 (Correct)

🔍 Content Overview

  • w36 (1/10)

    1. Course Introduction
    2. Algorithm Development

  • w37 (2/10)

    1. Algorithm Development
    2. Introduction: Excel
    3. Introduction: Excel
    4. Introduction: Excel

  • w38 (3/10)

    1. Functions and Subs
    2. Functions and Subs
    3. Content Review
    4. 📝Exam 1

  • w39 (4/10)

    1. Exam Review
    2. Data IO in VBA (part 1), Variables, Operators, Conditionals
    3. Variables, Operators, Conditionals

  • w40 (5/10)

    1. Loops (For Next, Do While … Loop, Do … Loop While)
    2. Data IO in VBA (part 2)
    3. Content Review
    4. 📝Exam 2

  • w41 (6/10)

    1. Exam Review
    2. Data Structures
    3. Data Structures

  • w42 (7/10)

    1. Simulation
    2. Simulation
    3. Content Review
    4. 📝Exam 3

  • 43 (8/10)

    • No activities.

  • w44 (9/10)

    1. Exam Review
    2. 📝Resit
Warning
  1. Adjustments to the course schedule may occur to reflect the students’ progress.
  2. Times and dates may change due to unforeseen circumstances.

🤖 Policy on Generative AI

In this course, we encourage the use of Generative AI, such as ChatGPT, to assist with your coding. However, we expect you to use it as a tool to help you understand the concepts, not as a replacement for your learning process. AI gives you the power to quickly test new ideas, but it is your responsibility to understand the concepts behind them and apply them correctly in the right context.

You may treat AI as a “virtual TA” that can help you with coding and debugging1.

For debugging, you can use AI to help you understand error messages and suggest possible solutions. However, you should always strive to understand the error and the solution yourself.

Important

The focus on understanding is crucial, as the exams will test your comprehension of the concepts, not your ability to generate code. Coding will be exercised in-class and necessary to complete the project.

Prompt Examples for an AI Tutor

The following are examples of prompts you can use with an AI tutor:

  • “What does this error message mean?”
  • “How can I fix this error?”
  • “What does this code do?”
  • “How can I improve this code?”
  • “What is the best way to implement this feature?”
  • “What is the output of this code?”
  • “How can I optimize this code?”
  • “What is the best way to structure this code?”
  • “What is the best way to debug this code?”
  • “What is the best way to test this code?”
  • “What is the best way to document this code?”
  • “What is the best way to refactor this code?”

These prompts can help you use AI as a tool to enhance your learning experience. It has been shown that if you ask AI to explain the code “step-by-step,” it can help you understand the code better.

Getting Started with AI

If you are new to using AI for coding, here are some tips to get started:

  1. Start Small: Begin with simple prompts and gradually increase the complexity.
  2. Ask Questions: Use AI to answer specific questions you have about the code.
  3. Understand the Output: Make sure you understand the AI-generated code and the reasoning behind it.

AI Tools

Here are some AI tools you can use for coding:

Priming the Answers

Sometimes, you may want to prime2 the AI to get better answers. Otherwise, the AI may return code that is too advanced or hard to understand.

The following is a series of directives you can use to prime the AI for better answers. You can copy and paste them into the chat window before asking your question.

I am learning to code in Excel VBA and want you to follow the guidelines below when generating code snippets for me:

  • Use only beginner-level VBA syntax. Avoid advanced features like arrays, custom classes, error handling, or complex object models.
  • Explain what the code does before the code block. Include a short summary of the macro’s purpose.
  • Add explanatory comments for every section of the code, including:
  • The purpose of the Sub or Function
  • What each block of logic does
  • Why certain methods or properties are used
  • Use simple and descriptive variable names (e.g., totalAmount, lastRow, userName).
  • Use only basic control structures: If...Then, For, Do While, MsgBox, InputBox.
  • Avoid advanced constructs like With blocks, Select Case, GoTo, or custom object manipulation.
  • Avoid using .Select or .Activate unless absolutely necessary, and explain why if used.
  • Use line continuation characters (_) when a line exceeds ~70–80 characters, following traditional VBA readability practices.
  • Keep each line of code short and readable. Break long expressions into multiple lines using _.
  • Avoid compact or shorthand syntax. Write everything explicitly for clarity.
  • Ensure the code is ready to run in Excel VBA without requiring additional setup.
  • Do not use external libraries or references. Stick to built-in Excel VBA features.

📚 Course Materials

You can find all the course materials in this website and on Canvas. Be aware that the this website will be updated frequently with new content and exercises. Feel free to explore the content and use the exercises to practice your skills. If you have feedback or suggestions, please mail me at b.alvesbeirigo@utwente.nl.

📢 Communication

We will use the following channels for communication:

  • Canvas: Announcements, grades, and discussions (content, questions).
  • Canvas Discussions:
    • Ask questions, share resources, discuss topics.
    • Answers by fellow students, TAs, and lecturer.
  • Email: For private matters.
  • Office Hours: Before/after class, or by appointment.
  • Feedback: Always welcome! The earlier, the better.

  1. Debugging refers to the process of identifying and fixing errors in code. It is an essential skill for programmers to develop.↩︎

  2. Priming is the process of preparing the AI to provide better answers by giving it specific instructions or context. It helps the AI understand your needs and generate more relevant responses.↩︎