Excel VBA Programming - The Complete Guide

Video description

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!

What You Will Learn

  • Automate tasks and procedures in Excel using Visual Basic for Applications (VBA)
  • Utilize objects in the Excel Object Model to emulate user actions in Excel
  • React dynamically to user events such as entering a cell value or opening a workbook
  • Get comfortable with the fundamentals of computer programming

Audience

This course is for spreadsheet users who want to automate their daily workflow and business analysts who want to remove redundancy from their common tasks. Excel users who are curious about exploring programming in a familiar work environment will also benefit from the course.

About The Author

Boris Paskhaver: Boris Paskhaver is a NYC-based web developer and software engineer with experience in building apps in React/Redux and Ruby on Rails. Raised in New Jersey, he graduated from the Stern School of Business at New York University in 2013 with a double major in business economics and marketing. Since graduation, his work has taken him in a wide variety of directions—he spent years in marketing, then financial services, and now the tech industry.

He has worked everywhere, from a 50-person digital agency to an international tech powerhouse with thousands of employees. He always had a love of learning but struggled with the traditional resources available for education. His goal is to create comprehensive courses that break down complex details into small, digestible pieces.

Table of contents

  1. Chapter 1 : Getting Started
    1. Introduction
    2. Enable the Developer Tab
    3. Excel File Types
    4. Macro Security
    5. The Macro Recorder
    6. Absolute vs. Relative References I
    7. Absolute vs. Relative References II
    8. The Visual Basic Editor
  2. Chapter 2 : The Fundamentals of the Excel Object Model
    1. Object-Oriented Programming in Real Life
    2. Collection Objects in Real Life
    3. Objects as Properties
    4. The Excel Object Model
    5. Access Object from Collection by Name
    6. Default Properties
    7. The Name Property on Workbook and Worksheet Objects
  3. Chapter 3 : The Visual Basic Editor
    1. Visual Basic Editor Options
    2. Create and Delete a Procedure
    3. The Immediate Window and Debug.Print Method
    4. The MsgBox Method
    5. Comments
  4. Chapter 4 : Objects and Methods
    1. Methods without Arguments
    2. Methods with Arguments
    3. Methods with Multiple Arguments
    4. The Object Browser
    5. Ways to Invoke A Procedure
    6. The TypeName Method
  5. Chapter 5 : Variables and Data Types
    1. Syntax Tips
    2. Variable Declarations and Assignments
    3. Multiple Variable Declarations
    4. The Option Explicit Setting
    5. The Byte, Integer and Long Data Types
    6. Mathematical Operations
    7. The Single and Double Data Types
    8. The String Data Type
    9. The Boolean Data Type
    10. The Date Data Type
    11. The Variant Data Type
    12. The Object Data Type
    13. Default Values for Declared Variables
  6. Chapter 6 : Procedures
    1. Variable Scope
    2. Call A Procedure from Another one
    3. Procedures with Arguments
    4. Procedure Scope (Public vs. Private)
    5. The Exit Sub Keywords
    6. Constants
    7. Predefined Constants
  7. Chapter 7 : Object Deep Dive
    1. The Application Object
    2. The Application.DisplayAlerts Property
    3. The Workbooks.Count and Worksheets.Count Properties
    4. The Workbooks.Open Method and Workbook.Path Property
    5. The Workbooks.Close Method
    6. The Workbooks.Add Method
    7. The Workbook.SaveAs and Workbook.Save Methods
    8. The Workbook.Activate Method
    9. The Workbook.Close Method
    10. The Worksheets.Add Method
    11. The Worksheet.Visible Property
    12. The Worksheet.Copy Method
    13. The Worksheet.Delete Method
    14. The Worksheet.Move Method
  8. Chapter 8 : Range References
    1. The Range.Select Method
    2. The Value vs. Text Properties
    3. R1C1 Notation, Part I
    4. R1C1 Notation, Part II
    5. The Formula and FormulaR1C1 Properties
    6. The Range.Offset Property
    7. The Range.Resize Property
    8. The Cells Property
    9. The Range.CurrentRegion Property
    10. The Range.End Property
    11. The Range.Count and Range.CountLarge Properties
    12. The Range.Row and Range.Column Properties
    13. The Range.Rows and Range.Columns Properties
    14. The Range.EntireRow and Range.EntireColumn Properties
    15. Get Last Row of Data in Worksheet
  9. Chapter 9 : Range Actions
    1. The Range.FillDown Method
    2. The Range.Replace Method
    3. The Range.TextToColumns Method
    4. The Range.Worksheet Property
    5. The Range.Sort Method
    6. The Range.Font Property
    7. The Range.Interior Property
    8. The Range.ColumnWidth and Range.RowHeight Properties
    9. The Range.AutoFit Method
    10. The Range.Clear, Range.ClearContents and Range.ClearFormats Methods
    11. The Range.Delete Method
    12. The Range.Copy and Range.Cut Methods
    13. The Paste and PasteSpecial Methods on the Worksheet Object
    14. The Parent Property on All Objects
  10. Chapter 10 : Conditionals
    1. Boolean Expressions
    2. The If Then Statement
    3. The ElseIf and Else Statements
    4. Select Case
    5. The AND OR Logical Operators
    6. The NOT Operator
  11. Chapter 11 : Iteration
    1. The For Next Loop
    2. The Step Keyword
    3. Deleting Rows
    4. The For Each-Next Construct
    5. Iterating over a Range of Cells with For Each
    6. The With-End With Construct
    7. Exit For and Review of Exit Sub
  12. Chapter 12 : Miscellaneous Features
    1. The MsgBox Method In Depth, Part I
    2. The MsgBox Method In Depth, Part II
    3. StatusBar
    4. The Application.ScreenUpdating Property
    5. SpecialCells
    6. The InputBox Function
    7. The Application.InputBox Method
  13. Chapter 13 : Arrays
    1. Intro to Arrays
    2. Alternate Syntax for Fixed-Size Arrays
    3. The Option Base 1 Syntax and Write Array Values to Cells
    4. Initialize Arrays within a For Loop
    5. The LBound and UBound Methods
    6. Dynamic Arrays
    7. The Range.RemoveDuplicates Method
  14. Chapter 14 : Functions
    1. VBA Functions, Part I
    2. VBA Functions, Part II
    3. The Split Function
    4. The Is Family of Functions
    5. Date and Time Functions
    6. More Date and Time Functions
    7. Excel Worksheet Functions
    8. Custom Functions
  15. Chapter 15 : Debugging
    1. Intro to Error Handling
    2. The OnError and GoTo Keywords
    3. The OnError Resume Next Keywords
    4. Error and Err.Number
    5. Stepping Through Code
    6. Breakpoints
  16. Chapter 16 : Events
    1. Introduction to Events
    2. The Worksheet_SelectionChange Event
    3. Review of Application.EnableEvents
    4. The Worksheet_Change Event
    5. The Worksheet_Activate Event
    6. Workbook Events and The Sh Argument
    7. The Workbook_Open Event
    8. Procedures with Boolean Arguments + The Workbook_BeforePrint Event
  17. Chapter 17 : User Forms
    1. Create UserForm, Toolbox, Properties, Controls
    2. The Label and TextBox Controls
    3. Naming Conventions
    4. Design Aesthetics
    5. The CommandButton Control
    6. Add Event Procedure to Control
    7. Unload and Hide a UserForm
    8. Submit the UserForm
    9. Activate a UserForm from Procedure
    10. The initialize Event
    11. The ListBox Control I - Wire up the Form
    12. The ListBox Control II - React to User Selection
    13. The ListBox Control III - Select Multiple Items
    14. The ComboBox Control I
    15. The ComboBox Control II
    16. The CheckBox Control

Product information

  • Title: Excel VBA Programming - The Complete Guide
  • Author(s): Boris Paskhaver
  • Release date: August 2019
  • Publisher(s): Packt Publishing
  • ISBN: 9781839214240