Excel/VBA for Creative Problem Solving, Part 2 from Coursera

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

What's inside

Syllabus

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.

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.

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.

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.

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Examines arrays and array functions, which are standard in data manipulation industry

Teaches how to work with text strings, which helps learners solve real-world problems

Develops skills in iterating through worksheets and workbooks, which are core skills for data analysis

Taught by Charlie Nuttelman, who is recognized for their work in data analysis

Offers hands-on labs and interactive materials, which helps learners apply knowledge

Requires previous knowledge of VBA code and programming structures, which may be a barrier for some learners

Reviews summary

Solve creative problems with excel/vba, part 2

Learners say this engaging course teaches them to solve complex problems in Excel using well explained Arrays, Forms, and Workbooks. With a difficult workload and challenging assignments, the course is perfect for students looking to develop professional level VBA skills. Highly recommended for engineers and professionals who want to automate tedious tasks in Excel.

Benefit from well explained concepts and screencasts.

"He explains things in a way that is easy to understand and follow."

"Arrays, iterating through workbooks and worksheets, input validation, error handling, user forms."

"The materials provided with the course is simple but it has everything you need to learn."

Complete engaging assignments that challenge and reinforce learning.

"There are various engineering examples covered as well which any science or engineering student can relate to."

"Assignments are interesting, challenging, and let you understand if you understood."

"Assignments, especially Assignment 3, are pretty challenging, which ensures that you will need to study in order to pass the course."

Enhance professional development by gaining advanced VBA skills.

"I am constantly thinking about ways to implement and automate everyday tasks with the knowledge provided by the course."

Develop practical skills in VBA for automating complex tasks.

"I have completed a number of projects at work successfully following these two courses."

"I always wanted to learn VBA and glad to find this course."

Expect a challenging course that requires dedication and effort.

"The learning itself is great."

"The one thing I felt that was really lacking was meta-learning"

"This course (and the preceding course) did a middling job explaining the object model and reference in such a way that the student could go out and explore a new function / statement / object with confidence."

Career center

Learners who complete Excel/VBA for Creative Problem Solving, Part 2 will develop knowledge and skills that may be useful to these careers:

Data Analyst

Data analysts transform raw data into valuable insights, enabling businesses to make better choices. A background in visual basic and excel, which is the focus of this course, will help you prepare for this role. Skills in working with arrays, text strings, workbooks, and user forms are all highly relevant to the role of a data analyst.

See salaries and explore the career path for Data Analyst

Financial Analyst

Financial analysts use their knowledge of finance to help organizations make sound investment decisions. Skills in working with arrays, text strings, workbooks and user forms are all highly relevant to the role of a financial analyst. This course can help you prepare for this field by teaching you how to automate tasks and use visual basic and excel to make more efficient use of your time and effort.

See salaries and explore the career path for Financial Analyst

Market Research Analyst

Market research analysts play a vital role in helping businesses understand their customers and make informed decisions. This course will teach you how to use visual basic and excel to automate repetitive tasks, such as data entry and analysis, so you can focus on more strategic initiatives.

See salaries and explore the career path for Market Research Analyst

Business Analyst

Business analysts use their skills in data analysis to help businesses identify and solve problems. This course will teach you how to use visual basic and excel to automate repetitive tasks, such as data entry and analysis, so you can focus on more strategic initiatives.

See salaries and explore the career path for Business Analyst

Software Developer

Software developers design, develop, and maintain computer applications. This course will teach you the basics of visual basic, which can help you build a foundation for a career in software development.

See salaries and explore the career path for Software Developer

Information Technology Specialist

Information technology specialists plan, implement, and maintain computer systems for businesses and organizations. This course will teach you the basics of visual basic, which can help you build a foundation for a career in information technology.

See salaries and explore the career path for Information Technology Specialist

Computer Programmer

Computer programmers write and maintain computer code. This course will teach you the basics of visual basic, which can help you build a foundation for a career in computer programming.

See salaries and explore the career path for Computer Programmer

Database Administrator

Database administrators manage and maintain databases. This course will teach you the basics of visual basic, which can help you build a foundation for a career in database administration.

See salaries and explore the career path for Database Administrator

Web Developer

Web developers create and maintain websites. This course will teach you the basics of visual basic, which can help you build a foundation for a career in web development.

See salaries and explore the career path for Web Developer

Computer Systems Analyst

Computer systems analysts design, develop, and implement computer systems. This course will teach you the basics of visual basic, which can help you build a foundation for a career in computer systems analysis.

See salaries and explore the career path for Computer Systems Analyst

Computer Network Architect

Computer network architects design and implement computer networks. This course will teach you the basics of visual basic, which can help you build a foundation for a career in computer network architecture.

See salaries and explore the career path for Computer Network Architect

Information Security Analyst

Information security analysts plan and implement security measures to protect computer systems and networks. This course will teach you the basics of visual basic, which can help you build a foundation for a career in information security analysis.

See salaries and explore the career path for Information Security Analyst

Data Scientist

Data scientists use their skills in data analysis and machine learning to help businesses make informed decisions. This course will teach you the basics of visual basic, which can help you build a foundation for a career in data science.

See salaries and explore the career path for Data Scientist

Operations Research Analyst

Operations research analysts use their skills in mathematics and optimization to help businesses make informed decisions. This course will teach you the basics of visual basic, which can help you build a foundation for a career in operations research analysis.

See salaries and explore the career path for Operations Research Analyst

Statistician

Statisticians use their skills in data analysis and probability to help businesses make informed decisions. This course will teach you the basics of visual basic, which can help you build a foundation for a career in statistics.

See salaries and explore the career path for Statistician

Excel/VBA for Creative Problem Solving, Part 2

What's inside

Syllabus

Good to know

Save this course

Reviews summary

Solve creative problems with excel/vba, part 2

Career center

Reading list

Share

Similar courses