What you’ll learn

Recording macros and using buttons

• Absolute and relative recording
• Storing and retrieving macros
• Assigning macros to buttons

VBA Programming Basics

• Getting help in VBA
• Creating Subroutines and Functions
• Data type and variable
• Comment your codes

The VBA code editor

• Insert modules
• Using Project Explorer
• Using Immediate Window
• Using Locals Window
• Using Watch Window
• Using Step Into
• Setting Breakpoint

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 For Each….. Next Loop

Looping over collections

• Object variables
• Looping over collections

Automating Excel PivotTables using VBA

• Creating an Excel Pivot Table report using VBA
• Adding Fields to the Pivot Table
• Reference Pivot Fields and Pivot Items using VBA
• Filtering data using VBA
• Removing fields from the pivot table
• Updating and refreshing the pivot table
• Changing Data source of the pivot table

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 Arrays

• Declaring arrays
• Declaring multidimensional arrays
• Declaring dynamic arrays

Working with UserForm

• Creating and calling a userForm
• Better users experiences with form controls
• Data validation
• Hiding a userForm
• Text box, button, label, optionbox, list box, checkbox, combo box
• Adding Event-handler procedure
• Introducing userForm control types
• Verifying Field Entry
• Collect userForm Controls input
• Filling userForm Controls with user data

Events Programming

• Disable/Enable Events
• Events for Worksheet
• Events for Workbook
• Events for userForm Controls
• Events for application
• Events Not Associated with Objects
• onTime Event / KeyPress Event

Reporting and Charting

• Consolidating data from different files
• Data cleansing and transformation
• Sorting and filtering data
• Automate the SUM and Vlookup functions
• Formatting the Headers and Footers
• Visualize the categorical data using charts
• Automate the charts' formats

Error Handling Techniques

• On Error statement
• Resuming after an error
• Identifying specific errors