Excel/VBA for Creative Problem Solving, P…

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

Traffic lights

Read about what's good

what should give you pause

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

Advanced excel/vba problem solving skills

According to learners, this course is a highly practical deep dive into advanced Excel/VBA techniques. Students appreciated the hands-on nature of the assignments, which require working directly in Excel and applying concepts like arrays, string manipulation, and iterating through workbooks. The module on creating professional user forms is frequently highlighted as particularly useful for real-world applications. While the course provides powerful automation skills, many found the assignments very challenging and time-consuming, often requiring significant effort beyond the lecture material. It is strongly recommended to have a solid foundation from Part 1 or equivalent VBA experience.

Expect to dedicate many hours weekly.

"This course is very time-consuming, primarily due to the challenging assignments."

"Requires significant dedication each week to keep up with the material and complete the homework."

"Allocate ample time in your schedule if you want to succeed in this course."

Requires prior VBA knowledge.

"This course definitely builds heavily on Part 1. Highly recommend taking Part 1 first if you are not strong in VBA fundamentals."

"Learners without a solid background in VBA or who skipped Part 1 may find the pace and complexity difficult."

"It assumes a certain level of comfort with VBA programming logic and structures from the outset."

Covers advanced, useful topics.

"The sections on user forms and handling multiple files were extremely useful and well-explained."

"Learning how to work with arrays efficiently was a game-changer for optimizing my code."

"The course covers critical VBA topics that go beyond basic macros, essential for serious automation."

"String manipulation and file handling are practical skills covered effectively."

Focuses on real-world VBA problems.

"The assignments were very practical and applicable to real-world problems I face at work."

"I learned skills that I can immediately use to automate tasks and save time."

"Working with arrays and iterating through workbooks provided invaluable automation techniques."

"The user forms module alone is worth the price of the course for its practical utility."

Homework requires significant effort.

"Assignments were significantly more challenging than Part 1 and required a lot of time and effort to complete."

"Some assignments felt overly complex and required external resources or extensive troubleshooting."

"While difficult, the assignments really push you to apply what you've learned in depth."

"Be prepared to spend a lot of hours on the homework; they are not trivial."

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Excel/VBA for Creative Problem Solving, Part 2 with these activities:

Watch a tutorial on how to use VBA to automate a task

Show steps

Watching tutorials can be a great way to learn new VBA skills. There are many free tutorials available online that can teach you how to use VBA to automate tasks in Excel.

Browse courses on VBA

Show steps

Search for a tutorial on how to use VBA to automate a task in Excel
Watch the tutorial and follow along with the steps
Apply what you learned from the tutorial to your own VBA code

Review the syntax of VBA

Show steps

Review the syntax of VBA to ensure a solid foundation in the programming language before starting the course.

Browse courses on VBA

Show steps

Go over the VBA syntax documentation.
Practice writing VBA code in a text editor or IDE.
Test the code and debug any errors.

Practice debugging VBA code

Show steps

Debugging is an essential skill for any programmer. Debugging VBA code can be especially challenging. By practicing debugging, you can improve your ability to identify and fix errors in your code.

Show steps

Review the basics of debugging in VBA
Find and fix a simple bug in a VBA code
Use the debugger to step through a VBA code and identify the source of an error

Eight other activities

Expand to see all activities and additional details

Show all 11 activities

Learn how to use the VBA Editor

Show steps

Become familiar with the VBA Editor to effectively create and edit VBA code throughout the course.

Show steps

Find resources and tutorials on using the VBA Editor.
Follow the tutorials to learn the basic functions and features of the VBA Editor.
Practice using the VBA Editor to write and edit code.

Join a study group focused on VBA

Show steps

Engage with peers to exchange knowledge, ask questions, and collectively solve VBA-related problems.

Show steps

Search for online forums or communities dedicated to VBA learning.
Join a study group and connect with other learners.
Participate in discussions, share ideas, and assist others.

Create a user form to collect data from the user

Show steps

Creating user forms is a great way to improve your VBA skills. User forms allow you to create custom interfaces for your VBA applications, making them more user-friendly and efficient.

Show steps

Design the layout of your user form
Add controls to your user form, such as text boxes, labels, and buttons
Write VBA code to handle events on your user form, such as button clicks

Create a VBA program to automate a task

Show steps

Apply the techniques learned in the course by creating a practical VBA program that automates a task, enhancing understanding and proficiency.

Browse courses on Automation

Show steps

Identify a task that can be automated using VBA.
Design and develop a VBA program to perform the task.
Test and debug the program to ensure it runs correctly.

Read 'Excel VBA Programming: A Guide to Object-Oriented Programming in Microsoft Excel'

Show steps

Enhance understanding of VBA principles and techniques by reviewing a comprehensive book that delves into object-oriented programming in Microsoft Excel.

View Professional Excel Development on Amazon

Show steps

Obtain a copy of the book.
Read through the chapters and make notes.
Complete the exercises provided in the book.

Participate in a VBA coding competition

Show steps

Participating in a VBA coding competition can be a great way to test your skills and learn from others. There are many VBA coding competitions available online, so you can find one that matches your skill level and interests.

Show steps

Find a VBA coding competition to participate in
Review the competition rules and requirements
Develop a VBA solution for the competition
Submit your solution to the competition

Participate in a VBA programming competition

Show steps

Challenge yourself and assess your progress by participating in a VBA programming competition, fostering problem-solving skills and driving proficiency.

Browse courses on Competition

Show steps

Find and register for a suitable VBA programming competition.
Study and prepare for the competition.
Participate in the competition and strive to perform well.

Mentor a beginner in VBA

Show steps

Solidify your understanding of VBA by teaching and guiding a beginner, reinforcing knowledge and fostering empathy for diverse learning styles.

Browse courses on Mentoring

Show steps

Find a beginner who is interested in learning VBA.
Share your knowledge and guide them through the basics of VBA.
Provide support and encouragement as they progress.

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