Automate your Excel workflow, accelerate your productivity, and master the fundamentals of programming with VBA!
About This Video
In Detail
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 …
Automate your Excel workflow, accelerate your productivity, and master the fundamentals of programming with VBA!
About This Video
In Detail
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:
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!
Who this book is for
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.
Chapter 1 : 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
Chapter 2 : 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
Chapter 3 : The Visual Basic Editor
Visual Basic Editor Options
Create and Delete a Procedure
The Immediate Window and Debug.Print Method
The MsgBox Method
Comments
Chapter 4 : Objects and Methods
Methods without Arguments
Methods with Arguments
Methods with Multiple Arguments
The Object Browser
Ways to Invoke A Procedure
The TypeName Method
Chapter 5 : 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
Chapter 6 : Procedures
Variable Scope
Call A Procedure from Another one
Procedures with Arguments
Procedure Scope (Public vs. Private)
The Exit Sub Keywords
Constants
Predefined Constants
Chapter 7 : 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
Chapter 8 : 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
Chapter 9 : 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
Chapter 10 : Conditionals
Boolean Expressions
The If Then Statement
The ElseIf and Else Statements
Select Case
The AND OR Logical Operators
The NOT Operator
Chapter 11 : 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
Chapter 12 : 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
Chapter 13 : 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
Chapter 14 : 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
Chapter 15 : Debugging
Intro to Error Handling
The OnError and GoTo Keywords
The OnError Resume Next Keywords
Error and Err.Number
Stepping Through Code
Breakpoints
Chapter 16 : 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
Chapter 17 : 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