What you’ll learn

Recording macros and using buttons

• Absolute and relative recording
• Macros security settings
• Assigning macros to buttons

VBA Programming Basics

• Getting help in VBA
• Create a subroutine
• Create a function
• Declare a variable
• Create a constant
• Comment your code

The VBA code editor

• Insert modules
• Using Project Explorer
• Using Immediate Window
• Using Locals Window

Range's properties and methods

• The Cells property
• The Offset property
• The entirecolumn and entirerow
• The Value property
• The Text property
• The Count property
• The Column and Row properties
• The Address property
• The Font property
• The Border
• The interior property
• The Formula and FormulaR1C1

Other useful object methods

• The select method
• The copy and paste methods
• The clear method
• The delete method
• The workbook methods
• The worksheet methods

Messages and variables

• Input boxes and message boxes
• Assign Values to Variables
• Declaring variables

Working with Excel Object Model

• An introduction to the Excel object model
• Using object browser
• Create an object variable
• Objects and collections
• Methods and properties
• Display a built-in dialog box

Loops and conditions

• Simple IF conditions
• Multiple IF and SELECT … CASE
• While Loop
• Execute tasks a specific number of times
• Using the For Each Next Loop

Looping over collections

• Object variables
• Looping over collections

Using built-in functions and statements

• Using Excel worksheet functions
• Using the MsgBox function
• Using the InputBox function
• Create and using custom functions

Working with UserForms

• Creating and calling a userForm
• Better users experiences with form controls
• Using Label, text box, button, checkbox and optionbox
• Data validation

Automating Excel PivotTables using VBA

• Creating an Excel Pivot Table report using VBA
• Reference Pivot Fields and Pivot Items using VBA
• Filtering data using VBA