Excel VBA

Course details

                                What is 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 MicrosoftOffice programs, like Word and PowerPoint.

Why learn Excel VBA?

Excel VBA enables you to use English likestatements to write instructions for creating variousapplications. It also allows you to enhance Excelfunctionality 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 Updated on 10 April, 2023