This course is part of the Excel/VBA for Creative Problem Solving Specialization

4.9
stars
1,244 ratings

Charlie Nuttelman

36,188 already enrolled

Offered By

Excel/VBA for Creative Problem Solving SpecializationUniversity of Colorado Boulder

About this Course

21,507 recent views

"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.

Flexible deadlines

Reset deadlines in accordance to your schedule.

Shareable Certificate

Earn a Certificate upon completion

100% online

Start instantly and learn at your own schedule.

Coursera Labs

Includes hands on learning projects.

Learn more about Coursera Labs

Course 2 of 3 in the

Excel/VBA for Creative Problem Solving Specialization

Intermediate Level

Approx. 20 hours to complete

English

Could your company benefit from training employees on in-demand skills?

Try Coursera for Business

Flexible deadlines

Reset deadlines in accordance to your schedule.

Shareable Certificate

Earn a Certificate upon completion

100% online

Start instantly and learn at your own schedule.

Coursera Labs

Includes hands on learning projects.

Learn more about Coursera Labs

Course 2 of 3 in the

Excel/VBA for Creative Problem Solving Specialization

Intermediate Level

Approx. 20 hours to complete

English

Could your company benefit from training employees on in-demand skills?

Try Coursera for Business

Instructor

Instructor rating

4.89/5 (254 Ratings)

Charlie Nuttelman

Senior Instructor

Chemical and Biological Engineering

350,966 Learners

6 Courses

Offered by

University of Colorado Boulder

See how employees at top companies are mastering in-demand skills

Learn more about Coursera for Business

Syllabus - What you will learn from this course

Content Rating95%(2,605 ratings)

Week1

Week 1

6 hours to complete

Arrays and Array Functions

In Week 1, you will learn all about arrays and array functions. You will learn how to iterate through arrays using nested For...Next loops and For Each...Next statements; you will learn how to import data to VBA from Excel and export data to Excel from VBA; you will learn how to create user-defined array functions; and you will learn about the ReDim Preserve statement for resizable arrays. Week 1 is concluded with a quiz, which unlocks Assignment 1.

6 hours to complete

14 videos (Total 93 min), 10 readings, 2 quizzes

14 videos

What you will learn in this course7mHow the course works4mHow To Switch Sessions of the Course1mIntroduction to arrays9mLocal arrays in VBA5mImporting/Exporting arrays from/to Excel8mUsing arrays in subroutines and functions8mUser-defined array functions6mExample 1: SortVector array function and ksmallest8mExample 2: Extracting diagonal elements from a square matrix4mExample 3: Residuals of simple linear regression8mReDim Preserve8mExample: ReDim Preserve8mAssignment 1 preview and instructions1m

10 readings

For Mac users1mSubscribe to "Everyday Excel VBA" YouTube Channel!2mThe importance of a Course Certificate and the future of higher education5mHow to get help2mOpening macro-enabled workbooks10m(OPTIONAL) Will Office Scripts replace VBA?10mRemember to use your DEBUGGING skills!10mQuiz 1 solutions and explanations10mAssignment 12hWeek 2 STARTER file (1 file)

2 practice exercises

Week 1 Quiz20mAssignment 1 submission30m

Week2

Week 2

5 hours to complete

Working with strings and .txt files

Week 2 deals with text strings and text (.txt) files. You will learn about the string functions in Excel and VBA. You will gain practice with string functions as you learn how to create functions in VBA that utilize string functions. You will learn how to extract strings, such as email addresses, from a spreadsheet of mixed string formats. You will also learn how to write information from Excel to .txt files and also to import information in .txt files to Excel. The module is concluded with a quiz and Assignment 2.

5 hours to complete

12 videos (Total 84 min), 3 readings, 2 quizzes

12 videos

How to use string functions in Excel8mExample: How to create email addresses from Last Name, First Name format7mHow to use string functions in VBA8mExample: Using string functions in VBA4mExample: Extracting email addresses from mixed string formats8mExample: VBA array function for separating strings into component parts8mExporting data from Excel to .txt files6mImporting data from .txt files8mImporting data from tab-delimited .txt files2mExample: Morse coder9mExample: Morse decoder8mAssignment 2 preview and instructions1m

3 readings

Quiz 2 solutions and explanations10mAssignment 22hWeek 3 STARTER files (11 files)

2 practice exercises

Week 2 Quiz20mAssignment 2 submission30m

Week3

Week 3

5 hours to complete

Iterating through worksheets and workbooks

