Excel/VBA for Creative Problem Solving, Part 1
Keep adding new skills with 10,000+ programs for $239 (usually $399). Save now.
Excel/VBA for Creative Problem Solving, Part 1
This course is part of Excel/VBA for Creative Problem Solving Specialization
Instructor: Charlie Nuttelman
170,941 already enrolled
Included with
Ask Coursera
4,313 reviews
4,313 reviews
What you'll learn
Utilize VBA to define and implement custom user-defined functions
Create macros to automate procedures in Excel
Identify the basic programming structures in VBA
Automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems
Skills you'll gain
Tools you'll learn
Details to know
See how employees at top companies are mastering in-demand skills
Build your subject-matter expertise
- Learn new concepts from industry experts
- Gain a foundational understanding of a subject or tool
- Develop job-relevant skills with hands-on projects
- Earn a shareable career certificate
There are 6 modules in this course
"Excel/VBA for Creative Problem Solving, Part 1" 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).
This course is the first part of a three-part series and Specialization that focuses on the application of computing techniques in Excel/VBA to solve problems. In this course (Part 1), you will: 1) create macros to automate procedures in Excel; 2) define your own user-defined functions; 3) create basic subroutines to interface with the user; 4) learn the basic programming structures in VBA; and 5) automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems. New to computer programming? The extremely intuitive and visual nature of VBA lends itself nicely to teaching and learning - what a fun way to learn to code! No prior knowledge in programming nor advanced math skills are necessary yet seasoned programmers will pick up new and creative spreadsheet problem solving strategies. After you have learned the basics of VBA, 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.
Week 1 will introduce you to visual basic for applications (VBA) and teach you the foundational tools required to create basic procedures in VBA. You'll learn about different data types and the scope of variables, how to troubleshoot your code when it's not working, and how to record basic macros using both absolute and relative referencing modes.
What's included
14 videos14 readings3 assignments3 discussion prompts
14 videos•Total 63 minutes
- Welcome!•2 minutes
- What can you do with Excel/VBA?•5 minutes
- How the course works•3 minutes
- How to switch sessions of the course•1 minute
- Week 1 preview•2 minutes
- Getting your feet wet•4 minutes
- Saving your files as macro-enabled workbooks•1 minute
- Recording basic macros•8 minutes
- Absolute vs. relative referencing during macro recording•8 minutes
- Overview of procedures in VBA•8 minutes
- Why use Option Explicit?•3 minutes
- Declaration of variables, data types, and scope of variables•9 minutes
- How to troubleshoot when your code is not working properly•6 minutes
- Assignment 1 preview and instructions•2 minutes
14 readings•Total 145 minutes
- Course Updates and Accessibility Support•1 minute
- For Mac users•10 minutes
- The power of Excel/VBA•3 minutes
- The importance of a Course Certificate and the future of higher education•5 minutes
- (OPTIONAL) Will Office Scripts replace VBA?•10 minutes
- Course improvement and my philosophy on learning•2 minutes
- If an assignment is not showing up for you•10 minutes
- How to get help•2 minutes
- VBA Tutorial•10 minutes
- Opening macro-enabled workbooks•10 minutes
- Week 1 files•2 minutes
- Need to improve your Excel skills?•10 minutes
- Assignment 1•60 minutes
- Week 2 files•10 minutes
3 assignments•Total 13 minutes
- Unlock quiz for Week 1 files•2 minutes
- Week 1 Quiz•10 minutes
- Assignment 1 Submission •1 minute
3 discussion prompts•Total 30 minutes
- What about you?!•10 minutes
- (OPTIONAL) Week 1 reflection•10 minutes
- Assignment 1 Discussion•10 minutes
In Week 2, you will learn how to write proper VBA syntax and basic mathematical expressions; you will learn about objects, properties, methods, and events; and you will learn how to borrow common Excel functions using VBA code. In addition, you will learn how to move information to and from Excel and the Visual Basic Editor (VBE).
What's included
11 videos2 readings2 assignments2 discussion prompts
11 videos•Total 59 minutes
- Week 2 preview•1 minute
- VBA syntax and expression entry: Part 1•7 minutes
- VBA syntax and expression entry: Part 2•6 minutes
- A warning about exponentiation and ampersands•2 minutes
- Introduction to objects, properties, methods, and events•8 minutes
- Common objects, properties, and methods•9 minutes
- How to borrow functions from Excel•12 minutes
- Examples, Part 1: Basic input and output in subroutines•7 minutes
- Examples, Part 2: Basic input and output in subroutines•5 minutes
- Examples, Part 3: Basic input and output in subroutines•2 minutes
- Assignment 2 preview and instructions•2 minutes
2 readings•Total 92 minutes
- Assignment 2•90 minutes
- Week 3 files•2 minutes
2 assignments•Total 21 minutes
- Week 2 Quiz•20 minutes
- Assignment 2 submission•1 minute
2 discussion prompts•Total 20 minutes
- (OPTIONAL) Week 2 reflection•10 minutes
- Assignment 2 Discussion•10 minutes
In Week 3, you will learn how to create your own user-defined functions. You will also learn how to call other subroutines and functions that reside in other files and you will learn how to design subroutines based on a modular approach. Finally, you will learn basic error handling in VBA.
What's included
9 videos3 readings2 assignments2 discussion prompts
9 videos•Total 26 minutes
- Week 3 preview•1 minute
- How to make your own user-defined function•5 minutes
- How to convert a user-defined function to an Excel Add-In•3 minutes
- How to troubleshoot your user defined functions•1 minute
- Why you don't use input and message boxes in functions•3 minutes
- How to handle basic user error in your subroutines•3 minutes
- An introduction to modular programming•6 minutes
- Running a subroutine that resides in another file•4 minutes
- Assignment 3 preview and instructions•2 minutes
3 readings•Total 67 minutes
- Remember to use your DEBUGGING skills!•5 minutes
- Assignment 3•60 minutes
- Week 4 files•2 minutes
2 assignments•Total 21 minutes
- Week 3 Quiz•20 minutes
- Assignment 3 submission•1 minute
2 discussion prompts•Total 20 minutes
- (OPTIONAL) Week 3 reflection•10 minutes
- Assignment 3 Discussion•10 minutes
Week 4 begins the "meat" of programming in VBA, and we are finally moving into some exciting problem solving. You will learn about the common programming structures in VBA (sequence, selection, and repetition) that form the foundation for advanced programming procedures. Several examples will highlight the utility of these programming structures.
What's included
15 videos2 readings2 assignments2 discussion prompts
15 videos•Total 95 minutes
- Week 4 preview•2 minutes
- How to implement One-Way If...Then selection structures•8 minutes
- How to implement Two-Way If...Then selection structures•8 minutes
- All about the Multi-Alternative If...Then•4 minutes
- Variable iteration loops (Do...Loops)•7 minutes
- Validating user input using a Do...Loop•4 minutes
- Creating a guessing game using a Do...Loop•3 minutes
- All about fixed iteration (For...Next) loops•9 minutes
- Iterating through a Selection vs. a Range•10 minutes
- Putting it all together: Example 1•5 minutes
- Putting it all together: Example 2•8 minutes
- Example: Highlighting max and min values in a range•7 minutes
- Using the For Each... Next statement•8 minutes
- Worked mini-project: Searching through high and low temperatures in an Excel workbook•9 minutes
- Assignment 4 preview and instructions•3 minutes
2 readings•Total 70 minutes
- Assignment 4•60 minutes
- Week 5 files•10 minutes
2 assignments•Total 21 minutes
- Week 4 Quiz•20 minutes
- Assignment 4 submission•1 minute
2 discussion prompts•Total 20 minutes
- (OPTIONAL) Week 4 reflection•10 minutes
- Assignment 4 Discussion•10 minutes
One of the most valuable uses of VBA is to manage large data sets in Excel. In this module, you'll learn how to automate the processes of filtering, sorting, and removing duplicates. You'll learn additional strategies for cleaning up data. The strategies learned in this week will make you a valued VBA user.
What's included
10 videos2 readings2 assignments2 discussion prompts
10 videos•Total 73 minutes
- Week 5 preview•1 minute
- Filtering basics•12 minutes
- Filtering for multiple criteria: Part 1•8 minutes
- Filtering for multiple criteria: Part 2•6 minutes
- Highlighting or deleting rows that satisfy a certain criterion•9 minutes
- Sorting basics•9 minutes
- Removing duplicates•6 minutes
- Example: Removing duplicates•9 minutes
- Putting it all together: Cleaning data•10 minutes
- Assignment 5 preview and instructions•3 minutes
2 readings•Total 100 minutes
- Assignment 5•90 minutes
- Week 6 files•10 minutes
2 assignments•Total 35 minutes
- Week 5 Quiz•30 minutes
- Assignment 5 submission•5 minutes
2 discussion prompts•Total 20 minutes
- (OPTIONAL) Week 5 reflection•10 minutes
- Assignment 5 Discussion•10 minutes
The final module of this course focuses on using the R1C1 style of cell formatting. This style, in contrast to the default A1 style of referencing in Excel, makes it much easier and straightforward to perform advanced manipulations to Excel worksheets. Several examples demonstrate the utility of the R1C1 style.
What's included
8 videos1 reading2 assignments2 discussion prompts
8 videos•Total 41 minutes
- Week 6 preview•1 minute
- When macro recording fails•6 minutes
- What is R1C1 style?•8 minutes
- The Cells property•3 minutes
- The Resize property•4 minutes
- Example 1: R1C1 style•9 minutes
- Example 2: R1C1 style•8 minutes
- Assignment 6 preview and instructions•2 minutes
1 reading•Total 90 minutes
- Assignment 6•90 minutes
2 assignments•Total 35 minutes
- Week 6 Quiz•30 minutes
- Assignment 6 submission•5 minutes
2 discussion prompts•Total 20 minutes
- (OPTIONAL) Week 6 reflection•10 minutes
- Assignment 6 Discussion•10 minutes
Earn a career certificate
Add this credential to your LinkedIn profile, resume, or CV. Share it on social media and in your performance review.
Instructor
Offered by
Explore more from Algorithms
- Status: Free TrialU
University of Colorado Boulder
Course
- Status: Free TrialU
University of Colorado Boulder
Course
- Status: Free TrialU
University of Colorado Boulder
Specialization
- Status: Free TrialL
Logical Operations
Specialization
Why people choose Coursera for their career
Learner reviews
- 5 stars
87.08%
- 4 stars
10.01%
- 3 stars
1.39%
- 2 stars
0.34%
- 1 star
1.15%
Showing 3 of 4313
Reviewed on Oct 26, 2018
Really enjoyed this course. From Week 4 onwards it's quite challenging. Really appreciate the active engagement from Dr. Nuttelman and other moderators on the forum who gave excellent advice.
Reviewed on Apr 10, 2020
Great course for anyone interested in Excel/VBA, especially engineers. There is a lack of course materials I must say, sometimes the spreadsheets that are worked with, were not uploaded.
Reviewed on Mar 2, 2020
i highly recommend this course as the content is new and the questions are designed to be challenging but not difficult. It is an excellent introduction course to VBA or programming in general
Frequently asked questions
To access the course materials, assignments and to earn a Certificate, you will need to purchase the Certificate experience when you enroll in a course. 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.
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.
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
Financial aid available,