Unlock Excel VBA and Excel Macros from Udemy

What's inside

Learning objectives

Enhance your current excel files & automate your work with vba.
Master excel vba & macros
Directly apply the excel vba example files included to your own work.
Reduce manual routine tasks to the click of a button.

Make your work-life easier.
Create simple solutions to complex problems by levering excel functionality with macros.
Formulas & excel core functionality can't do everything. use vba when you reach a limitation.
Easily adjust vba code you find online to fit your purpose.

Enhance your current excel files & automate your work with vba.
Master excel vba & macros
Directly apply the excel vba example files included to your own work.
Reduce manual routine tasks to the click of a button.
Make your work-life easier.
Create simple solutions to complex problems by levering excel functionality with macros.
Formulas & excel core functionality can't do everything. use vba when you reach a limitation.
Easily adjust vba code you find online to fit your purpose.

Syllabus

Introduction & Scope

Check out what is included in the course and what you can do with Visual Basic in Microsoft Excel. You'll find examples to Excel tools you can create using VBA.

Important Compatibility & Setup Information

Course Resources: Download Course Project Files

Course Expectations

Course Outline for Quick Reference

Become Familiar with Macros and Visual Basic Editor (VBE)

Overview: Macros & Visual Basic Editor (VBE)

Steps to Your First Macro Recording

Visual Basic Editor: What You Need to Know

Project: Dynamic Cell Selection (Special Cells) - Perfect for Flexible Macros

Project: Copy (Consolidate) Data - Absolute versus Relative Macro Recording

7 Ways to Run Macros / VBA code (incl. creative & modern buttons)

OFFICE 365 Update: Change from Excel Comments to Notes

Activity: Record a Macro that Deletes all Comments

What's the Difference Between Macros and VBA?

Please fill out the following 4 questions to ensure you're ready to enter the next section.

Understanding the Object model (the dot in VBA)

To become an expert in Excel VBA, it's important to fully understand the Object Model. This way you'd be able to write the correct VBA syntax for your Excel projects (without guessing!). Selecting the wrong combination of objects, properties and methods will result in errors in your code.

Writing VBA code is all about manipulating objects on the screen. These objects have code words for easier reference. This code can be found in the VBA reference library. In this lecture you'll learn how to use the Excel VBA reference library so you can find the correct code for your Excel macro.

In this lecture we cover a few essential Excel VBA concepts including what a sub and function procedures are and what the different color codes mean. We also learn some useful shortcuts we can use the VBA editor.

In this lecture we get into the setup of the object model. VBA syntax is object first, then action. Objects can also have relationships with other objects - you need to be aware of the object hierarchy. It gets really interesting and fun.

In this lecture you learn all about Excel VBA object properties and what these are.

When you start to learn Excel VBA, it's important to understand what methods are and how you can use them together with objects to automate tasks.

VBA syntax is not easy to remember. How do you find the correct object, property or method that you need for your code?

Make sure you answer these before moving on to the next section.

Conclusion: Key Takeaways & Message from Me

How to best refer to ranges (for example Excel VBA cells function), workbooks and worksheets.

Overview: Referencing Ranges, Worksheets & Workbooks

This lecture is a complete guide that teaches you how to refer and write to a range or to cells in Excel VBA. You'll learn how to change a cells value with VBA by using range and the cells property, the offset property. You'll also learn how to refer to different ranges in one statement, how to select a row or a column with VBA and how the used range property works.

Most Useful Range Properties & Methods

Find out how find the last empty row or the last empty column using different methods. Some work also with blank cells inside the data set. We take a look at using the end property of the range object. Then the current region property and the used range property of the worksheet object. We also take a look at using SpecialCells method to find the last used cell in the worksheet.

Copying & resizing a variably sized range

Properly Referencing Worksheets

Properly Referencing Workbooks

Activity: Save a Hard-coded Copy of Workbook

Project: Save Hard-coded Copy & Macro-free version of workbook

Key Takeaways for Referencing Ranges

Understanding Variables & Data Types

Overview: Role of Assignment & Variables

Data Types for Better Performance

Declaring Variables, Arrays & Constants (Role of Option Explicit)

Using Object Variables (Set statement)

Scoping Variables

Activity: Revisit Hard-coded Workbook Project & Use Variable Assignment

Test Your Knowledge on Variables and data types

Key Takeaways for Working with Variables

Learn to Work with Collections and Make Conditional Decisions

Overview: Why Collections are a VBA Essential

With & End With for Easier Code Writing

For Each to Loop Through Collections (sheets, ranges etc.) in one go

IF Then (Else, ElseIF) for Conditional Outcomes

Select Case as Alternative for Many IF Statements

Goto Statement to Change Program Flow

Activity: Get the Number of Formulas on the worksheet

Project: Get the Number of Formulas on the Worksheet

Learn 3 ways how you can unhide all Excel tabs in one go. You can also apply this to all macro-free Excel Workbooks.

