MS Excel and VBA Tutorial

Objectives

  • Demonstrate the power of MS Excel, Solver, and VBA
  • Briefly discuss about Numerical Methods and Mathematical Optimization
  • Emphasize the importance of software and computer programming skills in the Chemical Engineering curriculum through examples

Announcements

  • 01/18/2012: Lectures start on March 24th, 2012, from 2:00 PM to 3:30 PM in BH 140F.

Schedule and Material

Module Description Files
1. Highlights of Excel
(03/24)
Discusses some handy features/functions of MS Excel. Prior experience with MS Excel is assumed. The topics covered are:
  • Using the Fill Handle
  • Conditional Formatting
  • Data Tables
  • Pivot Tables
  • Lookup Functions
Applications:
  • Finding Roots of Equations (iterative methods)
    • Bisection Method
    • Successive Substitution Method
    • Newton's Method
Slides.pdf
Handout.pdf

Excel_Basics.xlsx
Excel_Applications.xlsx
2. Solver Tool
(03/31)
(04/07)
Introduces the Solver add-in and illustrates its use in the solution of Linear and Nonlinear Optimization problems, and Nonlinear Systems of Equations. The topics covered are:
  • Brief overview of Optimization
  • Solving Linear Optimization (LO) problems
  • Solving Nonlinear Optimization (NLO) problems
  • Solving nonlinear system of equations (NLE)
Applications:
  • Refinery Production Optimization (LO)
  • Distillation Column Design Cost Minimization (NLO)
  • Chemical Equilibrium of the Combustion of Propane (NLE)
Slides.pdf
Handout.pdf
NLO_Example.pdf
LE_Example.pdf

Solver_Examples.xlsx
3. Programming with VBA
(04/14)
(04/28)
(05/05)
Introduction to VBA and its syntax. Topics covered include:
  • Recording Macros
  • VBA Programming: Basics (Terminology, VBE, Data Types)
  • Manipulating Objects and Collections
  • Controlling Code Execution
  • Looping Blocks of Instructions
  • Working with Spreadsheets (Ranges and Cells)
Applications:
  • Calling MATLAB from Excel/VBA
    • Design of a Plug-Flow Reactor (PFR)
Slides.pdf
Handout.pdf

VBA_Examples.xlsm
file.txt

References

  • Walkenbach, J. (2010) Excel® 2010 Power Programming with VBA. Wiley Publishing, Inc. 1080p.
  • Billo, E. J. (2007) Excel® for Scientists and Engineers: Numerical Methods. John Wiley & Sons, Inc. 480p.
  • Webb, J., Saunders, S. (2006) Programming Excel with VBA and .NET. O'Reilly Media. 1120p.