Week 3 is all about iterating through (automating) worksheets and workbooks. You will learn how to consolidate information from multiple worksheets into a single worksheet and you will learn how to automatically consolidate information found within multiple workbooks into a single worksheet of a single workbook. The automation principles you learn in this module are priceless. Week 3 is concluded with a quiz and a very challenging assignment.

5 hours to complete

12 videos (Total 80 min), 2 readings, 2 quizzes

12 videos

All about worksheets8mIterating through worksheets7mConsolidating information in multiple worksheets into a single worksheet4mExample: Counting total number of 7's in all worksheets of a workbook5mPutting it all together: Consolidating employee schedules in multiple worksheets9mAll about workbooks9mOpening workbooks8mExample: Importing and consolidating data from multiple files6mExample: Counting 7's in multiple workbooks5mPutting it all together: Consolidating employee schedules8mAssignment 3 preview and instructions2mHow to select a range using the input box method1m

2 readings

Assignment 32h 30mWeek 4 STARTER files (5 files)

2 practice exercises

Week 3 Quiz20mAssignment 3 submission30m

Week4

Week 4

5 hours to complete

User forms and advanced user input/output

In Week 4, you will learn about advanced input and message boxes and event handlers. You will learn how to interface with the user in a professional manner using user forms that validate input and prevent the "debug" window and VBE from appearing. You will also learn how to place combo boxes on user forms and populate those combo boxes with information on the spreadsheet. Finally, you will learn how to put it all together and implement a solving technique (bisection method) to solve a real world problem in a user form. Week 4 is concluded with a quiz, which unlocks Assignment 4.

5 hours to complete

14 videos (Total 88 min), 1 reading, 2 quizzes

14 videos

Advanced input boxes7mAdvanced message boxes6mEvent handlers4mIntroduction to user forms3mCreating your first user form10mExample: Fuel efficiency user form7mExample: Tank volume user form6mDim'ming (or not Dim'ming) variables in user forms1mInput validation in user forms6mIntroduction to combo boxes, Part 15mIntroduction to combo boxes, Part 26mExample: Periodic table user form8m(OPTIONAL) Putting it all together: Conversion Solver user form10mAssignment 4 preview and instructions2m

1 reading

Assignment 42h 30m

2 practice exercises

Week 4 Quiz20mAssignment 4 submission30m

Reviews

4.9

324 reviews

5 stars
89.72%
4 stars
8.42%
3 stars
1.04%
2 stars
0.32%
1 star
0.48%

TOP REVIEWS FROM EXCEL/VBA FOR CREATIVE PROBLEM SOLVING, PART 2

by DLJun 9, 2021

Great course! I feel confident in my ability to develop, structure, and troubleshoot my code after taking this course. I look forward to solidifying the foundations built here in part 3.

by AGJul 7, 2020

It was a wonderful course and many of its learning can be applied straight to my project. I would love if it has increased the amount of time for the event handler topic.

by CCDec 5, 2020

Very interesting and practical course. Good for daily engineering application and tedious administrative work. Been enjoying Charlie's teaching a lot too! Highly recommended.

by BTFeb 8, 2020

I benefited from the course both in terms of utilizing VBA and programming in general. I used to use merely record macro and a few do loops. Now my programming prowess is much deeper.

View all reviews

About the Excel/VBA for Creative Problem Solving Specialization

This Specialization is for learners wishing to dramatically change the way that they use Excel spreadsheets by unleashing the power to automate and optimize spreadsheets using Visual Basic for Applications (VBA). The first two courses will teach learners the basics of VBA through the use of dozens of educational screencasts and a series of quizzes and in-application programming assignments. Finally, in Part 3 of the course, learners will complete 3 "real world" and somewhat open ended projects, which are graded through peer review.

Frequently Asked Questions

When will I have access to the lectures and assignments?
Access to lectures and assignments depends on your type of enrollment. If you take a course in audit mode, you will be able to see most course materials for free. To access graded assignments and to earn a Certificate, you will need to purchase the Certificate experience, during or after your audit. If you don't see the audit option:
The course may not offer an audit option. You can try a Free Trial instead, or apply for Financial Aid.
The course may offer 'Full Course, No Certificate' instead. This option lets you see all course materials, submit required assessments, and get a final grade. This also means that you will not be able to purchase a Certificate experience.
What will I get if I subscribe to this Specialization?
When you enroll in the course, you get access to all of the courses in the Specialization, and you earn a certificate when you complete the work. Your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile. If you only want to read and view the course content, you can audit the course for free.
Is financial aid available?
Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.

More questions? Visit the Learner Help Center.

Build employee skills, drive business results

Discover Coursera for Business

Excel/VBA for Creative Problem Solving, Part 2

About this Course