VISUAL BASIC ANALYSIS (VBA) IN EXCEL – BASIC LEVEL

DURATION: 1 DAY TRAINING / INSTRUCTOR-Led

COURSE DESCRIPTION

Visual Basic Analysis (VBA) is a human-readable and editable programming code that gets generated when you record a macro. Today, it is widely used with other Microsoft Office applications such as MS-Word, MS-Excel, and MS-Access.  MS VBA Excel is Microsoft’s programming language for Office applications such as MS-Excel, MS-Word, and MS-Access. Macros are what most people who write VBA code use.

COURSE OBJECTIVES

Upon completion of the training, learners should be able to:

• Start enabling the developer functions in Excel and record excel commands in Macros

• Use the VBA interface and run VBA Codes

• Understand codes and data structure and the best practices in implementing them

• Create excel-based formulas in VBA interface, apply object in Userforms and implement security 

PREREQUISITES

Attendees of this training are expected to have basic knowledge, understanding, and have experience in utilizing Microsoft Excel.

COURSE OUTLINE

Module 1: Getting Started

• Introduction

• Enable the Developer Tab

• Macro Security

• Macro Recorder Intro

Module 2: Recording Excel Commands using Macro

• Record Excel Subtotal Function to automate criteria-based reports

• Create Dynamic Filters Using Advanced Filter with Macro

Module 3: The Visual Basic Editor

• Introducing VBA Interface

• Introducing Macro related objects

• Creating Simple Commands

• Running Basic VBA Codes

Module 4: Defining Code and Data Structure

• Declaring Variables and Data Types

• Arithmetic and Logical operators

• Defining Visual Basic Operators

Module 5: Programming Basic and Best Practices

• Sequential Programming (Top to Bottom coding)

• Selection Programming (Create Decision)

• Iteration Programming (Create Loop)

Module 6: Creating Excel Based formulas in VBA Interface

• Cell Reference and Name Manager in VBA Interface

• Displaying output of VBA Excel Code to Spreadsheet

• Using CountIf, SumIf , AverageIf in VBA Interface

• Using CountIfs, SumIfs , AverageIfs in VBA Interface

• Creating Vlookup Function

Module 7: The UserForm Interface

• Define and aaply objects in Excel VBA Userforms

• Create basic codes inside VBA Objects

• Create automated reports with VBA Userforms

Module 8: Generate Reports and Data Security

• Creating Data Validation in VBA Objects

• Applying Protect Sheet in VBA Code

• Adding Password to Excel VBA Code