VBA
Excel VBA – Course Outline
Introduction to VBA and Macros
The fundamentals of VBAand Macros
The difference between VB and VBA
Preparing a workbook for Macros
Displaying the Developer Tab
Setting the Macro Security level
Saving a file containing Macros
Recording Macros
Recording a Macro
Creating a macro by using Absolute and Relative Reference
Adv Excel & VBA
The Macros Dialog box
Running a Macro by using Keyboard shortcut and Quick Access toolbar
Creating Objects to Run Macros, Assigning a Macro To An Object
The Excel VBA IDE (Integrated Development Environment)
Launching Microsoft Visual Basic
Microsoft Visual Basic interface/ Editor
Menu bars and Tool bars
The Project Explorer window
Code Window
Object Browser
Properties Window
Running Procedures From The Editor
VBA Fundamentals
VBA: An event driven language
VBA: An object-based language
Procedures: Function and Sub
Code Modules
Basic Data Input and Output – Message Box and Input Box
Excel Objects
Objects Defined
Objects - The Application Object, The Workbook and Window Objects, The
Worksheet Object, The Range Object
Collection Objects
Hierarchy of Objects
Objects: Methods and Properties
Using Object References (Selecting column, row, range; setting value)
Variables and Data Types
Declaring and using Variables
Implicit & Explicit Declarations
Variable Types
The Scope of Variables - Procedure Level Scoping, Module Level Scoping, Public
Scoping of Variables
Passing Variables By Reference, Passing Variables By Value
Avoiding Variant Data Types
Using Arrays
Programming Techniques
Programming convention
Program structure and syntax
Writing VBA Code
Code indentation
Comments
Procedures and User-Defined Functions
Creating a Procedure
Making Sense Of IntelliSense
Passing Data Between Procedures
Calling Procedures from Other Procedures
Basic data input and output – Message Box and Input Box
Creating a User Defined Function
Passing Arguments to Functions
Calling Functions from Other Procedures
Calling Functions directly in Excel
Events
Workbook Events
Worksheet Events
Control Structures &Program Flow
Determining the Flow of a Program
If (ElseIf, Else)
Select Case()
For ... Next Loop, For ... Each Loop
Do … Loop , Do … Until , Do … While
With ... End With
Nesting Code
User Forms
Creating and displaying a user form
Form Controls
Basic controls – Label, Text-Box, Command-Button, List items – List-Box,
Combo-Box
Mouse-controlled items – Check-Box, Option-Button(Radio Button), Toggle-
Button
Display items – Frame, Tab-Strip, Multi-Page, Scroll-Bar, Spin-Button, Image
Setting Form Properties, setting Form control Properties
Use of form events, procedures and functions
Running and displaying a user form
Programming Custom Forms
Initializing The Form
Closing the Form
Creating Error Checking Procedures, Running a Form From a Procedure
Debugging Code and Error handling
Understanding errors
Identifying compile-time, run-time and logical errors in code
Using debugging tools
Setting Break Points In The Editor
Stepping Through a Procedure
Using break mode during run mode
Understanding VBA's error trapping options
Elegant IT Services