Explore

Excel/VBA for Creative Problem Solving, Part 2

Approx. 20 hours to complete

Save Course

Go to Course

Course Summary

Excel VBA for Creative Problem Solving Part 2 is a continuation of Part 1 that explores more advanced topics in VBA programming for Microsoft Excel. Students will learn how to automate complex tasks, develop custom functions, and create user forms to enhance their spreadsheets.

Key Learning Points

Gain advanced knowledge of VBA programming for Excel
Automate complex tasks and develop custom functions
Create user forms to enhance spreadsheets

Learning Outcomes

Develop advanced VBA programming skills
Automate complex tasks and increase efficiency
Create user-friendly spreadsheets with custom functions and forms

Prerequisites or good to have knowledge before taking this course

Completion of Excel VBA for Creative Problem Solving Part 1
Basic knowledge of Excel and programming concepts

Course Difficulty Level

Intermediate

Course Format

Self-paced
Online
Video Lectures

Similar Courses

Excel VBA Programming for Beginners
Excel Skills for Business: Essentials
Data Visualization with Advanced Excel

Related Education Paths

Related Books

Description

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

Outline

Arrays and Array Functions
What you will learn in this course
How the course works
How To Switch Sessions of the Course
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 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 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 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

Summary of User Reviews

This Excel VBA course has received positive reviews from users who found it helpful for solving creative problems. Many users appreciated the instructor's clear explanations and the practical approach of the course.

Key Aspect Users Liked About This Course

Clear explanations and practical approach

Pros from User Reviews

Instructor provides clear explanations
Course covers practical applications
Good for solving creative problems
Helpful for automating tasks
Great for improving Excel skills

Cons from User Reviews

Some parts may be too advanced for beginners
Lacks in-depth coverage of some topics
Not much interaction with instructor
Lacks hands-on exercises
Some users found it too basic

Recommended for you

Success: Practical Thinking Skills

This award-winning, practical course helps you succeed in work and life. The current version (2. An Award-Winning MOOC Expanded and Updated...

Save Course

Hypnosis: Advanced Hypnosis Seminar 3

It’s close to REAL mind control! Now you can have your clients think and feel anything you want them to …...

Save Course