Excel/VBA for Creative Problem Solving, Part 3 (Projects)

1 Peer Review

• Currency Converter Grading Rubric

5 Videos

• Introduction to the currency converter project
• How to run a data query
• Initializing the combo boxes and today's date
• Importing data from the currency website using proper date format
• Plotting the last 30 days

1 Readings

• Currency Converter - Problem Statement

1 Peer Review

• Geography Toolbox Grading Rubric

8 Videos

• Introduction to the Geography Toolbox project
• All about the "Geography Toolbox - STARTER.xlsm" file
• How to populate the first set of combo boxes
• How to populate the second set of combo boxes
• How to utilize the combo box selections
• How to calculate the distance between two points knowing latitude and longitude
• Expectations for input validation and error handling
• How to unhide and hide sheets

1 Readings

• Geography Toolbox - Problem Statement

1 Peer Review

• Ultimate Organizer

7 Videos

• Introduction to the Ultimate Organizer project
• A demonstration of the Ultimate Organizer
• How to add a category
• How to add a new record, Part 1
• How to add a new record, Part 2
• How to reveal hidden labels and text boxes
• How to add an icon to the Quick Access Toolbar to run your user form

1 Readings

• Ultimate Organizer - Problem Statement

1 Peer Review

• Regression Toolbox - Peer Review

12 Videos

• Introduction to the Regression Toolbox project
• Introduction to the "Regression Toolbox - STARTER.xlsm" file
• All about simple linear regression
• All about general linear regression
• (OPTIONAL): All about multilinear regression
• Matrix approach to regression, Part 1
• Matrix approach to regression, Part 2
• A more complex example of the matrix approach to regression
• Using Excel’s matrix functions in VBA code
• What is adjusted R-squared?
• Calculating adjusted R-squared
• Plotting experimental data and model predictions in VBA

1 Readings

• Regression Toolbox - Problem Statement

1 Peer Review

• Monte Carlo Simulation

10 Videos

• Introduction to Monte Carlo simulation
• Monte Carlo cookie example
• How to output a random variable that follows a certain distribution
• The uniform distribution
• The discrete distribution
• The normal distribution
• The triangular distribution
• The Beta-PERT distribution
• How to apply a VBA user form to a Monte Carlo simulation
• An overview of the profitability analysis spreadsheet

1 Readings

• Monte Carlo Simulation Problem Statement

1 Peer Review

• Grade Manager

13 Videos

• Introduction to the Grade Manager Project
• Creating a new folder inside a preexisting directory
• Importing the Roster.xlsx file to a new sheet
• Creating section rosters from main roster file, Part 1
• Creating section rosters from main roster file, Part 2
• Initializing column headings on section roster files
• Syncing files, Part 1
• Syncing files, Part 2
• How to create a dated backup file
• Adding a new grade item to all section sheets, Part 1
• Adding a new grade item to all section sheets, Part 2
• Adding a new grade item to all section sheets, Part 3
• Search and replace tool

1 Readings

• Grade Manager - Problem Statement