Excel VBA Programming for Solving Chemical Engineering Problems

1
Excel VBA Programming for Solving Chemical
Engineering Problems

2
Teaching Plan

• Excel VBA Programming for Solving Chemical
  Engineering Problems will conducted in three
  lectures.
• It will cover basic programming skills that
  concentrate on solving chemical engineering
  problems.
• Demonstrate the programming of the feeding
  pattern of a Fed-Batch culture using Excel VBA
  and its comparison with alternative methods
  (Polymath and Excel)
• You are generally expected to be able to write
  simple Excel VBA programs to solve problems you
  have encountered in chemical engineering courses.
• You are specifically expected able to write Excel
  VBA programs to solve some problems that are
  encountered in CENG 364.
• One small quiz will be given at the end of these
  lectures and will be completed in class.
• A project will be given and this will contribute
  to the final assessment.

3
Introduction

• A computer can help us to solve problems,
  especially which require a large number of
  iterative calculations.
• Excel is a commonly used and easily available
  office software.
• You can compose your own Macro commands that
  instruct Excel to do repeating jobs.
• An Excel Macro command is written in Visual Basic
  for Application (VBA).
• We can make use of the Excel VBA to solve
  numerical problems in Chemical Engineering.

4
Objectives

• Introduce how to make use Excel VBA to solve
  chemical engineering problems.
• Introduce fundamental VBA syntax.
• Introduce how to analyze and covert a chemical
  engineering problem into an Excel VBA program.

5
Content

• Introduction
• Excel Macro
• Excel VBA Programming
• Basic Syntax
• Data Type, Variable and Operator
• Control Statements
• Array
• Simple Data Input Output
• Debugging
• Chemical Engineering Problem Solving

6
What Will be Not Covered

• Details of Excel VBA functions.
• It is because
• Limitation of time
• Not possible to teach all functions and during
  the class
• Technology changes from time to time, you are
  expected able to self-learn.

7
Solving a Mathematical Problem (1)

• Solving a function y 2-xwhen y x, for 0
  x 2.
• We can use the built-in Goal Seek feature of
  Excel.
• We can also write our own VBA program to solve
  this problem by iteration.

8
Solving a Mathematical Problem (2)

• Demonstration
• Goal Seek
• A VBA program
• Goal Seek is so convenient, why do we need to
  write our own program which is a time consuming
  task?

9
Solving a Mathematical Problem (3)

• Goal Seek can only find the solution of an
  equation. How about matrices, differential
  equations?
• We can write our own program to solve above
  problems that commonly encountered in the
  chemical engineering discipline.
• Example of Goal Seek vs. Programmed Macro
  (GoalSeek_vs_Programming.xls).

10
Excel Macro

• Programmed Macro vs. Recorded Macro

11
Macro Recording (1)
12
Macro Recoding (2)
13
Macro Recording (3)
14
Macro Recording (4)
15
Macro Recording (5)

• Example of recorded Macro
• Sub Macro1()
• '
• ' Macro1 Macro
• ' This is description
• '
• Range("A2").Select
• ActiveCell.FormulaR1C1 "R-1C100"
• Range("A3").Select
• ActiveCell.FormulaR1C1 "R-1C-20"
• Range("A2").Select
• Selection.Font.Bold True
• Range("A3").Select
• Selection.Font.ColorIndex 3
• End Sub
• A recorded Macro can undertake the same actions
  that can be done though the Excels graphical
  interface (for example, modifying font, color,
  position, etc). How about iterative calculations
  such as matrix or differential equations?

16
Macro (VBA) Programming

• Example of programmed Macro (Simple_Iteration.xls)
  .
• Recorded Macro can help us to do some repetitive
  jobs.
• But recorded Macro cannot help us to solve
  numerical problems that require iterative
  calculations.
• Therefore, we need to write VBA codes to solve
  our problem.

17
Help Topics

• As you are expected able to self-learn, Help is a
  good source for information.
• Sometimes you may find an example on how to make
  us a function / object.
• VBA Help content is different from Excel Help
  content.
• VBA functions are different from Excel functions.

18
Excel VBA Programming

• Basic Syntax of VBA

19
Procedure (1)

• When you write your own piece of program code,
  you need to put it in the worksheet somewhere.
