CHEE 205 – Homework 1: Exploring Excel VBA

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

CHEE 205 – Homework 1: Exploring Excel VBA

Introduction

In the field of chemical and environmental engineering, the ability to automate processes and conduct complex calculations is critical. Excel VBA (Visual Basic for Applications) is a powerful tool that allows you to automate tasks within Excel. It's often used in engineering fields for data analysis and process optimization. There are always a handful of interns and graduates from our program that report back that they use Excel VBA in their everyday work life.

 I encourage you to explore the website: Getting started with VBA in Office to understand when to use VBA and why, basic programming, and more. There are many resources on D2L available located in Part 1: Introduction to Course and in the page: [Optional] A Guide to Using Excel & Excel VBA. You will need to have the developer tab available.

Each homework from here forward will be worth 2 P/F items. They are separated into two different sections to give you two different tasks. Homework assignments are designed to last around one to two hours to give you more practice on topics learned in class.

Objective

The objective of this assignment is to introduce you to the basics of VBA in Excel. Upon completion of this assignment, you should be able to:

1. Understand how to write simple VBA scripts to automate tasks.

2. Gain familiarity with VBA data structures like variables and arrays.

3. Understand how to use basic VBA functionalities like loops, if statements, and user-defined functions when the code has been generated. 

Homework

Part 1: Automating tasks with VBA

For the first part of this assignment, you will write a simple VBA script to automate a repetitive task. A use for this would be if you have a large dataset of raw data in Excel, and you need to process this data in a certain way (e.g., convert units, filter out certain values, calculate averages, etc.). Finding, editing, or writing from scratch (not preferred) a VBA script that can perform this operation automatically will save you many hours of repetitive labor!

Instructions:

· Download the dataset CHEE 205 Homework 1 – Excel VBA Dataset

· Find/edit/write a VBA script to delete all rows with “---” or “XXX” in the origination and destination. You will need to code this in the Visual Basic Editor.

· Include detailed comments explaining what your code is doing at each step and your sources for where you found helpful code.

Part 2: Basic VBA functionalities

For the second part of this assignment, you will write a simple VBA function. Functions should take in one or more inputs, perform a calculation or process, and return an output. The applications are limitless! Functions can calculate the Reynolds number given the flow characteristics, convert between different units, or calculate the pH of a solution given the concentration of H+ ions. The world is your oyster with functions.

Instructions:

· See tab “Function Example” in the dataset workbook to see an example of a user-defined function at work.

· Write the VBA code for a function that converts the USD to euros. This should just be an input times the current conversion rate (which can be a static value for this exercise).  

· Include detailed comments explaining what your code is doing at each step.

· Test your function by creating a new column and converting the dollars to euros.

A Note on Programming

Very rarely will code work exactly the way you want it to the first time you program something. Debugging is a normal – and prolific – part of programming and you are encouraged to ask questions and seek help when needed. The teaching team is here to help!

What to submit to D2L:

A PDF of the completed template to D2L and your .xlsm file.

Diving Deeper

(This section is optional, but strongly encouraged)

· Write a brief reflection on what you learned, what you found challenging, and how you might use these skills in your future work. This is to encourage you to think critically about your learning process and the practical applications of your new skills.

· Update your function to pull in a current value for the conversion of USD to euros.

· Another helpful tool that you can create in excel is Userform. Follow the instructions for the currency converter to create a neat Userform!

· Feel free to visit WiseOwl Training for more practice on Excel VBA Macros exercises!

CHEE 205 – Homework 1: Exploring Excel VBA

[Names] [Date]

***Before you add to this template, please delete the previous pages of instructions to make this document more concise for the grader.***

The goal of this homework is to:

1. Understand how to write simple VBA scripts to automate tasks.

2. Gain familiarity with VBA data structures like variables and arrays.

3. Understand how to use basic VBA functionalities like loops, if statements, and user-defined functions when the code has been generated.

Part 1: Automating tasks with VBA

Edited Data

[Insert a screenshot of the first page of your edited dataset with the delete rows here]

Code

[Copy and paste your macro code here. Make sure you commented your code!]

Part 2: Basic VBA functionalities

Edited Data

[Insert a screenshot of the first page of your edited dataset with the converted column here]

Code

[Copy and paste your function code here. Make sure you commented your code!]