APPL78385
Excel Macros and VBA Level 1 - Data Input
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: N/A
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 hours of video training. An average of 25% of 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 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 Excel features that increase the speed and accuracy of data input. Students use the Macro Recorder to create "unbreakable" Macro code that navigates a workbook and input data into cells. Through self-paced training videos, students use the VB* Editor to examine and modify generated VBA* code and create an Input Form with Buttons and User Messages to control the input to and update of a sheet database.

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 Macro (VBA*) codes that avoid run-time errors (which result from modifications) by creating worksheet and macro components that combine to build a Database Update Application. Students employ best practices using Excel's Macro Recorder.
 
Learning Outcomes:

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

  1. Utilize Excel's AutoFill to speed data input.
  2. Use Excels Data Validation feature to restrict a cells input to a list of valid values.
  3. Identify two ways to expand these valid values Static and Dynamic assignment of list ranges.
  4. Record a Macro, run the Macro and examine its generated VBA code.
  5. Differentiate between Absolute and Relative Reference Macros and how to create them.
  6. Identify Macro security settings and how to save and open files that contain Macros.
  7. Differentiate between a Local and Global Macro and how to record code for each type.
  8. Share macros with other users and describe the pros/cons of sharing via a network.
  9. Create messages that inform users of processing done, or ask if its OK to run a macro.
  10. Identify a Macro with (Breakable Code) and create an Unbreakable code.
  11. Describe best practices that result in non-breakable Macros that work on copied worksheets.
  12. Create and use Worksheet Buttons to simplify end user execution of a macro.
  13. Describe the functions of the VB (Visual Basic) Editor Screen and Management of VBA Project Modules.
  14. Differentiate between creation and use of a (Sheet Form) and VBA's User Form for data input.
  15. Apply Data Validation to the creation of an Input Form with invalid data Error Messages.
  16. Create a Macro that transfers data from an input form to a separate database sheet.
  17. Create Macros to Clear the input form and save and close the workbook.
  18. Practice the use of User Messages prior to and at the end of the execution of macros.
  19. Hide and protect sheets, workbooks and VBA Code from unauthorized changes.
  20. Enhance the (Database Update Application), so that: If input fields are flagged as Mandatory, then the user is informed of any missing inputs. If the Database Sheet has misaligned columns, then new records cannot be added to it. If needed, after a new record is added to the Database Sheet, this last Add can be undone.
  21. Create a Backwards Compatible Macro to skip features not available in earlier versions.

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 upon 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. Streamline Input by using Excel’s “Data Entry” Features

  • Use AutoFill to Create a Data Series or Quickly Copy Data
  • Use Data Validation to Prevent or Warn of Invalid Input Data

2. Record a Macro, Run a Macro, Save/Open Macros and Macro Security

  • Record a Macro, Run it and Examine its Code
  • Macro Comments and Macro Run Options
  • Absolute or Relative Reference Macros
  • Save/Open a Macro and Security Settings

Week 2

3. a) Global Macros, Message Boxes, Worksheet Buttons, Non-Breakable Code

  • A Global Macro is One That is Executable From Any Workbook
  • Record a Global Macro to Your “Personal Macro Workbook”
  • Create User Interface Message Boxes using the VBE
  • Identify “Breakable Code” and Make it “Unbreakable”

Week 3

3. b) Non-Breakable Code (Continued) + The Visual Basic Editor (VBE)

  • Create Non-Breakable Macros via Relative Reference and Appropriately Scoped Range Names
  • Use Worksheet Buttons to Simplify Macro Execution
  • Explore the VBE’s Screen Components and VBA Project Modules

4. a) Begin Development of a Database Update Application

  • See a Demo of Database Application
  • Compare “Sheet Form” to “User Form”
  • Create Input Form with Data Validation of Fields

Week 4

4. b) Complete Develop a Database Update Application and Protect What’s Created

  • Create Macros that Transfer Input Form Data to a Database Sheet
  • Create “Clear Form” as well as “Save and Close File” Macros
  • Add Enhancements to the Application including an Option to Undo Any New Record
  • Protect the Application by Hiding Macros & Sheets, Protecting Sheets, Workbook & VBA Code


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.