• Your program will most probably read input from
  users, and print many lines of results in a
  worksheet.
• You can put your program code in a procedure with
  the VBAProject which is assessable through the
  VBA editor.
• A procedure can be executed by
• Running it though the Excels Macro dialog box,
  or
• Assigning a short-cut key though the Macro
  dialog box, or
• Creating a button and linking it to a specific
  procedure.

20
Procedure (2)

• A Sub procedure is a series of Visual Basic
  statements enclosed by the Sub and End Sub
  statements that performs actions but doesn't
  return a value.
• Private Sub CommandButton1_Click()
• MsgBox "Hello World!"
• End Sub
• Above procedure name is automatically given by
  the Excel VBA editor. This is an Event-driven
  procedure.

21
Event-Driven Programming (1)

• An event could be any action such as
• Open a workbook,
• Click a button,
• The value of any cell in a worksheet is changed,
  etc.
• Procedure name CommandButton1_Click() stands
  forWhen button CommandButton1 is clicked,
  execute following lines of code.
• The button named CommandButton1 is automatically
  assigned by Excel when you create a button. Of
  course, this name may be chnaged. And the
  procedure name should be YourButtonName_Click().

22
Event-Driven Programming (2)
23
Event-Driven Programming (3)
24
Comment

• After you have written thousands of lines of
  code, you may forget what you have written
  before.
• It is better to insert comments in your program
  to remind you what the program does.
• A comment is a line of text in the VBA macro that
  does not actually contain instructions and Excel
  will ignore it while running the macro.
