Excel VBA Programming for Solving Chemical Engineering Problems

1. Excel VBA Programming for Solving Chemical Engineering Problems

2. 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 361. One small quiz will be given at the end of these lectures and will be completed in class. A project will be given (batch sterilisation of medium) and this will contribute to the final assessment. Teaching Plan

3. 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. Introduction

4. 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. Objectives

5. 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 Content

6. 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. Solving a Mathematical Problem (1)

7. 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? Solving a Mathematical Problem (2)

8. 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). Solving a Mathematical Problem (3)

9. Excel Macro Programmed Macro vs. Recorded Macro

10. Macro Recording (1)

11. Macro Recoding (2)

12. Macro Recording (3)

13. Macro Recording (4)

14. Example of recorded Macro: Sub Macro1() ' ' Macro1 Macro ' This is description ' Range("A2").Select ActiveCell.FormulaR1C1 = "=R[-1]C*100" Range("A3").Select ActiveCell.FormulaR1C1 = "=R[-1]C-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 Excel’s graphical interface (for example, modifying font, color, position, etc). How about iterative calculations such as matrix or differential equations? Macro Recording (5)

15. 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. Macro (VBA) Programming

16. 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. Help Topics

17. Excel VBA Programming Basic Syntax of VBA

18. 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 Excel’s 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.

19. 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. Procedure (2)

20. 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 for:When 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().

21. Event-Driven Programming (2)

22. Event-Driven Programming (3)

23. 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 Comment

24. 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 Statement (1)

25. 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 Statement (2)

26. 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. Statement (3)

27. 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

28. 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 Function (2)

29. Data Type, Variable and Operator

30. 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. Data Types and Variables (1)

31. 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 Data Types and Variables (2)

32. 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. Data Types and Variables (2)

33. 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.

34. 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). Variable Scope (1)

35. 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). Variable Scope (2)

36. 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. Variable Scope (3)

37. 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 Variable Scope (4)

38. 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: <> Less Than: < Greater Than: > Less Than Or Equal To: <= Greater Than Or Equal To: >= Logical operators: Conjunction: And Disjunction: Or Negation: Not Operators (1) Commonly used operators

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

40. ^ - (negative number) *, / \ MOD +, - & <, <=, >, >=, =, <> (comparison) Not And Or Operator Precedence (1)

41. 1 ^ 2 + 3 = 4 (i.e. 12 + 3 = 4) 1 ^ (2 + 3) = 1 (i.e. 1(2+3) = 1) You need to add parentheses as necessary Operator Precedence (2)