Select Page
##### Engineering Computations

Question 1 (60%) – Conversion of kilowatts to watts and horsepower

Create an Excel spreadsheet application to convert kilowatts to watts and horsepower (HP).

The formula to convert kilowatts to watts is:

watts (W) = kilowatts (kW) × 1000

The formula to convert kilowatts to horsepower is:

Horsepower (HP) = kilowatts (kW) × 1.34

kW  is a variable input value. The start value of kW, end value of kW and the number of intervals are the input data to the program.

Your program should calculate a step size. This is (end value of kW – start value of kW) / (number of intervals).

Your spreadsheet should contain thtree buttons. Clicking on the “Convert kW to W” button reads the inputs, calculates and displays the watts (W) for each value of kW. Clicking on the “Convert kW to HP” button reads the inputs, calculates and display the horsepower (HP) for each value of kW.

Your program should only perform the unit conversion if the start value, end value and number of intervals are positive numeric values. The unit conversion should also only be performed if the end value is greater than the start value. You need to assume that the user may incorrectly input non-numeric inputs.

Your message boxes need to well-formatted with appropriate icons (eg. vbInformation) and a title.

Clicking on the Clear All button will clear all input and output data.

Your program should run as efficiently as possible. Use cell names where possible. Your program should be written programmatically in the VBA programming style. Your program should also handle unexpected or invalid inputs by the form of clearly written error messages (in message boxes).

Add brief comments into VBA program explaining the purpose of your subs (to be inserted above your sub names) and how the “trickier” lines work (as in-line comments).

Your code should be written for efficiency, reusability and readability.

Here are some run time examples of the spreadsheet application.

Question 2 – Air quality and multisensor data analysis case study

WARNING: The use of any non-conventional VBA code is strictly prohibited and the programming and stylistics conventions of VBA programming as taught in this subject must be followed closely. Any use of such code may result greatly affect the efficiency, reusability and readability of your code, and this may also lead to formal academic investigation.

Download the data file AirQ_smallDataset.csv and AirQ_largeDataset.csv from Canvas and save it into the same folder as your assignment Excel file.

The data file contains the hourly averaged responses of gas multisensor devices deployed in an Italian city. The data is used in the following academic paper:

1. De Vito, E. Massera, M. Piga, L. Martinotto, G. Di Francia, On field calibration of an electronic nose for benzene estimation in an urban pollution monitoring scenario, Sensors and Actuators B: Chemical, Volume 129, Issue 2, 22 February 2008, Pages 750-757, ISSN 0925-4005 (http://linkinghub.elsevier.com/retrieve/pii/S0925400507007691)

The actual data set can be found in the UCI Machine Learning repository at https://archive.ics.uci.edu/ml/datasets/Air+Quality.

The csv dataset files contain 7 columns / attributes:

Column A – Measurement Identifier (ID)

Column B  – Date (DD/MM/YYYY)

Column C – Time (Hour)

Column D –  Benzene concentration in microg/m^3

Column E – NOx concentration in ppb (reference analyzer)

Column F – Temperature in °C

Column G – Relative Humidity (%)

Missing values are represented with the number -200.

Create an Excel spreadsheet with following eight VBA subs:

• A ReadFileSmall sub that reads the data from the data file and stores them in seven arrays.
• A ReadFileLarge sub that reads the data from the data file and stores them into in seven arrays. The variables representing the arrays used in this sub must be the same as used in

(c)  A ShowData sub that displays the ID, date, time (hour), benzene concentration level, NOx concentration level, temperature and relative humidity for each hourly reading.

(d)  A SearchForHour sub that allows the user to enter a specific value for the searching a specific hour in an Input Box. After the value is validated, highlight the row containing the readings from a specific hour (by changing the background colour of the rows). This sub should only accept an integer from 0 to 23 inclusive. Display appropriate error message(s) if the user enters invalid input into the Input Box.

• A ReplaceValues sub that replaces the missing NOx concentration values (-200) with a corresponding measurement from the immediate previous hour (For example, at 11/3/2004 3 am, this value -200 should be replaced with 45 in the relevant array entry and on the screen). Also display a pop-up message pop that indicates the number of measurements that have been replaced (Hint: There are 4 missing values in the small data set)
• A Summary sub that displays the average benzene concentration and average temperature for each day (across all days in the dataset that is currently in your arrays). Your decimal outputs should be formatted to two decimal places. For example, the average temperature for 11/3/2004 is 9.84.
• SaveData sub that saves the ID, date, time, benzene concentration level, NOx concentration level, temperature and relative humidity into seven arrays, then saves these seven arrays into a file “SmoothedData.csv” in csv format (the same format as AirQ_smallDataset.csv or AirQ_largeDataset.csv). The last row of this file should also contain the average benzene concentration level, average NOx level, average temperature and average relative humidity (the first three columns of the last row should contain the label “All”). Assume that the user can alter the data listed in the spreadsheet, and that the user may add or delete data in the spreadsheet. You may also assume that the four rightmost columns will always contain numeric data.
• A ClearSheet sub that clears all data on the spreadsheet and clears all arrays;

Add clear comments into VBA program explaining the purpose of your subs (to be inserted above your sub names) and how the “trickier” lines work (as in-line comments).

Create eight command buttons to execute the VBA subs.

Samples of spreadsheet interface are shown below. The screenshot below is how is should appear when your spreadsheet is just opened

The following screenshot appears after “Read Small Dataset” and “Show Data” have been clicked.

Your program should run efficiently and in a user-friendly manner. Use cell names where possible.  Your program should be written programmatically in the VBA programming style. You may consider create additional subs to make your program for efficient, reusable and readable. Apply the “Don’t Repeat Yourself (DRY)” principle in your code to reduce the amount of redundant or repeated code.

Your program should work correctly if the user alternates between reading the small and large datasets. Your program should also handle unexpected situations in the form of clearly written error messages in message boxes.

Your code should be elegant and concise. It should be written for efficiency, reusability and readability. Create and use separate subs and functions where appropriate.

WARNING: The use of any non-conventional VBA code is strictly prohibited and the programming and stylistics conventions of VBA programming as taught in this subject must be followed closely. Any use of such code may result greatly affect the efficiency, reusability and readability of your code, and this may also lead to formal academic investigation.