• A comment starts with an apostrophe ( ' )
  character and ends with line break.
• Private Sub CommandButton1_Click()
• ' This is a line of comment.
• MsgBox "Hello World!"
• End Sub

25
Statement (1)

• A Statement is a line of VBA code to instruct VBA
  to perform certain tasks.
• Examples
• Perform calculation VarA VarB 10
• Assign a value to a variable VarB 2
• Declare a variable Dim VarA, VarB As Integer

26
Statement (2)

• A line of statement always ended with line break,
  i.e. One line, one statement.
• If you have a very long statement that is
  difficult to read, you can break it into several
  lines using an under scroll ( _ ) Result 1
  2 3 4 5 6 _ 7 8 9 10
  11 _ 12 13 14 15 16 _
  17 18 19 20

27
Statement (3)

• Not all statements can be broken into several
  lines using an under scroll
• MsgBox ("Now I want to show _
• you a very very very very _
• very very very long message")
• Above is an example of an invalid statement.
• Because it violates VBA syntax since it should be
  put in a single line.

28
Function (1)

• A function is similar to a process that can
  convert a input value into another value through
  a well defined steps of commands.
• A Function procedure is a series of Visual Basic
  statements enclosed by the Function and End
  Function statements.
• A Function procedure is similar to a Sub
  procedure, but a function can also return a
  value.
• For example, convert Celsius temperature into
  Kelvin temperature
• Function CelsiusToKelvin(CelsiusDegree)
• Dim AbsoluteZero
• AbsoluteZero 273.15
• CelsiusToKelvin CelsiusDegree _
• AbsoluteZero
• End Function

29
Function (2)

• When do we need functions?
• Extract a repeated procedure out from the main
  program can make your program with well
  structured. It will be easy for programming,
  debugging and maintenance.
• For example (pseudo-code)
• Get Celsius Temperatures 1, 2 from users
• Kelvin Temp 1 CelsiusToKelvin(Celsius Temp 1)
• Kelvin Temp 2 CelsiusToKelvin(Celsius Temp 2)
• Kelvin Temp 3 Kelvin Temp 2 exp((Kelvin Temp
  1 Kelvin Temp 2) / Kelvin Temp 2)
• Celsius Temp 3 KelvinToCelsius(Kelvin Temp 3)
• Display the result Celsius Temp 3 to user

30
Data Type, Variable and Operator
31
Data Types and Variables (1)

• Data are classified as different data types.
• Some common used data types for numerical
  problems
• Integer
• Double
• Boolean
• Integer variables are stored as 2-byte numbers
  ranging in value from -32,768 to 32,767.
• Double (double-precision floating-point)
  variables are stored as 8-byte floating-point
  numbers ranging in value from -1.79769313486231 x
  10308 to -4.94065645841247 x 10-324 for negative
  values and from 4.94065645841247 x 10-324 to
  1.79769313486232 x 10308 for positive values.
• Boolean variables are stored as 2-byte numbers,
  but they can only be True or False.

32
Data Types and Variables (2)

• You can declare a variable by using declaration
  statement
• Dim VarA As Integer
• Dim VarB, VarC As Double
• Note that for variables with different data
  types, you must declare them in separate
  statements.
• After a variable is declared, you can assign a
  value to it
• VarA 10
• Or you can perform a calculation
• VarC (VarB 3) / VarA

33
Data Types and Variables (2)

• Unless otherwise specified, undeclared variables
  are assigned the Variant data type. This data
  type makes it easy to write programs, but it is
  not always the most efficient data type to use.
• A Variant is a special data type that can contain
  any kind of data and stored as 16-byte numbers.
• You should consider using other data types if
• Your program is very large and uses many
  variables.
• Your program must run as quickly as possible.

34
Data Types and Variables (3)

• Many VBA programs for solving numerical problems
  may loop for more than a thousand times and may
  take a long time to complete.
• It is better to declare a data type for all
  variables.
• It is suggested that you put this statement at
  the beginning of all program code
• Option Explicit
• It forces you to declare data type for all
  variables.

35
Variable Scope (1)

• For a program containing two procedures
• Sub HelloWorld()
• Dim MyMessage
• MyMessage "Hello World!"
• MsgBox MyMessage
• End Sub
• Sub HelloYou()
• Dim MyMessage
• MyMessage "Hello You!"
• MsgBox MyMessage
• End Sub
• Both have a variable called MyMessage.
• However, these two variables are independent
  variables.
• Example program (Variable_Scope_1.xls).

36
Variable Scope (2)

• Now consider this program
• Dim MyMessage
• Sub HelloWorld()
• MyMessage "Hello World!"
• MsgBox MyMessage
• End Sub
• Sub HelloYou()
• MyMessage "Hello You!"
• MsgBox MyMessage
• End Sub
• Sub Hello()
• MsgBox MyMessage
• End Sub
• Now the variable MyMessage is shared among
  these three procedures.
• Example program (Variable_Scope_2.xls).

37
Variable Scope (3)

• Case I
• A variable declared inside a procedure is
  available only inside that procedure.
• This variable is called procedure-level variable.
• Case II
• A variable declared outside a procedure but in
  the same module. This variable is available to
  all procedures in the same module but not to
  other module.
• This variable is called module-level variable.

38
Variable Scope (4)

• You should pay attention to variable scope when
  designing your program.
• If you design your variable scope properly, you
  can avoid modifying the value of a variable
  accidentally.
• For the example program at right, what will be
  the value of module-level variable Var1 after
  execute procedure MainProgram()?

• Dim Var1 As Integer
• Sub MainProgram()
• Var1 10
• MyFunction()
• End Sub
• Function MyFunction()
• Var1 100
• End Function

39
Operators (1)
Commonly used operators

• Arithmetic operators
• Addition
• Subtraction -
• Multiplication
• Division /
• Integer Division \
• Modulo Division MOD
• Exponentiation
• Negative Number -
• String operator
• Concatenation (i.e., joining two strings together
  to form a longer string)

• Comparison operators
• Equal To
• Not Equal To ltgt
• Less Than lt
• Greater Than gt
• Less Than Or Equal To lt
• Greater Than Or Equal To gt
• Logical operators
• Conjunction And
• Disjunction Or
• Negation Not

40
Operators (2)

• Syntax of some operators
• 87 \ 10 ( 8, result is an integer )
• 87 MOD 10 ( 87 - (87 \ 10) 7, result is an
  integer )
• 102 ( 102 100 )
• "Hello " "World" ( "Hello World" )
• You should pay attention on the result data types
  of operators, and functions.

41
Operator Precedence (1)

• - (negative number)
• , /
• \
• MOD
• , -
• lt, lt, gt, gt, , ltgt (comparison)
• Not
• And
• Or

42
Operator Precedence (2)

• 1 2 3 4 (i.e. 12 3 4)
• 1 (2 3) 1 (i.e. 1(23) 1)
• You need to add parentheses as necessary