APPL78386
Excel Macros and VBA Level 2 - Data Analysis		 Sheridan
 
  I: Administrative Information   II: Course Details   III: Topical Outline(s)  Printable Version
 

Land Acknowledgement

Sheridan College resides on land that has been, and still is, the traditional territory of several Indigenous nations, including the Anishinaabe, the Haudenosaunee Confederacy, the Wendat, and the Mississaugas of the Credit First Nation. We recognize this territory is covered by the Dish with One Spoon treaty and the Two Row Wampum treaty, which emphasize the importance of joint stewardship, peace, and respectful relationships.

As an institution of higher learning Sheridan embraces the critical role that education must play in facilitating real transformational change. We continue our collective efforts to recognize Canada's colonial history and to take steps to meaningful Truth and Reconciliation.
Section I: Administrative Information
  Total hours: 10.0
Credit Value: 0.0
Credit Value Notes: N/A
Effective: Fall 2019
Prerequisites: APPL78385
Corequisites: N/A
Equivalents: N/A
Pre/Co/Equiv Notes: N/A

Program(s): Excel Macros & VB for Apps
Program Coordinator(s): N/A
Course Leader or Contact: N/A
Version: 20190903_00
Status: Approved (APPR)

Section I Notes: This course contains 7-8 runtime hours of video training. An average of 25% additional time will be required for pausing, rewinding and replaying the videos. Students will need approximately 10 hours to complete the training (2-3 hours per week) over the course duration of 4 weeks. This course is self-paced, and contains bonus materials, as well as follow-up support.
 
 
Section II: Course Details

Detailed Description
Students explore how to automate Data Consolidation of files that have a "standardized (uniform) structure and content" as well as files that do not. Students automate PivotTable analysis, Data Filters, Sorts and Subtotals, as well as the Creation of Charts. Through self-paced training including video training exercises and a number of data analysis exercises, students create macros for these features and learn to dynamically recognize the database row growth and column structure changes. Students explore how to accept user inputs that control what a macro will do and to "trap run-time errors" caused by unexpected user inputs.

Program Context

 
Excel Macros & VB for Apps Program Coordinator(s): N/A
This course is part of the Excel Macros and Visual Basic for Applications Recognition of Achievement.


Course Critical Performance and Learning Outcomes

  Critical Performance:
By the end of this course, students will have demonstrated the ability to generate a macro code, free of errors, for Excel Data Analysis and incorporate special VBA* code tools (that are designed to handle processing not possible through code) generated by macro recording.
 
Learning Outcomes:

To achieve the critical performance, students will have demonstrated the ability to:

  1. Create (permanent links) between different files that allows for source file changes.
  2. Differentiate between (permanent) and (current period) Consolidation Links between files.
  3. Handle the selection of a column of data that may be one of many columns within a list.
  4. Describe how the use of a VB Editor Option can greatly help in eliminating variable name code errors.
  5. Describe importance of Macro Recording Planning and (testing the plan) prior to macro recording.
  6. Identify how use of Range Names can lead to run-time errors and how to avoid the error.
  7. Explain how use of a Summary File's Name can cause a run-time error and how to avoid the error.
  8. Identify best practices to handle the opening of files to avoid run-time error conditions.
  9. Describe how (real-time) methods for File Open or Switching can cause errors and how to avoid them.
  10. Describe how a (real-time) method for Pasting Data can cause an error and how it can be avoided.
  11. Select a special VBA tool that eliminates potential File Path errors on the open of files.
  12. Transform file specific code into generic code that can be used to process multiple files.
  13. Use an InputBox command to accept user input that determines what a macro will do.
  14. Incorporate macro Error Trapping Code to cause run-time errors to become (user friendly).
  15. Differentiate between Elementary and Enhanced Error Trapping and what's needed for each.
  16. Use Excel's built-in feature to automate the consolidation of a set of non-standardized files.
  17. Filter a data list and show the Filter Status by using Option Buttons and the Advanced Filter.
  18. Use a special VBA tool to enable the generation of code for multiple PivotTable analysis.
  19. Automate the sort and subtotal of a PivotTable, as well as doing the same on source data.
  20. Generate code for the use of a Chart Template for the creation of a chart.
  21. Generate code for the automation of a chart that uses a Template.

