Introduction to VBA Part 2: Recording and Modifying Macros
CIS 418
Macros
Macros are used for automate a repetitive operation in Excel. We would learn:
• • • •
How to record a macro
Where to find the macro so that it can be edited How to form a string with a reference to a cell How to create a loop with a macro
Simon Business School CIS-418 Ricky Roet-Green
2
Function Vs. Subroutine
Function
Subroutine
Can be called directly from the worksheet
Yes
No
Can change the value or format of a cell
No
Yes
Can accept arguments
Yes
Yes
Simon Business School CIS-418 Ricky Roet-Green
3
Task: Dropping three lowest homework scores in calculating a grade
File SampleStudentHWScores.xlsx has homework scores for 81 students. The task is to calculate total homework score for each student dropping the 3 lowest scores.
Simon Business School CIS-418 Ricky Roet-Green
4
A series of steps for each student
1. Copy and paste transposing the data
2. Sort the scores from largest to smallest (Data -> Sort)
3. Sum up the 15 highest scores
4. Copy the result as a value into a designated column
5. Now repeat while recording the Macro
Simon Business School CIS-418 Ricky Roet-Green
5
A series of steps for each student
1. Copy and paste transposing the data
Simon Business School CIS-418 Ricky Roet-Green
6
Second step: sort the scores
Simon Business School CIS-418 Ricky Roet-Green
7
Third step: sum up the 15 highest scores
Simon Business School CIS-418 Ricky Roet-Green
8
Fourth step: copy in the result as the total score
Simon Business School CIS-418 Ricky Roet-Green
9
Repeat using a Subroutine
To repeat these steps for 80 other students we can create a Subroutine. Start out recording the steps as a macro:
Simon Business School CIS-418 Ricky Roet-Green
10
Edit the macro
Once done with the steps, stop recording and go to edit the macro:
Simon Business School CIS-418 Ricky Roet-Green
11
Modify the macro to run the same operations for all the students
Simon Business School CIS-418 Ricky Roet-Green
13
You can modify the macro to work on a particular row
Simon Business School CIS-418 Ricky Roet-Green
14
Use loop to go over the rest of the rows
Simon Business School CIS-418 Ricky Roet-Green
15
Try it out!
Simon Business School CIS-418 Ricky Roet-Green
16
After running the Macro
Don’t forget to do a quality assurance check!
• Pick a couple rows at random and check the results
SUB is different from a FUNCTION
• •
If you change a score for one of the homework assignments, will the total score get updated automatically?
What if you implemented calculation of the total score as a user-defined function?
Simon Business School CIS-418 Ricky Roet-Green
17
Save the VBA code with the Excel file
Choose a correct file type if you want to save the VBA code with the Excel file
Simon Business School CIS-418 Ricky Roet-Green
18