Course Highlights
  • Utilize VBA to define and implement custom user-defined functions
  • Create macros to automate procedures in Excel
  • Identify the basic programming structures in VBA
  • Automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems
Curriculum

34 Topics
Welcome!
What can you do with Excel/VBA?
How the course works
How to switch sessions of the course
Week 1 preview
Getting your feet wet
Saving your files as macro-enabled workbooks
Recording basic macros
Absolute vs. relative referencing during macro recording
Overview of procedures in VBA
Why use Option Explicit?
Declaration of variables data types and scope of variables
How to troubleshoot when your code is not working properly
Assignment 1 preview and instructions
For Mac users
The power of Excel/VBA
The importance of a Course Certificate and the future of higher education
Auditing the course vs. purchasing a Course Certificate
(OPTIONAL) Will Office Scripts replace VBA?
Course improvement and my philosophy on learning
If an assignment is not showing up for you
How to get help
VBA Tutorial
Opening macro-enabled workbooks
Week 1 files
Need to improve your Excel skills?
Assignment 1
Week 2 files
Unlock quiz for Week 1 files
Week 1 Quiz
Assignment 1 Submission
What about you?!
(OPTIONAL) Week 1 reflection
Assignment 1 Discussion

17 Topics
Week 2 preview
VBA syntax and expression entry: Part 1
VBA syntax and expression entry: Part 2
A warning about exponentiation and ampersands
Introduction to objects properties methods and events
Common objects properties and methods
How to borrow functions from Excel
Examples Part 1: Basic input and output in subroutines
Examples Part 2: Basic input and output in subroutines
Examples Part 3: Basic input and output in subroutines
Assignment 2 preview and instructions
Assignment 2
Week 3 files
Week 2 Quiz
Assignment 2 submission
(OPTIONAL) Week 2 reflection
Assignment 2 Discussion

16 Topics
Week 3 preview
How to make your own user-defined function
How to convert a user-defined function to an Excel Add-In
How to troubleshoot your user defined functions
Why you don't use input and message boxes in functions
How to handle basic user error in your subroutines
An introduction to modular programming
Running a subroutine that resides in another file
Assignment 3 preview and instructions
Remember to use your DEBUGGING skills!
Assignment 3
Week 4 files
Week 3 Quiz
Assignment 3 submission
(OPTIONAL) Week 3 reflection
Assignment 3 Discussion

21 Topics
Week 4 preview
How to implement One-Way If...Then selection structures
How to implement Two-Way If...Then selection structures
All about the Multi-Alternative If...Then
Variable iteration loops (Do...Loops)
Validating user input using a Do...Loop
Creating a guessing game using a Do...Loop
All about fixed iteration (For...Next) loops
Iterating through a Selection vs. a Range
Putting it all together: Example 1
Putting it all together: Example 2
Example: Highlighting max and min values in a range
Using the For Each... Next statement
Worked mini-project: Searching through high and low temperatures in an Excel workbook
Assignment 4 preview and instructions
Assignment 4
Week 5 files
Week 4 Quiz
Assignment 4 submission
(OPTIONAL) Week 4 reflection
Assignment 4 Discussion

16 Topics
Week 5 preview
Filtering basics
Filtering for multiple criteria: Part 1
Filtering for multiple criteria: Part 2
Highlighting or deleting rows that satisfy a certain criterion
Sorting basics
Removing duplicates
Example: Removing duplicates
Putting it all together: Cleaning data
Assignment 5 preview and instructions
Assignment 5
Week 6 files
Week 5 Quiz
Assignment 5 submission
(OPTIONAL) Week 5 reflection
Assignment 5 Discussion

12 Topics
Week 6 preview
When macro recording fails
What is R1C1 style?
The Cells property
The Resize property
Example 1: R1C1 style
Example 2: R1C1 style
Assignment 6 preview and instructions
Assignment 6
Week 6 Quiz
Assignment 6 submission
(OPTIONAL) Week 6 reflection

  Write a Review

Excel/VBA for Creative Problem Solving, Part 1

Go to Free Course