Excel VBA

Visual Basic for Applications (VBA) is an event-driven programming language implemented by Microsoft to develop Office VBA is Microsoft’s programming

language for Excel and all the other Microsoft Office programs, like Word and PowerPoint.

Why learn Excel VBA?

Excel VBA enables you to use English like statements to write instructions for creating various applications. It also allows you to enhance Excel functionality by making it behave the way you this Excel VBA course,

• You will learn all the basics of VBA like introduction to Macros, VBA data types, variables, arrays, etc.
• You will also learn the advanced concepts of Excel VBA like VBA Excel form control, ActiveX control, VBA operators, subroutines, functions, objects, web scraping with VBA.
• Also, you will find out how to use input boxes and message boxes to interact with end users.
• You will learn practical methods for using macro errors to improve your VBA code. When you complete this course, you’ll be well on your way to using VBA macros in Excel to help you and others automate tasks and operations performed frequently. Why SPOTON Training Institute ?
• Trainers guide the students with both technical and hands-on projects .
• Trainers are the working professionals.
• Tutors support the students to possess strong practical & technical knowledge.
• Trainers pay individual attention to the students and support them in their academic and career needs.
• Tutors train the students with real-time software projects. SPOTON trains the students efficiently to learn Excel VBA to master the art of making macros to optimize their time in also enhance the students with the right blend of theoretical and practical knowledge. KHDA CERTICATE will be provided by the institute.

This course is meant for those that use Microsoft Excel and are looking to become more efficient by automating their daily Excel tasks.

COURSE CONTENT

Module 1: Introduction

• Getting Excel ready

o Enabling the developer toolbar

o Understanding file types supported by macros

o Configure Excel to save as .xlsm by default

• Macros

o Recording & Running Macros

o Edit a Macro

o Debug a Macro

o Different ways of running a Macro

The Visual Basic Environment

o Macro security

• Visual Basic Editor (VBE)

o How to access

o Layout of the window

o Project explorer

o How to rearrange the display window

o Debugging VBA code

o Add breakpoint

o Step into, over, continue

o Adding & Deleting Modules

o Sub & End Sub

o Keyboard Shortcuts

• Introduction to Object-Oriented

Programming (OOP)

Module 2: Message Box

• Customizing Msgboxes and Inputbox

• Reading Cell Values into Messages

• Various Button Groups in VBA

Module 3: Variables

• Using Non-Declared Variables

• Variable Data Types

• Using Const variables

Module 4: Ranges and Selections

• Named ranges

• Range references

• Relative ranges

• Cells, offset, resize, columns, rows, union,intersect, regions

• Referencing tables

Module 5: Looping / Flow Control

• For / Next loops

• Do loops

• While loops

• For / Each loops

Module 6: Selection Statements

• If / Then / Else

• Select / Case

Module 7: Functions

• Functions Overview

• Calling a Function

• User Defined Functions

Module 8: Strings and String Functions

Module 9: Excel Object Model

• The Application Object

• Workbook object and a collection of

Workbooks

• Worksheet Object and Collection

Worksheets

• Objects ThisWorkbook, ActiveWorkbook,

ActiveCell

• Object Selection

• Collection Range

• Object Cells

• Display data on the status bar

Module 10: Event Handling

• VBA – Events

o Worksheet Events

o Workbook Events

o Responding to an event and create an event handler

o Triggering Event Handlers

o Service frequently occurring events

Module 11: Debugging and Error Handling

Module 12: VBA Forms & Controls

• Creating forms

• Calling a form from code

• Using the forms in the sheet

• Filling User form Controls • Filling a Listbox

and Combobox using Loops • User form

Multipage Contro

Module 13: Arrays

• Declaring single-dimension and multi-

dimension arrays

• Retrieving data from an array

• Reasons and examples of why to use arrays

• Dynamic arrays

• Passing arrays

o Reference

o Argument

Module 14: Pivot Tables & Charts

• Introduction and understanding Pivot

Tables and versions

• Examples

• Filtering Datasets

• Using the Data Model

• Creating and customizing charts

• Combo charts

• Advanced charts

• Exporting charts as graphics

• Pivot charts