Microsoft Excel Expert: VBA and Macros

The first rule of any technology used in a business is that automation applied to an efficient operation will magnify the efficiency. The second is that automation applied to an inefficient operation will magnify the inefficiency. ~ Bill Gates

You can automate almost any task you do in Excel that are long and time consuming such as: reports that need to be produced, data that needs to be updated or tables that need to be formatted on a regular basis. How? Simple. Learn VBA.

Visual Basic for applications (VBA) is a tool used that develops program which controls VBA Macros Excel. A macro is a way to automate a task that you perform repeatedly or on a regular basis.

DESCRIPTION:

This course is designed for participants who want to utilize the power of VBA and use Macros to automate routine when using spreadsheets. Participants will learn the Visual Basic for Applications language as well as the Excel classes and objects needed to create VBA scripts to automate Excel. Furthermore, participants will create procedures and functions using the VBA language and Excel objects as well as debugging and error handling techniques.

LEARNING OBJECTIVE:

At the end of this training course, learners will:

  1. You will learn from the basic concepts to the principles to build your own applications in Excel using VBA.
  2. You will understand and digest the concept of object-oriented programming and overcome your fears in programming codes.
  3. You will be able to retrieve data from databases or other files and present them in Excel files.
  4. You will create automated procedure to format data, display information and create complex reports and graphs.

DURATION:3 DAYS

PRE-REQUISITE: Proficient knowledge on Microsoft Excel.

LEARNING BENEFITS:

At the end of this training course, learners will:

  1. Write instructions that Excel carries out by writing programs in VBA.
  2. Learn how to debug your VBA program.
  3. Prepare reports in a shorter period of time.

TARGET AUDIENCE:

This course is intended for individuals who frequently use Excel 2016, 2013 or 2010 and who need to automate routine tasks, such as supervisors, data analyst, admin and accounting staff, and database programmers.

The course is also ideal for individuals with no prior knowledge of VBA Programming or those who are new and would like to understand the basics of programming.

LEARNING OUTLINE

MODULE 1: INTRODUCTION TO VBA

Microsoft Visual Basic Fundamentals

The Microsoft Visual Basic Interface

VBA in Visual Basic

Macros

Writing Code


MODULE 2: VARIABLES AND DATA TYPES

Variables

Introduction to Data Types

Integral Numeric Variables

Decimal Variables

Any-Type Variables

The Scope or Lifetime of a Variable

The Access Level of a Global Variable


MODULE 3: VBA OPERATORS AND OPERANDS

VBA Operators

Arithmetic Operators


MODULE 4: THE PROPERTIES WINDOW

The Appearance of the Properties Window

Types of Properties


MODULE 5: INTRODUCTION TO FORMS

Forms Fundamentals

Using a Form

The Characteristics of a Form


MODULE 6: INTRODUCTION TO CONTROLS

Controls Fundamentals

Adding a Control to a Container

Control Selection


MODULE 7: INTRODUCTION TO CONDITIONS

Introduction to Boolean Values

Boolean Values and Procedures

Boolean Built-In Functions

Logical Operators

Checking Whether a Condition is True/False

What Else When a Condition is True/False?


MODULE 8: FUNCTIONAL CONDITIONS

Alternatives to a Condition Being True/False?

Conditional Statements and Functions

If-Condition Built-In Functions


MODULE 9: CONDITIONAL SELECTIONS

The Select...Case Statement

Managing Conditional Statements

Loop Repeaters

Loop Counters

Exiting a Procedure or a Loop


MODULE 10: INTRODUCTION TO BUILT-IN FUNCTIONS

Constants, Expressions and Formulas

Fundamentals of Built-In Functions

Accessory Built-In Functions


MODULE 11: PROCEDURES

Introduction to Procedures

Procedures

Introduction to Sub-Procedures

Calling a Sub Procedure

Procedures and Access Levels


MODULE 12: INTRODUCTION TO FUNCTIONS

Creating a Function

Returning a Value from a Function


MODULE 13: ARGUMENTS AND PARAMETERS

Passing Arguments

Techniques of Passing Arguments


MODULE 14: STRINGS

Introduction to Strings

Introduction to Characters

Characters, Strings, and Procedures

Character and String Conversions

The Sub-Strings of a String

Other Operations on Strings

The Message Box

The Input Box


MODULE 15: DATES AND TIMES IN VBA EXCEL

Fundamentals of Dates

The Components of a Date

Formatting a Date Value

Built-In Time Functions

The Components of a Time Value

Operations on Date and Time Values


MODULE 16: ERROR HANDLING

Handling Errors

In Case of Error

Types of Error

The Error Object

OUR PARTNER’S PROFILE

  • Microsoft Certified Professional (MCP), Microsoft Office Specialist (MOS), Microsoft Technology Associate (MTA), TESDA NCII Computer Hardware & System Servicing, National TVET Trainer’s Competency, BS Computer Engineer, Rizal Technological University
  • Over 8 Years in IT Professional Training for Government and Corporate Accounts; Certified IT Trainer of BSP, DILG, DepED, Pag-ibig Fund, Coca-Cola, Jollibee, San Miguel Corporation, SM Supermalls
  • Currently Director for Technical Curriculum Development, AICS Cebu
  • Former Academics Manager, 2014 Academic Excellence Award for Most Outstanding Academic Trainer, Informatics Ortigas and Makati Branch
  • Proficient in Office Productivity, Database Management, and Computer Programming

UPCOMING PUBLIC TRAINING SCHEDULE

Please view here.

For inquiries on registration, course content, course customization, and on-site training, please click here.