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

4.9
stars
1,211 ratings

Charlie Nuttelman

34,560 already enrolled

Offered By

Excel/VBA for Creative Problem Solving SpecializationUniversity of Colorado Boulder

About this Course

30,546 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.

Course 2 of 3 in the

Excel/VBA for Creative Problem Solving Specialization

Intermediate Level

Approx. 21 hours to complete

English

Subtitles: Arabic, French, Portuguese (European), Italian, Vietnamese, German, Russian, English, Spanish

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.

Course 2 of 3 in the

Excel/VBA for Creative Problem Solving Specialization

Intermediate Level

Approx. 21 hours to complete

English

Subtitles: Arabic, French, Portuguese (European), Italian, Vietnamese, German, Russian, English, Spanish

Instructor

Instructor rating

4.89/5 (245 Ratings)

Charlie Nuttelman

Senior Instructor

Chemical and Biological Engineering

323,875 Learners

6 Courses

Offered by

University of Colorado Boulder

CU-Boulder is a dynamic community of scholars and learners on one of the most spectacular college campuses in the country. As one of 34 U.S. public institutions in the prestigious Association of American Universities (AAU), we have a proud tradition of academic excellence, with five Nobel laureates and more than 50 members of prestigious academic academies.

Syllabus - What you will learn from this course

Content Rating95%(2,514 ratings)

Week

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), 8 readings, 2 quizzes

14 videos

What you will learn in this course7m

How the course works4m

How To Switch Sessions of the Course1m

Introduction to arrays9m

Local arrays in VBA5m

Importing/Exporting arrays from/to Excel8m

Using arrays in subroutines and functions8m

User-defined array functions6m

Example 1: SortVector array function and ksmallest8m

Example 2: Extracting diagonal elements from a square matrix4m

Example 3: Residuals of simple linear regression8m

ReDim Preserve8m

Example: ReDim Preserve8m

Assignment 1 preview and instructions1m

8 readings

For Mac users1m

The importance of a Course Certificate and the future of higher education5m

If your computer won't open a macro-enabled workbook10m

(OPTIONAL) Will Office Scripts replace VBA?10m

Remember to use your DEBUGGING skills!10m

Quiz 1 solutions and explanations10m

Assignment 12h

Week 2 STARTER file (1 file)

2 practice exercises

Week 1 Quiz20m

Assignment 1 submission30m

Week

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 Excel8m

Example: How to create email addresses from Last Name, First Name format7m

How to use string functions in VBA8m

Example: Using string functions in VBA4m

Example: Extracting email addresses from mixed string formats8m

Example: VBA array function for separating strings into component parts8m

Exporting data from Excel to .txt files6m

Importing data from .txt files8m

Importing data from tab-delimited .txt files2m

Example: Morse coder9m

Example: Morse decoder8m

Assignment 2 preview and instructions1m

3 readings

Quiz 2 solutions and explanations10m

Assignment 22h

Week 3 STARTER files (11 files)

2 practice exercises

Week 2 Quiz20m

Assignment 2 submission30m

Week

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 worksheets8m

Iterating through worksheets7m

Consolidating information in multiple worksheets into a single worksheet4m

Example: Counting total number of 7's in all worksheets of a workbook5m

Putting it all together: Consolidating employee schedules in multiple worksheets9m

All about workbooks9m

Opening workbooks8m

Example: Importing and consolidating data from multiple files6m

Example: Counting 7's in multiple workbooks5m

Putting it all together: Consolidating employee schedules8m

Assignment 3 preview and instructions2m

How to select a range using the input box method1m

2 readings

Assignment 32h 30m

Week 4 STARTER files (5 files)

2 practice exercises

Week 3 Quiz20m

Assignment 3 submission30m

Week

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 boxes7m

Advanced message boxes6m

Event handlers4m

Introduction to user forms3m

Creating your first user form10m

Example: Fuel efficiency user form7m

Example: Tank volume user form6m

Dim'ming (or not Dim'ming) variables in user forms1m

Input validation in user forms6m

Introduction to combo boxes, Part 15m

Introduction to combo boxes, Part 26m

Example: Periodic table user form8m

(OPTIONAL) Putting it all together: Conversion Solver user form10m

Assignment 4 preview and instructions2m

1 reading

Assignment 42h 30m

2 practice exercises

Week 4 Quiz20m

Assignment 4 submission30m

Reviews

4.9

314 reviews

5 stars
89.62%
4 stars
8.49%
3 stars
1.06%
2 stars
0.32%
1 star
0.49%

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

by JUJun 27, 2020

Very interesting journey through advanced capabilities of VBA for Microsoft Excel. Looking forward for the interesting projects to work on Part 3!

by PNDec 26, 2020

very interesting course, very nicely and deeply explained all topics in course. All assignments were quite challenging and very useful for real world applications. looking forward to part 3 of course!

by SJJun 2, 2019

The course provides one the opportunity to work with a lot of concepts/objects in Vba.Be it arrays,working with diffreent workbooks/worksheets/user forms,you have it all in the course.

by CJOct 21, 2020

This was an interesting course and taught me how to use userforms and make much more sophisticated excel/vba files.

I would have liked to learn more about how VBA interacts with charts though.

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.

Excel/VBA for Creative Problem Solving, Part 2

About this Course