Course Description

This exceptional course is aimed at all students who plan to attend college and/or enter the workforce after graduation. We will work with applications such as the Microsoft Office Suite.  Most people are unaware of the hidden and powerful capabilities within these applications that boost productivity far beyond anything one can accomplish by mere manual efforts. These capabilities are unlocked through the native programming language for these applications – Microsoft’s Visual Basic for Applications (VBA).

 

Prerequisites

1. Some computer programming. Programming Catholic Games with Python (2-D) (available live Fall 2019) or Computer Programming 101 (available as a recorded course through Unlimited Access) or Introduction to Computer Science (available live or recorded Fall 2019) would provide sufficient prerequisite experience. All programming will be performed using VBA.

2. General familiarity with computers including the ability to open applications, use menu-driven commands, and type using the keyboard.

3. Recommended software: the Microsoft Suite of productivity applications (Excel, Word, PowerPoint, etc.) will need to be installed in a Windows-based operating system.

4. The “OpenOffice” software which often serves as an alternative to the Microsoft Office suite does operate on a programming language that is based on Microsoft Office VBA code. Some code may be easily transferable, other code may need to be tweaked to work with OpenOffice. Students with this setup would have to independently resolve compatibility issues since the instructor does not have the same setup to offer any personal assistance. Completing some assignments may not be possible, however if this occurs then it would be at no penalty to the student.

5. Mac users are not discouraged from enrolling in the class, however it should be noted that “Office for Mac” has limited capabilities. Some VBA programs will work in “Office for Mac” while other functionality such as customized user interface controls will not. Students with this setup would have to independently resolve compatibility issues since the instructor does not have the same setup to offer any personal assistance. Completing some assignments may not be possible, however if this occurs then it would be at no penalty to the student.

6. Middle-school students are not discouraged from taking this class if they already have a firm familiarity with the Microsoft suite of applications and the programming experience noted in prerequisite #1. No complex mathematics will be covered beyond counts, averages, max/min, and similar basic math functions.

​

Course Outline

Topics subject to minor changes. Topics will be interspersed throughout the lectures and will span multiple weeks.

• Application overview for common Microsoft Office applications (Excel, Word, PowerPoint, and more)

• The most critical and common elements of the VBA object libraries

• How to program one Microsoft Office application to control 1 or more other Microsoft Office applications

• Utilizing the many available “trigger” events and how to avoid inadvertently triggering others at the wrong time

• Small yet helpful functions to handle large datasets (grouping, sorting, unique values, bulk data exchanges, etc.)

• Learning the fundamentals of an Agile-like lean development process

• Principles of good user interface design and how to build them in VBA

• Creating custom-menus and ribbon-buttons that enhance the user experience and stay with the file

• An introduction to data science methodologies and how they are facilitated by the automated analysis and reporting of results

• How to create “templates” in Excel, Word, and PowerPoint that the code will manipulate to generate the automated exports

• Being mindful of the future: Knowing when and how to make your programs more robust to handle the natural evolution of business process so that those reasonable changes don’t “break” your programs

• Working with relational datasets (one-to-one, one-to-many, and many-to-many)

• VBA in MS Access to create dynamic SQL queries and other cool things not easily found in Access

• Emulating the capabilities of other expensive software applications (e.g. Tableau ™)

• Keeping the user informed of the progress being made during the automation process

​

Course Materials

All course materials are to be provided by the professor. Software to be installed – refer to the prerequisite information regarding software on Windows and Mac operating systems. Reference books will be recommended by the instructor, however, there is no obligation to purchase them and students will do well without them with all the free resources provided by the professor and via the Microsoft developer website.

​

Homework

Computer-generated quizzes, at-home analytical exercises, and exploration of methodologies applied towards items of personal interest. Students can expect 2-6 hours of studies outside of class depending upon their proficiency with programming. More time is encouraged, if available, to allow for personal creativity and exploration to take place using the knowledge being gained. Programming challenges where concepts are introduced but students need to be creative with their solution will be optional but rewarding assignments.