Excel VBA Programming for

Solving Chemical Engineering

Problems

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

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.

Objectives

1. Introduce how to make use Excel VBA to

solve chemical engineering problems.

2. Introduce fundamental VBA syntax.

3. Introduce how to analyze and covert a

chemical engineering problem into an Excel

VBA program.

Content

1. Introduction

2. Excel Macro

3. Excel VBA Programming

1. Basic Syntax

2. Data Type, Variable and Operator

3. Control Statements

4. Array

5. Simple Data Input & Output

6. Debugging

4. Chemical Engineering Problem Solving

Solving a Mathematical Problem (1)

Solving a function

y = 2-x

when 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 (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?

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

Excel Macro

Programmed Macro vs. Recorded

Macro

Macro Recording (1)

Macro Recoding (2)

Macro Recording (3)

Macro Recording (4)

Macro Recording (5) 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 Excels graphical interface (for example, modifying font, color, position, etc). How about iterative calculations such as matrix or differential equations?

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.

Help Topics

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.

Excel VBA Programming

Basic Syntax of VBA

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:

1. Running it though the Excels Macro dialog box, or

2. Assigning a short-cut key though the Macro dialog box, or

3. Creating a button and linking it to a specific procedure.

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.

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().

Event-Driven Programming (2)

Event-Driven Programming (3)

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

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

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

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.

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

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

Data Type, Variable and

Operator

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.

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

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.

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.

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

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

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.

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

Operators (1)

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: =

Logical operators: Conjunction: And

Disjunction: Or

Negation: Not

Commonly used operators

Operators (2)

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.

Operator Precedence (1)

^

- (negative number)

*, /

\

MOD

+, -

&

=, =, (comparison)

Not

And

Or

Operator Precedence (2)

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