Excel/VBA

An Introduction to Data Analysis and Programming

Author

Breno A. Beirigo

Syllabus

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

🧠 Learning objectives

📊 Data Processing in Excel

  1. The student gets familiar with Excel by using several types of graphs, tables, and functions.
  2. The student can navigate, analyse, clean, sort and represent (graphically or numerically) data.
  3. The student can make pivot tables based on data and apply it in graphs.

💻 Elementary Programming Skills with VBA

  1. The student knows the basic logic constructs of programming.
  2. The student knows the basic functionality of VBA in Excel.
  3. The student is able to use common debugging tools to find errors in code.
  4. The student can implement simple routines, with input and/or output parameters.
  5. The student knows the basic syntax of VBA, and can execute VBA code via buttons or forms.
  6. The student can implement simple routines to read or write data from/to Excel.

📝 Assessment Scheme

The assessment consists of 6 multiple-choice exams with a duration of 1 hour each. Each exam covers all topics discussed up to that point in the course with a focus on the most recent content. The final grade will be calculated as the average of the top 5 grades. Each exam comprises both VBA programming and Excel questions.

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.

Conditions to Pass the Course

To pass the course, you must meet the following conditions:

  1. Attend at least 5 of the 6 exams. Missing one of these exams is allowed.
  2. Achieve at least \(m_i\) on every partial exam. Achieving one grade below \(m_i\) is still allowed, as only the top 5 grades will constitute the final average. The value \(m_i\) is about 40% of the maximum grade. You can find the maximum and minimum grades for each exam in Table 1.
  3. Achieve a grade of at least 5.5 on the average of the top-five-graded exams. This is the minimum grade required to pass the course.
Table 1: Maximum and minimum scores for each exam. Minimum scores are required to continue doing the partial exams.
Group Exam \(i\) E1 E2 E3 E4 E5 E6
Excel & VBA Programming Max. Score (\(N_i\)) 12 10 10 10 10 10
Min. Score (\(m_i\)) 5 4 3 4 4 4
Only VBA Programming Max. Score (\(N_i\)) 12 6 10 10 10 10
Min. Score (\(m_i\)) 6 2 3 4 4 4

Grade Calculation

The final grade can be calculated as follows:

  1. Calculate the Each Test Grade: \[ \text{Grade}_i = 1 + 9 \times \left( \frac{\text{Score}_i}{N_i} \right) \quad \forall i \in \{1, 2, 3, 4, 5, 6\}, \] where \(\text{Score}_i\) is the score obtained in test \(i\) and \(N_i\) is the maximum score of test \(i\). Please notice that:

    • \(\text{Score}_i\) can be found on Canvas.
    • \(\text{Grade}_i\) ranges from 1 to 10.
    • \(\text{Score}_i\) ranges from 0 to \(N_i\).

  2. Sum the Top Five Highest Grades: \[ \text{Sum of Top Five Grades} = \sum_{i=1}^{6} \text{Grade}_i - \text{Lowest Grade} \]

  3. Compute Final Grade: \[ \text{Final Grade} = \frac{\text{Sum of Top Five Grades}}{5} \]

Passing Grade

If \(\text{Final Grade}\) is greater than or equal to 5.5 AND for all \(i \in \text{Top-Five-Graded Tests}\), \(\text{Score}_i \geq m_i\), then you pass the course. Otherwise, you are required to take the resit exam; your final grade for opportunity 1 will be 5.

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 2.

Table 2: 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, analogous to the 6th exam. It will last 2 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.

Example 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).

Example 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)

    • Wed, 04/09 - 10:45-12:30 | NH 209 | Course Introduction
    • Wed, 04/09 - 13:45-15:30 | NH 209 | Algorithm Development

  • w37 (2/10)

    • Mon, 09/09 - 10:45-12:30 | NH 209 | Algorithm Development
    • Wed, 11/09 - 10:45-12:30 | NH 209 | Introduction: Excel
    • Wed, 11/09 - 13:45-15:30 | NH 209 | Introduction: Excel
    • Fri, 13/09 - 10:45-12:30 | NH 209 | Content Review
    • Fri, 13/09 - 13:45-14:45 | SC 2 | 📝Exam 1 (Written)

  • w38 (3/10)

    • Wed, 18/09 - 10:45-12:30 | NH 209 | Exam Review, VBA Basics
    • Wed, 18/09 - 13:45-15:30 | NH 209 | Functions and Subs
    • Fri, 20/09 - 10:45-12:30 | NH 209 | Content Review
    • Fri, 20/09 - 13:45-14:45 | Therm 1, Therm 2 | 📝Exam 2 (Chromebook)

  • w39 (4/10)

    • Mon, 23/09 - 10:45-12:30 | NH 209 | Exam Review, Data IO in VBA (part 1), Variables
    • Wed, 25/09 - 10:45-12:30 | NH 209 | Operators, Conditionals
    • Wed, 25/09 - 13:45-15:30 | NH 209 | Conditionals
    • Fri, 27/09 - 10:45-12:30 | NH 209 | Content Review
    • Fri, 27/09 - 13:45-14:45 | NH 115, NH 124 | 📝Exam 3 (Chromebook)

  • w40 (5/10)

    • Wed, 02/10 - 10:45-12:30 | NH 209 | Exam Review, Loops, Data IO in VBA (part 2)
    • Wed, 02/10 - 13:45-15:30 | NH 209 | Loops (For Next, Do While … Loop, Do … Loop While)
    • Fri, 04/10 - 10:45-12:30 | NH 209 | Content Review
    • Fri, 04/10 - 13:45-14:45 | NH 115, NH 124 | 📝Exam 4 (Chromebook)

  • w41 (6/10)

    • Mon, 07/10 - 10:45-12:30 | NH 209 | Exam Review, Data Structures
    • Wed, 09/10 - 10:45-12:30 | NH 209 | Data Structures
    • Wed, 09/10 - 13:45-15:30 | NH 209 | Data Structures
    • Fri, 11/10 - 10:45-12:30 | NH 209 | Content Review
    • Fri, 11/10 - 13:45-14:45 | NH 115, NH 124 | 📝Exam 5 (Chromebook)

  • w42 (7/10)

    • Mon, 14/10 - 08:45-09:30 | TL 3330 | Exam Review, Data Structures
    • Mon, 14/10 - 13:45-15:30 | TL 3130 | Data Structures
    • Wed, 16/10 - 10:45-12:30 | NH 209 | Simulation
    • Wed, 16/10 - 13:45-15:30 | NH 209 | Simulation
    • Fri, 18/10 - 10:45-12:30 | TL 3130 | Content Review
    • Fri, 18/10 - 13:45-14:45 | SC 1 | 📝Exam 6 (Written)

  • w44 (8/10)

    • Wed, 30/10 - 10:45-12:30 | NH 115 | Inspection
    • Thu, 31/10 - 13:45-15:30 | Therm 2 | 📝Resit (Chromebook)
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:

📚 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.↩︎