Keep Calm and Study On - Unlock Your Success - Use #TOGETHER for 30% discount at Checkout
Testprep Training

Excel VBA Programming Online Course

Excel VBA Programming - The Complete Guide

Welcome to Excel VBA Programming–The Complete Guide, the most comprehensive VBA course! Visual Basic for Applications (VBA) is a powerful language built on top of popular Microsoft Office applications such as Excel, Access, and Outlook. It allows developers to write procedures called macros that perform automated actions. Anything that you can do in Excel, you can automate with VBA!

Over the course of more than 18 hours of content, we cover VBA from the ground up, beginning with the fundamentals and proceeding to advanced topics including:

  • The Excel Object Model
  • The Visual Basic Editor
  • Objects and methods
  • Variables and data types
  • Writing your own procedures
  • Workbooks and workbook objects
  • Worksheets and worksheet objects
  • Range references
  • Range actions
  • Conditional logic
  • Iteration
  • Alerts
  • Configuring Excel functionality
  • Custom functions
  • Arrays
  • Debugging, even procedures, and user forms

No programming experience is required; complete beginners are more than welcome! VBA is a great language to start with because it lets you master the fundamentals of programming in a familiar work environment. No extra software is necessary: VBA is bundled with all modern versions of Excel.

Excel is the World's most popular spreadsheet software and is available on over 750 million computers worldwide. Whether you use it for professional or personal reasons, VBA can help you remove redundancy in your workflows and accelerates your productivity drastically! Thanks for checking out this course!


Course Curriculum

Getting Started

  • Introduction
  • Enable the Developer Tab
  • Excel File Types
  • Macro Security
  • The Macro Recorder
  • Absolute vs. Relative References I
  • Absolute vs. Relative References II
  • The Visual Basic Editor

The Fundamentals of the Excel Object Model

  • Object-Oriented Programming in Real Life
  • Collection Objects in Real Life
  • Objects as Properties
  • The Excel Object Model
  • Access Object from Collection by Name
  • Default Properties
  • The Name Property on Workbook and Worksheet Objects

The Visual Basic Editor

  • Visual Basic Editor Options
  • Create and Delete a Procedure
  • The Immediate Window and Debug.Print Method
  • The MsgBox Method
  • Comments

Objects and Methods

  • Methods without Arguments
  • Methods with Arguments
  • Methods with Multiple Arguments
  • The Object Browser
  • Ways to Invoke A Procedure
  • The TypeName Method

Variables and Data Types

  • Syntax Tips
  • Variable Declarations and Assignments
  • Multiple Variable Declarations
  • The Option Explicit Setting
  • The Byte, Integer and Long Data Types
  • Mathematical Operations
  • The Single and Double Data Types
  • The String Data Type
  • The Boolean Data Type
  • The Date Data Type
  • The Variant Data Type
  • The Object Data Type
  • Default Values for Declared Variables

Procedures

  • Variable Scope
  • Call A Procedure from Another one
  • Procedures with Arguments
  • Procedure Scope (Public vs. Private)
  • The Exit Sub Keywords
  • Constants
  • Predefined Constants

Object Deep Dive

  • The Application Object
  • The Application.DisplayAlerts Property
  • The Workbooks.Count and Worksheets.Count Properties
  • The Workbooks.Open Method and Workbook.Path Property
  • The Workbooks.Close Method
  • The Workbooks.Add Method
  • The Workbook.SaveAs and Workbook.Save Methods
  • The Workbook.Activate Method
  • The Workbook.Close Method
  • The Worksheets.Add Method
  • The Worksheet.Visible Property
  • The Worksheet.Copy Method
  • The Worksheet.Delete Method
  • The Worksheet.Move Method

Range References

  • The Range.Select Method
  • The Value vs. Text Properties
  • R1C1 Notation, Part I
  • R1C1 Notation, Part II
  • The Formula and FormulaR1C1 Properties
  • The Range.Offset Property
  • The Range.Resize Property
  • The Cells Property
  • The Range.CurrentRegion Property
  • The Range.End Property
  • The Range.Count and Range.CountLarge Properties
  • The Range.Row and Range.Column Properties
  • The Range.Rows and Range.Columns Properties
  • The Range.EntireRow and Range.EntireColumn Properties
  • Get Last Row of Data in Worksheet

Range Actions

  • The Range.FillDown Method
  • The Range.Replace Method
  • The Range.TextToColumns Method
  • The Range.Worksheet Property
  • The Range.Sort Method
  • The Range.Font Property
  • The Range.Interior Property
  • The Range.ColumnWidth and Range.RowHeight Properties
  • The Range.AutoFit Method
  • The Range.Clear, Range.ClearContents and Range.ClearFormats Methods
  • The Range.Delete Method
  • The Range.Copy and Range.Cut Methods
  • The Paste and PasteSpecial Methods on the Worksheet Object
  • The Parent Property on All Objects

Conditionals

  • Boolean Expressions
  • The If Then Statement
  • The ElseIf and Else Statements
  • Select Case
  • The AND & OR Logical Operators
  • The NOT Operator

Iteration

  • The For Next Loop
  • The Step Keyword
  • Deleting Rows
  • The For Each-Next Construct
  • Iterating over a Range of Cells with For Each
  • The With-End With Construct
  • Exit For and Review of Exit Sub

Miscellaneous Features

  • The MsgBox Method In Depth, Part I
  • The MsgBox Method In Depth, Part II
  • StatusBar
  • The Application.ScreenUpdating Property
  • SpecialCells
  • The InputBox Function
  • The Application.InputBox Method

Arrays

  • Intro to Arrays
  • Alternate Syntax for Fixed-Size Arrays
  • The Option Base 1 Syntax and Write Array Values to Cells
  • Initialize Arrays within a For Loop
  • The LBound and UBound Methods
  • Dynamic Arrays
  • The Range.RemoveDuplicates Method

Functions

  • VBA Functions, Part I
  • VBA Functions, Part II
  • The Split Function
  • The Is Family of Functions
  • Date and Time Functions
  • More Date and Time Functions
  • Excel Worksheet Functions
  • Custom Functions

Debugging

  • Intro to Error Handling
  • The OnError and GoTo Keywords
  • The OnError Resume Next Keywords
  • Error and Err.Number
  • Stepping Through Code
  • Breakpoints

Events

  • Introduction to Events
  • The Worksheet_SelectionChange Event
  • Review of Application.EnableEvents
  • The Worksheet_Change Event
  • The Worksheet_Activate Event
  • Workbook Events and The Sh Argument
  • The Workbook_Open Event
  • Procedures with Boolean Arguments + The Workbook_BeforePrint Event

User Forms

  • Create UserForm, Toolbox, Properties, Controls
  • The Label and TextBox Controls
  • Naming Conventions
  • Design Aesthetics
  • The CommandButton Control
  • Add Event Procedure to Control
  • Unload and Hide a UserForm
  • Submit the UserForm
  • Activate a UserForm from Procedure
  • The initialize Event
  • The ListBox Control I - Wire up the Form
  • The ListBox Control II - React to User Selection
  • The ListBox Control III - Select Multiple Items
  • The ComboBox Control I
  • The ComboBox Control II
  • The CheckBox Control

Tags: Excel VBA Programming Online Course