Key Takeaways for Collections & Decision Making in VBA

Useful Built-in Functions

Overview: VBA versus Worksheet Functions

Most Useful VBA Functions

Message Box (also with Yes, No buttons)

Input Box (VBA InputBox Function)

Input Box that can Select Ranges (Excel InputBox Method)

Activity: Show Top 3 Values in a Message Box Based on Range Selected

Project: Show Top 3 Values in a Message box based on Range Selected

Key Takeaways Built-in Functions

Learn to Debug code, Handle Errors & use Sub Procedures effectively

Overview: Debugging, Handling Errors & Procedure Scope

Debugging Options: Watch, Locals, Immediate Windows & More

Error Handling: Different Methods

Faster Code by Suppressing Pop-ups & Flickering Screen

Procedure Scope & Running one Sub from Another

Passing Arguments to Sub Procedures (By Ref, By Val)

Activity: Get the Total Number of Formulas (or Comments) Used in a Workbook

Project: Get the Total Number of Formulas (or Comments) Used in a Workbook

Key Takeaways Debugging, Error Handling & Course Map Status

Automatically create Table of Contents for ANY of your Excel Workbooks

Project overview: Table of Contents (TOC) with one click

TOC Project: The Basics

TOC Project: Adding Hyperlinks

TOC Project: Testing the VBA Code

TOC Project: Adding Error Handling & More Checks

TOC Project: Adding to Personal Macro Workbook (Make it Available to all Files)

First Milestone Completed!

Looping in VBA (Controlling the Flow of Code)

Section Overview: Looping in VBA

For Next Counter Loops (Simple Example)

For Next Nested Loop (Loop through Text & Cells)

For Next Reverse Loop (Delete Filtered/Hidden Rows)

Do Until & Do While Loop

Practical Example of Do Loop (with Inputbox)

Find Method for Quicker Lookup Results

Find Method with Do Loop - Multiple Matches

Add a Timer to Test & Speech to Inform

Activity: Document all Comments in Workbook in a Sheet

Project: Document all Comments in Workbook in a Sheet

Useful VBA Statements & Key takeaways for Looping in VBA

Working with Arrays

Learn how to use arrays in Excel VBA.

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Teaches how to enhance Excel files and automate work with VBA

Develops proficiency in Excel VBA and Macros

Provides example files that learners can directly apply to their own work

Empowers learners to automate routine tasks with the click of a button

Facilitates the creation of simple solutions to complex problems by leveraging Excel functionality with Macros

Course may require learners to have some prior experience in Excel or programming

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 Unlock Excel VBA and Excel Macros with these activities:

Read VBA and Macros for Microsoft Excel

Show steps

This book provides a comprehensive overview of VBA and Excel macros, including advanced topics such as object-oriented programming and error handling.

View Excel VBA Programming For Dummies 5th Edition... on Amazon

Show steps

Read the book thoroughly, taking notes on important concepts.
Complete the practice exercises provided in the book.
Apply what you've learned to your own Excel projects.

Find a mentor who can help you with VBA

Show steps

A mentor can provide guidance, support, and advice as you learn VBA.

Browse courses on Excel VBA

Show steps

Identify your learning goals and what you hope to gain from a mentor.
Network with people in your field and ask for recommendations.
Interview potential mentors and choose one who is a good fit for you.

Practice using VBA to automate common Excel tasks

Show steps

Completing practice exercises will reinforce the syntax and techniques learned in the course.

Browse courses on VBA Syntax

Show steps

Choose a task you want to automate, such as formatting a table or generating a report.
Break down the task into smaller steps.
Write VBA code to perform each step.
Test your code and make any necessary adjustments.

Five other activities

Expand to see all activities and additional details

Show all eight activities

Attend a VBA workshop

Show steps

A workshop can provide hands-on experience and allow you to ask questions to an expert.

Browse courses on Excel VBA

Show steps

Find a VBA workshop that fits your schedule and learning needs.
Attend the workshop and actively participate.
Follow up after the workshop by practicing what you've learned.

Create an Excel UserForm using VBA

Show steps

UserForms can greatly enhance the user experience of your Excel applications.

Browse courses on Excel VBA

Show steps

Follow a tutorial on how to create a UserForm using VBA.
Create a UserForm for a specific task, such as collecting user input or displaying data.
Test your UserForm and make any necessary adjustments.

Create a complex VBA macro to automate a multi-step task

Show steps

This activity will challenge you to apply your VBA skills to a real-world problem.

Browse courses on Excel VBA

Show steps

Identify a multi-step task that you can automate using VBA.
Break down the task into smaller steps.
Write VBA code to perform each step.
Test your code and make any necessary adjustments.
Document your code so that others can understand and use it.

Create a blog post or article about VBA

Show steps

This activity will allow you to share your knowledge of VBA with others and reinforce your own understanding.

Browse courses on Excel VBA

Show steps

