Microsoft Excel - Macros and VBA

by Ken Puls

3h 43m

Auto
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc
Auto
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc
Auto
  • HD
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

Overview

Learn to automate your most repetitive tasks at the push of a button with Excel macros and VBA. In this online course, you will learn to create powerful macros using VBA (Visual Basic for Applications) to really make Excel work for you. With no prior programming experience required, you'll have your daily spreadsheets running like clockwork to save you hours of time and boost your productivity.


Syllabus

Macros vs VBA - What's the Difference?5m 04s

A brief discussion about the differences between macros and VBA, and how this course covers both.

Watch lesson

Setting up the Macro Environment6m 13s

Entry level steps to allow the user to record macros in Excel.

Creating Your First Macro4m 24s

Recording a simple Macro in Excel.

File Types & Saving4m 26s

A discussion on which files types support macros, and why it is critical to save your work before moving forward.

Running Macros: Using the Macro Dialog5m 01s

How to run a macro from the Macro dialog.

Running Macros: Using Keyboard Shortcuts4m 59s

How to run a macro from a keyboard shortcut.

Running Macros: Using Buttons5m 21s

How to run your macro from a worksheet button.

Watch lesson

Navigating the Visual Basic Editor5m 15s

Meet the Visual Basic Editor (VBE) - your coding studio.

VBA Objects & the Object Model5m 24s

A discussion of the different items you'll encounter as you learn to code, and how they relate to each other.

Excel's VBA Object Model5m 46s

A visual view of a portion of Excel's object model.

Where Do I Put My Code?4m 34s

Examining the different code containers and where you should place your code.

Understanding Code: Macro Syntax4m 32s

Looking at the different keywords and structure that make up a valid macro.

Cleaning up Recorded Code6m 41s

Editing the previously recorded code in order to remove unnecessary objects.

Step vs Run5m 33s

How to step through a macro line by line in order to aid in debugging.

Watch lesson

Using Breakpoints5m 02s

How to set and use breakpoints during code development and debugging.

What are Variables?5m 05s

A discussion of what variables are, and what they do for us when coding.

Creating Variables5m 39s

Setting up variable dimensions and ensuring the code does so in the correct location.

Setting Variables6m 54s

How to assign strings, values or objects to variables and use them in your code.

Explicit vs Implicit Variables6m 11s

Why forgetting to set a simple flag can burn you in the long term.

Using the Locals Window5m 23s

Working with the locals window to help you debug and explore the object model.

Using the Immediate Window6m 00s

Exploring the benefits of the Immediate window for logging and querying, as well as writing when needed.

Using the Watch Window5m 19s

How to use the Watch window to break code execution when variable conditions are met.

Using the Stop Keyword4m 54s

How the Stop keyword can be used during code development similar to a breakpoint.

With Blocks5m 25s

How "With" blocks can tighten up your code and ensure your code targets the objects you expect.

Logic Tests: If Then Else6m 44s

Implementing If/Then choices in VBA.

Logic Tests: Select Case6m 06s

Understanding how the Select Case construct adds another logic test to your coding arsenal.

Loops: Basic Looping with Do Loops5m 01s

Basic looping including counting iterations and exiting.

Loops: Looping under Conditions with Do While/Until Loops5m 37s

More advanced looping by looping while or until a certain condition is met.

Loops: Looping X Iterations with For X to Y Loops5m 11s

Running a loop a set number of times.

Loops: Looping Through Collections with For Each X in Y Loops5m 39s

Using a For Each loop to cycle through each object in a collection such as each worksheet in a workbook's worksheets.

Calling Other Macros6m 15s

Setting up a master macro allowing you to call other macros from a single source.

Creating VBA Message Boxes5m 49s

How to provide feedback to your user via the VBA MsgBox object.

Collecting Feedback from a VBA MsgBox6m 38s

Identifying which button the user clicked when presented with a MsgBox in order to use their response in our code.

Collecting Feedback from a VBA InputBox5m 39s

Working with the VBA InputBox to prompt the user to enter information and capture it for later use.

Forcing User Input5m 04s

Forcing users to enter data when requested.

Error Types5m 15s

How to trigger various errors in VBA, and what they mean.

Trapping and Handling Errors5m 08s

How to set up an error trap in VBA to handle errors.

Building Error Handlers5m 11s

Setting up an error handling section for your macro.

User Defined Function (UDF) Syntax3m 51s

The syntax signature for a UDF and how it differs from a standard subroutine.

Creating User Defined Functions (UDFs)5m 35s

Creating a UDF to return the user name.

Calling a User Defined Function (UDF)5m 17s

Calling a UDF from a worksheet and from VBA.


Description

Highlights:

  • 42 practical tutorials.
  • Understand the differences between macros and VBA.
  • Run your macro from the Macro dialog, a keyboard shortcut or worksheet button.
  • Understand the syntax and structure that make up a valid macro.
  • How VBA Objects and the Objects model relate to each other.
  • Using step vs run in entry level debugging.
  • How to create variables and assign strings, values or objects.
  • Implementing logic tests in VBA.
  • Set up looping with Do loops and Do While loops.
  • Create a VBA Message Boxes.
  • Set up an error trap in VBA to handle errors.
  • Create a User Defined Function (UDF).

Video tutorials are recorded in Microsoft Excel 2016 for PC. Learn more about how a GoSkills Excel certification can boost your career.

Once enrolled, our friendly support team and tutors are here to help with any course related inquiries.


Accreditations and approvals

CPD - The CPD Certification Service.

Reviews1

A useful introduction to understanding and writing appropriate coding language in Excel.

— Rebecca Naughten

View all reviews