Excel VBA Macros Course

Excel VBA programming can seem baffling, especially when you don’t fully understand the code you are asked to write. Learn to write Excel VBA macros, from scratch, to automate Microsoft Excel tasks and operations.

This course is aimed particularly at Excel users without much programming experience, who struggle to remember the syntax and structure of the VBA (Visual Basic for Applications) programming language.

During the course, we will use a style of programming that emphasizes the significance of each line of code that you write. This means that when you revisit the code, the meaning of each line is apparent from the syntax, rather than becoming more and more unfamiliar. The course also demonstrates how to get the maximum benefit from IntelliSense, Microsoft’s code completion feature.

Whether you are looking to enhance your career prospects by adding Excel VBA to your C.V., saving your company money, or increasing your productivity, this course will provide you with all the knowledge you need to get started.

You can download all of the materials used in the lectures so that you can follow along. (Please, remember to unzip the downloaded files.)

INTRODUCTION TO VBA

• What Is VBA?
• What Can You Do with VBA?
• VBA IDE
• Recording the Macro

VISUAL BASIC EDITOR

• The Project Explorer
• The Properties Window
• The VB editor windows
• Creating and Removing Modules
• Renaming Modules

VARIABLES IN VBA

• What are Variables?
• Using Non-Declared Variables
• Variable Data Types

MESSAGEBOX AND INPUTBOX FUNCTIONS

• Customizing Msgboxes and Inputbox
• Concatenating Boxe
• Using Multiple Lines
• Reading Cell Values into Messages
• Asking Questions with Msgboxes / Inputbox

VBA WITH STATEMENTS

• Writing a Simple With Statement
• Creating More Complex References
• Referencing other Objects

IF STATEMENTS

Simple If Statements

• Single line Ifs
• The Elseif Statement
• Nesting Ifs
• Combining Logical Tests

LOOPING IN VBA

• Introduction to Loops and its Types
• The Basic Do Loop Statement
• Exiting from a Loop
• Advanced Loop Examples

FOR NEXT LOOPS

• 1 Using Loop Counters
• The Step Keyword
• Exiting from a For Next Loop
• Looping Through Items in a collection

FOR EACH LOOP

• The For Each Next Statement
• Looping Over Cells
• Nesting for Each Loop

CREATING FUNCTIONS

• Writing Simple Functions
• Calling Functions
• Creating Parameters
• Optional Parameters
• Rewriting Code to Use Functions

ARRAYS IN VBA

• Array Declaration
• Assigning Values to an Array
• Types of Array
• One dimensional, Two dimensional, and multi-dimensional arrays

WORKING WITH SHEETS

• Sheets and Types of Sheets
• Manipulating Sheets:
• Inserting and Deleting
• Copying and Moving
• Hiding and Unhiding

WORKING WITH WORKBOOKS

• Referring to workbooks
• Opening and Creating workbooks
• Saving Workbooks

ERRORS HANDLING AND DEBUGGING

• Types of Error:
• Syntax Errors
• Compile Errors
• Runtime Errors

WORKING ON ACTIONS / EVENTS

1. Worksheet Level Events
2. Workbook Level Events
3. Application.OnTime
4. Working with Excel Range and Data
5. Worksheet and Workbook Operations,
6. Using Excel Functions in VBA
7. Volatile Functions

FILTER AND REPORTING

• AutoFilter
• Filter Object and Criteria(s)
• Advanced Filters
• Creating a PivotTable Report
• PivotCaches and Naming
• Adding PivotFields
• Adding Fields for Calculation
• Creating and Editing Charts

FORM CONTROLS

Understanding User forms and References
Combo box, Radio Button, Check the box
Built-in Dialog Boxes, Calendar
Working with Spin Buttons, Images
Reading data from Excel Sheets
Writing data on Excel Sheet
Project Study on GUI Pattern

MAIL FUNCTIONS – OUTLOOK

• Using Outlook Namespace
• Send automated mail
• Outlook Configurations, MAPI
• Creating Contact
• Create and Schedule Task
• Export Mails Attachments in a specific location

WORKING WITH DATABASES

• Introduction ADO
• Various Connection Drivers
• Database Connection and Recordset Object
• Excel – Access Connectivity – Select, Retrieve, Insert Data Operations
• Excel –SQL Connectivity – Select, Retrieve, Insert Data Operations

WORKING WITH WEB APIS

•                                                         Form submissions, ROBOTs programming
• Fill out automated forms
• Fill HTML Form using VBA
• Robots programming
• Interview Preparation
• Training Certification