Excel/VBA for Creative Problem Solving, Part 2
"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
Buy Now AED 170.99 + VAT
Monthly Subscription Starting at AED 99 + VAT
- Level Professional
- المدة 21 ساعات hours
- الطبع بواسطة University of Colorado Boulder
-
Offered by
عن
"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.الوحدات
WELCOME!
-
3
Videos
-
5
Readings
-
1
Discussion
1
Discussions
- What about you?
3
Videos
- What you will learn in this course
- How the course works
- How To Switch Sessions of the Course
5
Readings
- For Mac users
- The importance of a Course Certificate and the future of higher education
- How to get help
- Opening macro-enabled workbooks
- (OPTIONAL) Will Office Scripts replace VBA?
Screencasts
-
10
Videos
-
1
Readings
-
1
Discussion
1
Discussions
- (OPTIONAL) Week 1 reflection
10
Videos
- 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
1
Readings
- Remember to use your DEBUGGING skills!
Quiz 1 and Assignment 1
-
1
Videos
-
3
Readings
-
1
Discussion
-
2
Assignment
2
Assignment
- Week 1 Quiz
- Assignment 1 submission
1
Discussions
- Assignment 1 Discussion
1
Videos
- Assignment 1 preview and instructions
3
Readings
- Quiz 1 solutions and explanations
- Assignment 1
- Week 2 STARTER file (1 file)
Screencasts
-
11
Videos
-
1
Discussion
1
Discussions
- (OPTIONAL) Week 2 reflection
11
Videos
- 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
Quiz 2 and Assignment 2
-
1
Videos
-
3
Readings
-
2
Discussion
-
2
Assignment
2
Assignment
- Week 2 Quiz
- Assignment 2 submission
2
Discussions
- Quiz 2 Discussion
- Assignment 2 Discussion
1
Videos
- Assignment 2 preview and instructions
3
Readings
- Quiz 2 solutions and explanations
- Assignment 2
- Week 3 STARTER files (11 files)
Screencasts
-
10
Videos
-
1
Discussion
1
Discussions
- (OPTIONAL) Week 3 reflection
10
Videos
- 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
Quiz 3 and Assignment 3
-
2
Videos
-
2
Readings
-
1
Discussion
-
2
Assignment
2
Assignment
- Week 3 Quiz
- Assignment 3 submission
1
Discussions
- Assignment 3 Discussion
2
Videos
- Assignment 3 preview and instructions
- How to select a range using the input box method
2
Readings
- Assignment 3
- Week 4 STARTER files (5 files)
Screencasts
-
13
Videos
-
1
Discussion
1
Discussions
- (OPTIONAL) Week 4 reflection
13
Videos
- 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
Quiz 4 and Assignment 4
-
1
Videos
-
1
Readings
-
1
Discussion
-
2
Assignment
2
Assignment
- Week 4 Quiz
- Assignment 4 submission
1
Discussions
- Assignment 4 Discussion
1
Videos
- Assignment 4 preview and instructions
1
Readings
- Assignment 4
Auto Summary
"Excel/VBA for Creative Problem Solving, Part 2" is a professional-level IT and Computer Science course by Coursera, designed to enhance and optimize Excel skills using VBA. Taught over 1260 minutes, it covers arrays, text string manipulation, data import/export, automation, and user form creation. Ideal for those with foundational VBA knowledge, it offers hands-on assignments for practical learning. Subscription starts at the Starter level.
Charlie Nuttelman