APPL78387
Excel Macros and VBA Level 3 - Formula and Functions		 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: APPL78386
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 online course contains 7-8 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 best practices when creating Worksheet Formulas and Worksheet Functions. Students identify the similarities and differences between Worksheet and VBA Functions and the differences between VBA Function and VBA Sub (Macro) Procedures, including their Creation, Purpose and Execution. Through self-paced training including video training exercises, students examine VBA code constructs that can handle multiple conditions with procedures, functions from various Function Categories and create User Defined Functions (UDFs) that simplify their use. Students also obtain a reference to VBA code Statements and Functions. Important: Before attending this course, complete the video-based training found here: http://exceltrainers.com/beforevba3/

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 identify the purpose and creation of User Defined Functions (UDFs) through exercises that consider key Worksheet Functions. Students determine how UDFs can be created to simplify an end user's use of these functions.
 
Learning Outcomes:

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

  1. Explain the 4 Cell Reference methods for Worksheet Formula creation and Worksheet Function use.
  2. Identify keyboard shortcuts to show all Formulas in cells and how to select a Cell Reference option.
  3. Determine when to assign each of the 4 Cell Reference methods.
  4. Determine when a Name Range can be used in a formula.
  5. Determine where a UDF¿s code needs to be placed, within the VB* Editor, and what to do if it is missing.
  6. Use the critical code statement that a UDF requires to ensure that it returns a value to a cell.
  7. Determine whether or not code generated by the Macro Recorder can be used for a UDF.
  8. Identify the 2 ways that a UDF can be inserted into a cell.
  9. Identify the 7 points of comparison between a ¿Sub¿ procedure and a ¿Function¿ procedure.
  10. Determine the keyboard shortcuts and alternate Excel screen icon methods of executing a UDF.
  11. Use the 1 VBA Function and 1 Code construct recommended for multiple condition tests.
  12. Use Compound Logic Operators AND/OR for the creation of complex condition tests.
  13. Identify 3 major benefits derived from the use of UDFs.
  14. Identify the 2 ways that you can create end user Help for a UDF.
  15. Explain Boolean Data, Logical and Information Functions and how to use them within UDFs.
  16. Create a UDF to replace many separate formulas used to display data into date ranges.
  17. Use Text Manipulation Functions and Custom Date Formats in UDFs.
  18. Define Public and Private UDFs, identify when to use them and how to fix errors with Private UDFs.
  19. Create Fiscal Quarter & Year UDFs and how to PivotTable fields assigned these UDF values.
  20. Enable Flexible Date Filters by converting Dates into Text values, and then using Text Filters
  21. Create VLOOKUP functions ¿unbreakable¿ and simplify ¿VLOOKUP functionality¿ via UDFs.
  22. Use the SUBTOTAL function to make Data Filter Calculations, and simply its use via a UDF.
  23. Use a Reference for VBA Functions, and a List of Worksheet Functions available within VBA.

Evaluation Plan
Students demonstrate their learning in the following ways:

 Evaluation Plan: ONLINE
 Pass/No Pass100.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.

Week 1

1. Formula and Function Overview

  • Formulas and Functions in Excel’s Worksheet Environment
  • Formulas and Functions in Excel’s VBA Environment
  • Built-in Worksheet & VBA Functions versus User Defined Functions

Week 2

2. User Defined Functions (UDFs)

  • Compare Macro Creation & Execution to UDF Creation & Execution
  • Can the Macro Recorder Help in the Creation of Macros?
  • VBA Code Alternatives for The Processing of Multiple Conditions
  • The Three Benefits of UDFs and How to Add Help Information to UDFs


3a. Boolean Data, Logical and Information Functions and UDFs

  • Directly Create & Use Boolean Data, Plus the “IS” Worksheet Functions

Week 3

3b. Boolean Data, Logical and Information Functions and UDFs -- Continued

  • Compound Logic using AND / OR Worksheet Functions & UDF Simplification
  • Compound Logic for Aging Analysis via Worksheet Functions & UDF Simplification


4. Text Functions, Custom Date Formats and UDFs

  • Text Manipulation via Operators, Functions, plus Public & Private UDFs
  • Create Fiscal Quarter & Year UDF
  • Enhance Date Filters via the Worksheet TEXT Function & UDF Simplification

Week 4

5. Database Calculations Respond to Filtering via Worksheet SUBTOTAL & UDF Simplification

6. Database Lookups, Information/Reference Functions and UDFs

  • Make the Worksheet VLOOKUP Function “Unbreakable”
  • Transform a Specific Worksheet VLOOKUP Formula into an Object-oriented UDF
  • Use this Worksheet Specific “Formula UDF” to Create a Generic Object-oriented UDF

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.