ISOM3230Business Applications

Programming

VBA Business Applications

Implementing Formulas

2

Learning Outcomes apply programming concepts to solve business

problems describe the logic and flows of given programs predict the output of a program write programs with common programming

practices identify and fix logical and run-time errors in

programs

3

Outline

1. Understanding Mean and SD application

2. Problem #1: Wrapper Subroutines

3. Problem #2: Revised Data Types

4. Problem #3: Initialize an ArraySimplified MacrosDynamic Array

5. Problem #4: Selection

6. Problem #5: Dynamic Size

3. Given Program

1. Mean & SD

2. Excel Worksheet

4

Prob. 3

Prob. 1

Prob. 2

Imp

rov

em

en

t

Prob. 4

Imp

lem

en

tin

g

Fo

rmu

las

Sol. 3: Initialize array & Simplified Macros

Sol. 1: Wrapper Sub.

Sol. 2: Revised Data Types

Sol. 4: Selection

4. Understanding Program

Prob. 5 Sol. 5: Dynamic Size

7

8

9-11

12-13

16

18

20-23

25-26

28

15

17

19

24

27

6

End

5

Business Applications –Implementing Formulas

Step 1: Mean and Standard Deviation

Mean (Average) is calculated using the following formula

Standard Deviation (SD) is calculated using the following formula

n

iixn

x1

1

