Introduction to Information Systems
Learning Outcomes
Lab 05: Advanced Business Analytics III
1. Manipulate data using iterative (looping) statements.
Copyright By PowCoder代写 加微信 powcoder
2. Selection of data using conditional statements.
Iterative (looping) statements
In previous lab, we have made input and output interface of Excel worksheet using VBA. In this lab, we are going to carry out some data processing.
Consider a list in Excel worksheet as follows.
The marked price is expected to be the sum of cost and profit. Besides copy and paste the sum formula throughout the column, we may make use of VBA iterative (looping) statements to compute the marked prices of the products.
Syntax of “Do Until” loop:
Do Until Condition_is_True
Statements_to_be_executed.
This is the VBA program that can compute the marked prices of the products:
Sub MarkedPrice()
Dim i As Integer
Dim count As Integer
Dim MarkedPrice As Double
Do Until IsEmpty(Cells(i, “A”).Value)
MarkedPrice = Cells(i, “B”).Value + Cells(i, “C”).Value Cells(i, “D”).Value = MarkedPrice
count = count + 1
MsgBox “Totally ” & count & ” items processed.”, vbInformation, “Marked Price processed” End Sub
Introduction to Information Systems
Furthermore, we may incorporate input box to allow user inputs. For example:
Sub SellingPrice()
Dim i As Integer
Dim count As Integer
Dim Discount As Integer
Dim DisRate As Double
Dim SellPrice As Double
Discount = InputBox(“Enter the products’ discount (% off)”, “Discount”) Cells(1, “E”).Value = “Selling Price”
Cells(1, “F”).Value = Discount & “% off”
DisRate = 1 – Discount / 100
MsgBox Discount & “% off = ” & DisRate & ” discount rate.”, vbInformation, “Convert”
Do Until IsEmpty(Cells(i, “A”).Value)
SellPrice = Cells(i, “D”).Value * DisRate Cells(i, “E”).Value = SellPrice
count = count + 1
MsgBox “Totally ” & count & ” selling prices done.”, vbInformation, “Selling Price done”
In the Excel worksheet “TaskAnB”, design an interface to process the data in the table.
1. User should use input box to input the discount (in % off) of the products. The input will be
confirmed by a message box and then display in cell F1 of the table.
2. Calculate the Selling prices of the products. And store the value in appropriate cells.
3. Display a message box reporting the number of items processed at the end.
You may refer to the following variable list.
1. Discount (proper data type: Integer)
2. Selling price (proper data type: Double)
3. Counters i and count (proper data type: Integer)
Name your VBA program as TaskA().
(Hint: You will need 1 input box and 2 message boxes.)
Introduction to Information Systems
Conditional statements
When we want to process the data differently according to some conditions, we can use conditional statements If … Then and If … Then … Else.
Syntax of “If-then” and “If-then-else” statement:
If Condition_is_True Then
Statements_to_be_executed.
If Condition_is_True Then Statements_to_be_executed_when_True.
Statements_to_be_executed_when_False.
Here are some simple examples:
Sub VIP_Coupon()
Dim Membership As String
Dim CouponAmt As Integer
CouponAmt = 50
Membership = InputBox(“What is your membership?”, “Input membership”) If Membership = “VIP” Then
CouponAmt = CouponAmt * 4
MsgBox “You will get $” & CouponAmt & ” coupon.”, vbInformation, “Your coupon” End Sub
Sub About_Today()
Dim Weather As String
Dim Feeling As String
Weather = InputBox(“What is today¡¯s weather?”, “Input the weather”) If Weather = “Sunny” Then
Feeling = “Soooo Great!”
Feeling = “Not Bad.”
MsgBox “Today is ” & Weather & ” day. ” & Feeling, vbInformation, “All about today” End Sub
Sub Your_Age()
Dim Age As Integer
Dim About_You As String
Age = InputBox(“Enter your age”, “About your age”)
If Age < 25 Then
About_You = "Younger than UST!"
ElseIf Age < 65 Then
About_You = "Still a young guy/girl!"
About_You = "Stay young."
MsgBox "You are " & Age & " years young. " & About_You, vbInformation, "Age and you" End Sub
Introduction to Information Systems
Conditional statement within iterative statement:
Sub Classification()
Dim i As Integer
Dim Category As String
Dim CatCount As Integer
Dim NonCatCount As Integer
CatCount = 0
NonCatCount = 0
Category = InputBox("Enter the counting Category", "Find your Category") Do Until IsEmpty(Cells(i, "B").Value)
If Category = Cells(i, "A").Value Then
CatCount = CatCount + 1
NonCatCount = NonCatCount + 1
i=i+1 Loop
MsgBox "There are " & CatCount & " items for " & Category & ". And " & NonCatCount & " items for others.", vbInformation, "Final report"
In the Excel worksheet "TaskAnB", finish the followings making use of both iterative and conditional statements.
1. User input the Category first by input box. Copy the input category to an appropriate cell. And then the subroutine will go through the entries (records) in the product list table one by one.
(Hint: Using Do-Until Loop.)
2. If a record that matches the input category is found, copy the data (Product Name, Unit Price and Pricing based on) to appropriate cells next to the list. Also increment the item counter by 1.
3. If a record does not match the input category, increment another item counter by 1.
4. Finally, display a message box (or two separate message boxes) reporting the number of items
belong to and not belong to the input category respectively.
You may refer to the following variable list.
1. Category (proper data type: String)
2. Counters i, j, CatCount and NonCatCount (proper data type: Integer)
Name your VBA program as TaskB().
(Hint: You will need 1 input box and 1 message box.)
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com