University of Colorado Boulder - Excel/VBA for Creative Problem Solving, Part 2
- Offered byCoursera
Excel/VBA for Creative Problem Solving, Part 2 at Coursera Overview
Duration | 20 hours |
Start from | Start Now |
Total fee | Free |
Mode of learning | Online |
Difficulty level | Intermediate |
Official Website | Explore Free Course  |
Credential | Certificate |
Excel/VBA for Creative Problem Solving, Part 2 at Coursera Highlights
- This Course Plus the Full Specialization.
- Shareable Certificates.
- Graded Programming Assignments.
Excel/VBA for Creative Problem Solving, Part 2 at Coursera Course details
- "Excel/VBA for Creative Problem Solving, Part 2" builds off of knowledge and skills obtained in "Excel/VBA for Creative Problem Solving, Part 1" and is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA).
- In Part 2 of the course, learners will: 1) learn how to work with arrays and import/export arrays from/to Excel using VBA code; 2) learn how to work with text strings and write data to .txt files and import information from .txt files; 3) automate the import, modification, and consolidation of information from multiple worksheets into a central worksheet as well as the import of information from multiple workbooks to a central workbook; and 4) gain experience with creating professional user forms to interface with the user, perform advanced calculations, and manipulate data on the spreadsheet.
- Learners who have a foundational understanding of VBA code and programming structures can jump right into Part 2 of the course without taking Part 1 and use the screencasts in Part 1 as reference.
- Each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.
Excel/VBA for Creative Problem Solving, Part 2 at Coursera Curriculum
Arrays and Array Functions
What you will learn in this course
How the course works
Introduction to arrays
Local arrays in VBA
Importing/Exporting arrays from/to Excel
Using arrays in subroutines and functions
User-defined array functions
Example 1: SortVector array function and ksmallest
Example 2: Extracting diagonal elements from a square matrix
Example 3: Residuals of simple linear regression
ReDim Preserve
Example: ReDim Preserve
Assignment 1 preview and instructions
For Mac users
The importance of a Course Certificate and the future of higher education
Remember to use your DEBUGGING skills!
Quiz 1 solutions and explanations
Assignment 1
Week 2 STARTER file (1 file)
Week 1 Quiz
Assignment 1 submission
Working with strings and .txt files
How to use string functions in Excel
Example: How to create email addresses from Last Name, First Name format
How to use string functions in VBA
Example: Using string functions in VBA
Example: Extracting email addresses from mixed string formats
Example: VBA array function for separating strings into component parts
Exporting data from Excel to .txt files
Importing data from .txt files
Importing data from tab-delimited .txt files
Example: Morse coder
Example: Morse decoder
Assignment 2 preview and instructions
Quiz 2 solutions and explanations
Assignment 2
Week 3 STARTER files (11 files)
Week 2 Quiz
Assignment 2 submission
Iterating through worksheets and workbooks
All about worksheets
Iterating through worksheets
Consolidating information in multiple worksheets into a single worksheet
Example: Counting total number of 7's in all worksheets of a workbook
Putting it all together: Consolidating employee schedules in multiple worksheets
All about workbooks
Opening workbooks
Example: Importing and consolidating data from multiple files
Example: Counting 7's in multiple workbooks
Putting it all together: Consolidating employee schedules
Assignment 3 preview and instructions
How to select a range using the input box method
Assignment 3
Week 4 STARTER files (5 files)
Week 3 Quiz
Assignment 3 submission
User forms and advanced user input/output
Advanced input boxes
Advanced message boxes
Event handlers
Introduction to user forms
Creating your first user form
Example: Fuel efficiency user form
Example: Tank volume user form
Dim'ming (or not Dim'ming) variables in user forms
Input validation in user forms
Introduction to combo boxes, Part 1
Introduction to combo boxes, Part 2
Example: Periodic table user form
(OPTIONAL) Putting it all together: Conversion Solver user form
Assignment 4 preview and instructions
Assignment 4
Week 4 Quiz
Assignment 4 submission
Excel/VBA for Creative Problem Solving, Part 2 at Coursera Admission Process
Important Dates
Other courses offered by Coursera
Student Forum
Anything you would want to ask experts?