Introduction to Information Systems
Learning Outcomes
Lab 03: Advanced Business Analytics 1
1. Distinguish the relations between Excel Macros and Excel VBA.
Copyright By PowCoder代写 加微信 powcoder
2. Learn to construct VBA programs by Macro recording.
3. Learn to construct or modify VBA programs using VB Editor.
4. Identify the three ways to run VBA programs (or known as Macros).
5. Understand how VBA works with business applications.
Introduction
In this laboratory, you will learn VBA (Visual Basics for Applications) as an advanced way to reduce your time and workload on repeated tasks. More specifically, you will learn the various ways to construct and run a VBA program.
Task 1: Excel setting to enable Macros and VBA programs.
1. Select FILE > Options in Ribbon.
2. In Customize Ribbon, check Developer on the right hand side of the list box.
3. Click OK button to confirm.
4. Right-click on the status bar. Check Macro Recording on the popup menu.
Introduction to Information Systems
Task 2: Construct a VBA program by Macro Recording; Run a VBA program by Run Macros
By using Excel Macro Recording, user interactions will be recorded as VBA program code. And the VBA program (or known as Macro) can be replayed later. So it is very useful if the interactions have to be repeated many times. Now you are going to record a Macro that can plot a stock chart.
1. Open the file lab03_stock_slot.xlsm that can be downloaded from CANVAS.
There are two worksheets, namely 3988.HK and 0005.HK, we will record the steps on 3988.HK
worksheet and later we will replay the steps for 0005.HK.
2. Select 3988.HK worksheet.
3. Click Record Macro button ( ) in the status bar to begin the recording process.
4. Type My_First_Macro as the Macro name in the popup dialog box. And then click OK button to
5. Change the names in Row 1 as below:
i) Open → opening price ii) High → high price
iii) Low → low price
iv) Close → closing price
6. Select Columns A to E.
7. Click INSERT > Insert Line Chart
8. Click Stop Recording button ( ) in the status bar to finish the recording process.
Now, we are going to replay the steps. 9. Delete the chart.
10. Select DEVELOPER > Macros.
11. Choose My_First_Macro and click Run button to replay the Macro.
Public Sub Calculate_Change()
Dim i As Long
Cells(1, “H”).Value = “Change”
Do Until IsEmpty(Cells(i + 1, “G”))
Cells(i, “H”).Value = Cells(i, “G”).Value – Cells(i + 1, “G”).Value
i=i+1 Loop
Introduction to Information Systems
Task 3: Construct a VBA program by VB Editor; Run a VBA program by clicking a button
In this 1.
part, you are going to develop a VBA program that calculates the daily stock closing price change. On the same stock data worksheet, select DEVELOPER > Visual Basic in the Ribbon.
A new window pops up, and this window is called Visual Basic Editor (VBE). In VBE, you can create your own programming code for a button, list box, menu, etc.
In VBE, select Insert > Module in the menu.
Now you can develop a VBA program in the Module window. As a beginner, copy the following VBA program code in the VBE Module window:
Go back to the stock data worksheet window. Insert a Button to call the VBA program in Step 3.
i) Select DEVELOPER in Ribbon.
ii) Select Insert > Button.
iii) Use the mouse to drag a rectangular shape in order to create a button.
iv) Choose Calculate_Change and click OK button.
Click the button to run the VBA program. You will have the similar result below.
Introduction to Information Systems
Task 4: A Simple Game – Slot Machine
In this part, you are going to develop a simple slot machine game in VBA programming code. In this game, the user wins when s/he gets three identical pictures.
Sample outlook:
Instruction
1. Select the Slot Machine worksheet.
2. Copy the VBA program code (listed in the next page) to a Visual Basics Editor Module window.
3. Insert a button and assign the PlaySlotMachine VBA program to the button.
Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As LongPtr)
‘ Between each draw, stop (sleep) for some time to let people see the flashing
‘ effects.
Public Sub PlaySlotMachine()
‘Define the variable here
Dim num1 As Integer, num2 As Integer, num3 As Integer
Dim count As Integer
Randomize Timer
‘draw the slot machine for 50 times
For count = 1 To 50
‘ Randomly generate a number from 0-1 to num1 then *3 +1
num1 = Int(Rnd() * 3 + 1)
‘ If num1=1 then return happy face in cell B2, elseif num1=2
‘ then return normal face in cell B2. ‘
‘ All other value will return unhappy face in cell B2
If num1 = 1 Then
Cells(2, “B”).Value = “J”
ElseIf num1 = 2 Then
Cells(2, “B”).Value = “K”
Cells(2, “B”).Value = “L”
num2 = Int(Rnd() * 3 + 1)
If num2 = 1 Then
Cells(2, “C”).Value = “J”
ElseIf num2 = 2 Then
Cells(2, “C”).Value = “K”
Cells(2, “C”).Value = “L”
num3 = Int(Rnd() * 3 + 1)
If num3 = 1 Then
Cells(2, “D”).Value = “J”
ElseIf num3 = 2 Then
Cells(2, “D”).Value = “K”
Cells(2, “D”).Value = “L”
‘The closer to the end of the draw, the slower are the flashing effects
Sleep count * 4
Next count
‘If num1=num2=num3, then will display message box
If (num1 = num2 And num1 = num3) Then
MsgBox “Congratulation! You are so lucky today”
If you are using Mac version of Excel, please make the below two statements as comment: ‘Declare PtrSafe Sub Sleep Lib “kernel32” (ByVal dwMilliseconds As LongPtr)
‘Sleep count * 4
These two lines are used to perform slow animation in Windows version Excel.
Introduction to Information Systems
Introduction to Information Systems
Challenge Questions [Optional]
Q1. Try to modify the VBA program of the slot machine so that:
1. The display message will be different for 3 happy faces, 3 normal faces and 3 sad faces.
2. The program will display another message when all three pictures are different.
Q2. Do we have to modify the VBA program if we use 3 other different pictures?
Two ways to construct a VBA program (or known as Macro)
1. Macro recording, the steps involved will be recorded as a VBA program.
2. Write the codes within Visual Basics Editor.
Three ways to run a VBA program (or known as Macro)
1. Select the intended VBA program from the Macro Menu and click RUN. (As in Task 2.)
2. Assign the intended VBA program to a button first, then click that button. (As in Tasks 3 and 4.)
3. In Visual Basics Editor, place the cursor within the intended VBA program code. Then click the
Editor’s play button.
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com