Evaluation Plan
Students demonstrate their learning in the following ways:

 Evaluation Plan: ONLINE
 Pass/ Fail Evaluation100.0%
Total100.0%

Evaluation Notes and Academic Missed Work Procedure:
A 'satisfactory' grade is received for the completion of all the training exercises.

Provincial Context
The course meets the following Ministry of Colleges and Universities requirements:

 

Prior Learning Assessment and Recognition
PLAR Contact (if course is PLAR-eligible) - Office of the Registrar

  • Not Eligible for PLAR
 
 
Section III: Topical Outline
Some details of this outline may change as a result of circumstances such as weather cancellations, College and student activities, and class timetabling.
Instruction Mode: Online
Professor: N/A
Resource(s):
 TypeDescription
RequiredSoftwareWindows version of Microsoftİ Excel (2007 or higher)

Applicable student group(s): Continuing Education Students.
Course Details:

Training is self-paced. Students can view and complete video training exercises at whatever speed they choose.  The 4 week schedule shown below is only a suggested training plan.  A 'satisfactory' grade is received for the completion of all the training exercises.

Week 1

1. a) Automate Consolidation of “Standardized” Lists

  • Create “Permanent Links” to Cells in External Workbooks
  • Reduce VBA Code Errors by using “Option Explicit” and “Dim”
  • Consolidation Part 1 – Introduction to Consolidatiion of Multiple Files
  • Consolidation Part 2a – Start a Macro Recording Plan (to process 1 file)
  • Consolidation Part 2b – Continue the Macro Recording Plan
  • Consolidation Part 3 – Record the Macro and Tweak Code

Week 2

1. b) Automate Consolidation of “Standardized” Lists (Continued)

  • Consolidation Part 4 – Dealing with File Open Path
  • Consolidation Part 5 – Create Generic Code and Loop (to process multiple files)
  • Consolidation Part 6 – Enhance the Macro with User Input that Controls the Loop
  • Consolidation Part 7 – Enhance the Macro with User Friendly Error Messages

Week 3

2. Automate Consolidation of “Non-Standardized” Lists

  • Consolidate Non-Standardized Files – Part 1
  • Consolidate Non-Standardized Files – Part 2

3. Automate Filters and Show Filter Status

  • Automate an AutoFilter to Show Filter Status by using Button Buttons
  • Automate an AutoFilter to Show Filter Status by using Option Buttons
  • Automate an Advanced Filter to Show Filter Status with Button Buttons

Week 4

4. Automate PivotTables

  • Automation of PivotTables – Part 1
  • Automation of PivotTables – Part 2

5. Automate Sort and Subtotals

  • Automate PivotTable Sort and Subtotals
  • Automate Source Data (Non-PivotTable) Sort

6. Automate Chart Creation

  • Automate Chart Creation – Part 1
  • Automate Chart Creation – Part 2

Sheridan Policies

It is recommended that students read the following policies in relation to course outlines:

  • Academic Integrity
  • Copyright
  • Intellectual Property
  • Respectful Behaviour
  • Accessible Learning
All Sheridan policies can be viewed on the Sheridan policy website.

Appropriate use of generative Artificial Intelligence tools: In alignment with Sheridan's Academic Integrity Policy, students should consult with their professors and/or refer to evaluation instructions regarding the appropriate use, or prohibition, of generative Artificial Intelligence (AI) tools for coursework. Turnitin AI detection software may be used by faculty members to screen assignment submissions or exams for unauthorized use of artificial intelligence.

Course Outline Changes: The information contained in this Course Outline including but not limited to faculty and program information and course description is subject to change without notice. Nothing in this Course Outline should be viewed as a representation, offer and/or warranty. Students are responsible for reading the Important Notice and Disclaimer which applies to Programs and Courses.
[ Printable Version ]

Copyright © Sheridan College. All rights reserved.