Excel 365 VBA, Expert

Name: Excel 365 VBA, Expert
Rating: 4.4 (32 reviews)

Learn proper Visual Basic programming techniques.

Created byIntellezy Trainers

Last updated 8/2021

English

What you'll learn

Work with variables.
Use various conditional logic statements such as If and Else.
Work with string functions.
Work with various loops.
Create user forms.
Learn debugging tools and how to debug code.

Course content

11 sections • 51 lectures • 5h 11m total length

Introduction2:39
Learn the essentials of Excel 365 VBA, the Visual Basic for Applications language that automates tasks, using variables, conditional logic, strings, loops, and user forms.

Adding the Developer Toolbar5:07
Enable the developer tab, access Visual Basic, and manage macros, from recording to editing and running, while understanding relative reference options and macro security practices.
Understanding the VBA Editor10:59
Recording a Macro in Personal9:51
Understanding the Elements of the VBA Language6:57
Understanding Methods5:12
Understanding Comments5:41
Working with the Range Property and Worksheet Object6:38
Adding a Macro Button to a Spreadsheet2:52
Working with the ActiveCell Object2:00
Learn how to combine ActiveCell with the Range object in Excel VBA to build flexible ranges, starting at the ActiveCell and extending to a fixed end like C5.
Working with the Offset Property7:40

Understanding Variables6:44
Declare variables with dim for texts, numbers, or dates, and an integer type; assign values, display in a message box, and use control space to auto-complete descriptive names.
Working with Integer Variables2:50
Understanding Option Explicit5:20
Learn how option explicit improves VBA by enforcing explicit variable declarations, preventing typos, and easing debugging. Use auto-complete (Ctrl+Space) to ensure variable names are spelled correctly in Excel 365 VBA.
Working with Other Variable Types7:23

Working with IF Statements4:03
Working with ELSE2:43
Working With ELSEIF3:27
Understanding Conditional Operators5:53
Working with NOT, AND, and OR6:31
Working with Nested IF4:22
Working with Select Case6:22
Working with WITH and End WITH4:42
Explore Excel 365 VBA techniques to streamline code using with and end with, applying multiple font properties to a single activecell with dot notation for better readability.

Understanding and Creating a User Form7:31
Compare input box to user forms and demonstrate creating a user form in the Visual Basic editor, adjusting properties, naming conventions like frm, and previewing with the run button.
Adding Controls to a User Form15:44
Adding Code for a User Form9:56
Use a user form to collect name and department and write them to A2 and B2 via the cmdNameDept button. Close the form with me.hide and show a message box.
Launching a User Form from Excel5:12

Getting Started with Debugging9:41
Identify and fix syntax, run time, and logic errors in VBA; use error messages, end if, option explicit, and improve debugging with indentation, comments, macro recorder, and tools.
Stepping Through Your Code6:12
Working with Breakpoints3:27
Working with the Immediate Window5:08
Explore the immediate window in the Visual Basic Editor to test single lines and run macros with Enter. Inspect values with the value property, and count sheets with Sheets.count.
Working with the Locals Window4:47
Working with the Err Object5:38
Getting Help6:41

Requirements

Intermediate Microsoft Excel training or equivalent experience is recommended.

Description

This course will teach students proper Visual Basic programming techniques along with an understanding of Excel’s object structure. Students will learn how to work with variables, how to use various conditional logic statements such as If and Else, and how to work with string functions. Students will also learn how to work with various loops such as next loops and each loops, as well as how to create user forms. Finally, students will get an in-depth understanding of the debugging tools available and how to effectively debug their code.

Course Updated: 08/10/2021

With nearly 10,000 training videos available for desktop applications, technical concepts, and business skills that comprise hundreds of courses, Intellezy has many of the videos and courses you and your workforce needs to stay relevant and take your skills to the next level. Our video content is engaging and offers assessments that can be used to test knowledge levels pre and/or post course. Our training content is also frequently refreshed to keep current with changes in the software. This ensures you and your employees get the most up-to-date information and techniques for success. And, because our video development is in-house, we can adapt quickly and create custom content for a more exclusive approach to software and computer system roll-outs.

This course aligns with the CAP Body of Knowledge and should be approved for 5 recertification points under the Technology and Information Distribution content area. Email info@intellezy.com with proof of completion of the course to obtain your certificate.

Who this course is for:

Students wishing to learn proper Visual Basic programming techniques.

Excel 365 VBA, Expert

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 3min

Getting Started with VBA10 lectures • 1hr 3min

Working with Variables4 lectures • 22min

Working with Conditional Logic8 lectures • 38min

Working with Strings and String Functions4 lectures • 23min

Working with Loops6 lectures • 36min

Working with Sub Procedures Options and Functions6 lectures • 44min

Working with User Forms4 lectures • 38min

Debugging your Code7 lectures • 42min

Conclusion1 lecture • 2min

Requirements

Description

Who this course is for: