Unformatted Attachment Preview
ECH 3002 – Intro to Chemical and Biomedical Engineering
In this assignment, you will create two Excel-based spreadsheet tools. You should turn in two Excel
macro-enabled files as your answers to this assignment (i.e. they will be saved in “.xlsm” format so
that the macros you create can be seen and tested). The code you write should be commented so
that the TA can understand what your code does and it should use variable names that are somewhat
obvious as to their function. When saving your Excel files, keep the same name that they are
downloaded as and simply add your last name to the end of the file name.
1. As we have seen before, solution methods for linear systems of equations can be very useful
in chemical engineering. We have already seen how to set up systems of linear equations
format and then solve them both by hand and by using MATLAB. It is also the case that Excel
can be programmed to solve Ax=b problems using methods like Guassian Elimination with
Back Substitution or Gauss-Jordan Elimination. Excel also has built-in worksheet functions to
operate on matrices like MMULT(rangeofcells1, rangeofcells2) which will multiply two
ranges of cells together by treating them like matrices (of course the number of columns in the
first range of cells must equal the number of rows in the second range of cells and the ranges
must contain only numbers).
In this problem you are provided with an Excel worksheet that has already been started which
will build a tool to solve Ax=b problems in Excel. The sheet already has 3 command buttons
defined in it. The first button has code that clears the working area of the sheet so that it can
be used over and over again to solve different Ax=b problems. The second button has code
in it that asks the user for the number of variables in the Ax=b system and uses that information
to draw areas for the user to input A and b into the spreadsheet.
Your assignment is to complete the code for the third button labeled “Solve Ax=b Problem.
The code you use must accomplish the following tasks:
1. It must find the solution for Ax=b problem.
2. It must display the solution to the user in the area of the worksheet labelled by the
“Define Number of Unknown Variables” button as the x vector solution.
You can use built in functions like Application.MInverse(Range of Cells To Be Inverted) to
find the inverse of the A matrix and then do matrix math for x=A -1b (this probably requires the
least code to be written but you will probably need to do a little research on how to use that
built-in function and then do the matrix multiplication) or you can write code to perform
Gaussian Elimination with Back Substitution or Gauss-Jordan (these just require reading in
the A and b from the sheet into arrays in Excel and then a couple of loops to do the row
elimination, but will require more lines of code and some careful thinking about the loops).
You should test your code to make sure it works by solving the following system of
3𝑥 + 2𝑥 + 1𝑥 = 11
1𝑥 + 1𝑥 + 2𝑥 = 9
1𝑥 = 5
We will discuss building a worksheet in class that will find a root for a polynomial equation
supplied by the user. That Excel spreadsheet is provided with this homework assignment as
a starting point. Your task in this problem is to complete that VBA code in the “PolyFind”
subroutine linked to the “Define Polynomial and Find Root” button so that when pressed the
routine will find one root of the equation supplied by the user. We will assume for this problem
that the equation supplied by the user will in fact have a root (note this is not always guaranteed
with polynomial equations that have an even degree or order) so that you do not have to test
to make sure it in fact has a root.
The code is already built such that it expects you to evaluate the value of each term of the
polynomial at the estimated root in the cells next to each term in Column 3. It also expects
you to sum up the values of all of the polynomial terms at the estimated root in a cell at the
bottom of all of the term values in Column 3. Make sure that your root estimate appears in
Cell(2,2) when the code is finished running.
Test your code by finding a root of the polynomial:
2𝑥 + 𝑥 − 15𝑥 − 18 = 0
This sheet will solve an Ax=b linear system of equations problem.
1. Press the "Clear Work Area" button to clear the working area portion of the spreadhseet.
2. You must then press the "Define Number of Unknown Variables" button and supply the number of variables in the equatio
3. When you define the number of variables, the sheet will display areas in which to enter the A matrix and b vector, and you
4. You must press the "Solve System of Equations" button and the program will display the solution to the system.
matrix and b vector, and you must enter the coefficients for A and b into the indicated cells. It will also define the area where the answer
efine the area where the answer vector for x will be shown, but do not type any numbers into that area.