Choose a topic that you are knowledgeable about and that you think would be helpful to others.
Write a blog post or article that is well-organized, informative, and engaging.
Publish your blog post or article and share it with others.

Mentor another student who is learning VBA

Show steps

Mentoring others will help you to solidify your own understanding of VBA and to develop your communication and teaching skills.

Browse courses on Excel VBA

Show steps

Identify a student who is learning VBA and who could benefit from your help.
Meet with the student regularly to provide guidance and support.
Answer the student's questions and help them to overcome any challenges they are facing.

Career center

Learners who complete Unlock Excel VBA and Excel Macros will develop knowledge and skills that may be useful to these careers:

Financial Analyst

Financial Analysts spend a significant amount of their time working with Excel and VBA, as the course teaches comprehensive Excel VBA and macros, it would be instrumental to this role.

See salaries and explore the career path for Financial Analyst

Management Analyst

Management Analysts often use VBA and Macros to create custom solutions and automate tasks within an organization. The course will be extremely useful for learning about macros, as well as more advanced principles of VBA including setting up object models, working with object variables, and looping techniques.

See salaries and explore the career path for Management Analyst

Data Analyst

In order to maximize efficiency and optimize business decisions, Data Analysts need to have deep Excel skills, including VBA. Given that data analysis is a major component of the role, the course would also be indispensable to working with large quantities of data using VBA and Excel Macros.

See salaries and explore the career path for Data Analyst

Data Scientist

Data Scientists use scientific methods, processes, algorithms, and systems to extract knowledge and insights from data in various forms, both structured and unstructured. They use Excel VBA and macros to perform data analysis, visualization, and modeling. The course can be useful in providing a strong foundational knowledge of VBA and programming principles.

See salaries and explore the career path for Data Scientist

Operations Research Analyst

Operations Research Analysts use advanced analytical techniques to solve complex business problems, often using VBA and Excel macros to automate tasks and improve efficiency. The course will provide a strong foundation in VBA, Macros, and other programming principles, which will be an advantage for those looking to enter this field.

See salaries and explore the career path for Operations Research Analyst

Software Engineer

Software Engineers apply engineering principles to the design, development, maintenance, testing, and evaluation of computer software. The course will be useful for learning about software engineering and programming, which would be a foundational skill in this field.

See salaries and explore the career path for Software Engineer

Systems Analyst

Systems Analysts are responsible for studying business needs and designing and implementing software systems to meet those needs. As such, they must have deep theoretical and practical knowledge of computer science, with a strong emphasis on modern programming practices such as those taught in the course. Someone looking to pivot into this field would benefit from the course as they will learn the fundamentals of computer science, including VBA, macros, and excel functions.

See salaries and explore the career path for Systems Analyst

Business Analyst

Business Analysts are responsible for studying business needs and determining how technology can be used to improve and optimize those processes. As such, they must have deep theoretical and practical knowledge of computer science, with a strong emphasis on modern programming practices such as those taught in the course. Someone looking to pivot into this field would benefit from the course as they will learn the fundamentals of computer science, including VBA, macros, and excel functions.

See salaries and explore the career path for Business Analyst

Quality Assurance Analyst

Quality Assurance Analysts are responsible for ensuring the quality of software products. To perform the role, it is essential to understand programming concepts, including coding in VBA. The course will be useful in providing such a foundation in both VBA and general programming principles.

See salaries and explore the career path for Quality Assurance Analyst

Software Developer

Software Developers design, develop, and test software applications. The course can be useful in helping someone pivot into this career field, as it teaches programming concepts, including VBA coding and the software development life cycle.

See salaries and explore the career path for Software Developer

IT Business Analyst

IT Business Analysts bridge the gap between business and IT teams, working to ensure alignment between business requirements and technical solutions. To do this role, it is necessary to have strong knowledge in both fields. The course would help build a foundation in programming, which would be helpful for understanding technical business requirements.

See salaries and explore the career path for IT Business Analyst

Web Developer

Web Developers design, develop, and maintain websites. The course may be useful for someone looking to learn web development, as it will teach them VBA, as well as provide some foundational knowledge in programming, including debugging and error handling.

See salaries and explore the career path for Web Developer

Database Administrator

Database Administrators are responsible for the design, implementation, and maintenance of database management systems. The course may be useful for someone in this role who is looking to expand their skills in programming and data management.

See salaries and explore the career path for Database Administrator

Project Manager

Project Managers are responsible for overseeing and managing projects from start to finish. In this role, there may be a need to use VBA and Excel macros to plan, track, and report on project progress, as the course teaches project management principles, it may be useful.

See salaries and explore the career path for Project Manager

Computer Systems Analyst

Computer Systems Analysts are responsible for analyzing and designing computer systems. They may use VBA and Excel macros to automate tasks and improve efficiency, so the course may be useful for someone in this role who is looking to expand their skills.

See salaries and explore the career path for Computer Systems Analyst