1

)(1

2

n

xxn

ii

Step 1: Excel Worksheet

Excel worksheet as below

Public Sub Compute() Dim x(59) As Single Dim Average As Single Dim Std_Dev As Single Dim i As Integer For i = 1 To 59 x(i) = Sheets("ISOM3230").Cells(i + 1, 5) Next Average = Mean(59, x) Std_Dev = StdDev(59, x) Sheets("ISOM3230").Cells(61, 5) = Average Sheets("ISOM3230").Cells(62, 5) = Std_DevEnd Sub

Step 2: Given Program (1)

The following is the main program.

General steps to process data in business applications:1. Read data in and

assign them to an array (determining the size of array and creating the array)

2. Process data

3. Present the results

Step 2: Given Program (2)

The following is the Mean function in VBA:Public Function Mean(ByVal n As Long, ByRef x() As Single) As Single Dim sum As Single Dim i As Integer

sum = 0

For i = 1 To n sum = sum + x(i) Next

Mean = sum / nEnd Function

Step 2: Given Program (3) The following is the Standard Deviation

function in VBA:Public Function StdDev(ByVal n As Long, ByRef x() As Single) As Single Dim i As Integer Dim avg As Single, SumSq As Single

SumSq = 0 avg = Mean(n, x) For i = 1 To n SumSq = SumSq + (x(i) - avg) ^ 2 Next

StdDev = Sqr(SumSq / (n - 1))End Function

When the Text Box is clicked, the procedure Compute() executes and calculates Mean and Standard Deviation by calling functions Mean and StdDev.

Step 3: Understanding the program – Excel controls

Step 3: Understanding the program – Main program and the two functions

Is it the End?

No! We need improvement

15

Problem 1

In the original program, it must calculate both mean and standard deviation together, but not one of them. However, there are functions in the VBA module and that cannot be assigned to any Excel controls. Why?

Solution Make wrapper subroutines to both Mean and StdDev. A

wrapper is simply a subroutine in which it calls a particular function with some predefined settings

Assign both wrapper subroutines into remaining Excel controls called Average and StdDev

16

Solution 1: Wrapper SubroutinesPublic Function Mean(ByVal n As Long, ByRef x() As Single) As Single...End Function Public Function StdDev(ByVal n As Long, ByRef x() As Single) As Single...End Function

They are functions and cannot be assigned as macros program

Public Sub Average() Dim x(59) As Single Dim i As Integer For i = 1 To 59 x(i) = Sheets("ISOM3230").Cells(i + 1, 5) Next

Sheets("ISOM3230").Cells(61, 5) = Mean(59, x)End Sub

Wrapper subroutine for Mean function

17

Problem 2

As you know, average and standard deviation are the basic Excel built-in functions. And now, we intend to verify the correctness of our program

Insert “=AVERAGE(E2:E60)” to Cell F61 Insert “=STDEV(E2:E60)” to Cell F62 Expand the width of the column F so that we can see all

digits From the Excel functions, they generate the results that are

a little difference from ours. But, both Excel and our functions apply the same equations. Why?

Solution Replace all variables from Single to Double

18

Solution 2: Revised Data TypesPublic Function StdDev(ByVal k As Long, ByRef Arr() As Single) As Single Dim i As Integer Dim avg As Single, SumSq As Single avg = Mean(k, Arr) For i = 1 To k SumSq = SumSq + (Arr(i) - avg) ^ 2 Next StdDev = Sqr(SumSq / (k - 1))End Function

Solution - change all single variables into double

Using single precision may not be appropriate that introduces incorrect financial values

Public Function StdDev(ByVal k As Long, ByRef Arr() As Double) As Double Dim i As Integer Dim avg As Double, SumSq As Double avg = Mean(k, Arr) For i = 1 To k SumSq = SumSq + (Arr(i) - avg) ^ 2 Next StdDev = Sqr(SumSq / (k - 1))End Function

19

Problem 3

In most of times, a professional programmer cannot guarantee number of user inputs (input data) to be captured from an end user

Therefore, we cannot assume the number of elements in the current program is 59 always

What if the end user inserts one or two elements instead? The program must be modified in order to handle the new data set

We also cannot assume our end users have VBA programming knowledge in order to use the program

So, how can we solve this problem?

Solution Change from the fixed-size array into a dynamic-size array using

ReDim Use IsEmpty() to detect the end of a data set

20

Solution 3: Initialize an ArrayPublic Sub InitializeArray(ByRef x() As Double) Dim i As Integer i = 1 Do Until IsEmpty(Sheets("ISOM3230").Cells(i + 1, 5)) i = i + 1 Loop ReDim x(1 To i - 1)

i = 1 Do Until IsEmpty(Sheets("ISOM3230").Cells(i + 1, 5)) x(i) = Sheets("ISOM3230").Cells(i + 1, 5) i = i + 1 LoopEnd Sub

Solution:Create a separate subroutine to initialize an arrayApply IsEmpty to determine whether a cell is empty

21

Solution 3: Initialize an ArrayPublic Sub InitializeArray(ByRef x() As Double) Dim i As Integer

i = 1 Do Until IsEmpty(Sheets("ISOM3230").Cells(i + 1, 5)) i = i + 1 Loop ReDim x(1 To i - 1)

For i = LBound(x) To UBound(x)x(i) = CDbl(Sheets("ISOM3230").Cells(i + 1, 5).Value)

NextEnd Sub

Revised Solution:Create a separate subroutine to initialize an arrayUse For-Next Loop when the array size is determined

Type Conversion

22

Solution 3: Simplified MacrosPublic Sub Compute() Dim x(59) As Double Dim Average As Double Dim Std_Dev As Double Dim i As Integer For i = 1 To 59 x(i) = Sheets("ISOM3230").Cells(i + 1, 5) Next Average = Mean(59, x) Std_Dev = StdDev(59, x) Sheets("ISOM3230").Cells(61, 5) = Average Sheets("ISOM3230").Cells(62, 5) = Std_DevEnd Sub

Public Sub Compute() Dim x() As Double InitializeArray x Sheets("ISOM3230").Range("AVG") = Mean(x) Sheets("ISOM3230").Range("STDEV") = StdDev(x)End Sub

Solution:In Excel file, we must create an empty cell to notify that is the end of records

Similar changes to Average and SD functions

Make the array to become dynamic and remove redundant variables

Create Named Range for particular data cells

23

Solution 3: Dynamic ArrayPublic Function Mean(ByVal n As Long, ByRef x() _ As Double) As Double Dim sum As Double Dim i As Integer sum = 0 For i = 1 To n sum = sum + x(i) Next i Mean = sum / nEnd Function

Public Function Mean(ByRef x() As Double) As Double Dim sum As Double Dim i As Integer sum = 0 For i = LBound(x) To UBound(x) sum = sum + x(i) Next i Mean = sum / UBound(x)End Function

Solution:Remove the variable n, and use LBound() and UBound() instead

Apply similar changes to StdDev too

Make the program to be more generic

24

Problem 4

Until now, an end-user can easily calculate the mean and standard deviation (SD) from all input data in the worksheet

Sometimes, not all values are required as input data. In this case, the user must select relative cells together and calculate new average and SD again

In the original design, all data cells are connected together and their values are transferred to an array. Now, we need a special mechanism to capture user selection, and copy each individual cell to an array; Then, apply the particular function. i.e. Average and SD

How?

25

From Selection to Array88

89

88

86

85

84

82

. . . Dim x() As DoubleDim r As RangeDim i As Integer i = 1ReDim x(1 To Selection.Count)For Each r In Selection x(i) = CDbl(r.Value) i = i + 1Next. . .

Solution

x

26

Solution 4: SelectionPublic Sub Cal_Average() Dim x() As Double Dim r As Range Dim i As Integer i = 1 ReDim x(1 To Selection.Count) For Each r In Selection x(i) = CDbl(r.Value) i = i + 1 Next MsgBox Mean(x)End Sub

Public Sub Cal_SD() Dim x() As Double Dim r As Range Dim i As Integer i = 1 ReDim x(1 To Selection.Count) For Each r In Selection x(i) = CDbl(r.Value) i = i + 1 Next MsgBox StdDev(x)End Sub

27

Problem 5

There is another way to choose the calculated data, which is using Selection

By using:Range("E2").Select

Range(Selection, Selection.End(xlDown)).Select

You may choose a rectangular interconnected data cells How to modify?

(Hint: Only modify the InitializeArray subroutine)

28

Solution 5: Dynamic SizePublic Sub InitializeArray(ByRef x() As Double) Dim i As Integer

i = 1 Do Until IsEmpty(Sheets("ISOM3230").Cells(i + 1, 5)) i = i + 1 Loop ReDim x(1 To i - 1)

For i = LBound(x) To UBound(x)x(i) = CDbl(Sheets("ISOM3230").Cells(i + 1, 5).Value)

NextEnd Sub Public Sub InitializeArray(ByRef x() As Double)

Dim i As Integer Dim r As Range Range("E2").Select Range(Selection, Selection.End(xlDown)).Select ReDim x(1 To Selection.Count) i = 1 For Each r In Selection x(i) = CDbl(r.Value) i = i + 1 NextEnd Sub