Automating Tasks with Microsoft Excel VBA

Course Modules

Module 1 – Introduction to Microsoft Excel As

 A Development Tool

Overview of the Excel Objective and how to use them to design applications.

Lesson

• Introduction to Excel Objects
• Know what Excel Objects is (Application, Workbook, Worksheet, Range)
• Getting and Setting Properties
• Get and Set Excel Object properties
• Calling Methods
• Use Excel Object Method
• Using Application Object
• Know Excel = Application in VBA
• Working with Application Properties
• Get and Set Application Properties (Caption, Path, Window State, DisplayStatusBar, Display Formula Bar)
• Working with Application Methods
• Use Application Methods (Close, Calculate)
• Using Workbook Object
• Know Workbook in VBA
• Working With Workbook Properties
• Get and Set Workbook Properties (Name, Path, Saved)
• Working with Workbook Methods
• Use Workbook Methods (Activate, Close, Protect, Save, Save Copy As)
• Using Worksheet Object
• Know Worksheet Object in VBA
• Working with Worksheet Properties
• Get and Set Worksheet Properties (Index, Name, Used Range, Visible)
• Working with Worksheet Methods
• Use Worksheet Methods (Activate, Calcilate, Delete)
• Using Range Object
• Know Range in VBA
• Working with Range Properties
• Get and Set Range Properties (Count, Dependents, Name, Value, Formula, Text)
• Working with Range Methods
• Use Range Methods (Calculate, Clear Contents, Copy)

Module 2 – Language Used In Manipulating

Excel Objects

Focuses on the Language use to Manipulate Excel Objects, i.e., Microsoft Visual Basic for Applications (VBA). You will be given an Insight into the most important concepts of VBA and will be introduced to its Basic Components.

Lesson

• Introduction to Visual Basic for Application (VBA)
• Work Around in Visual Basic Editor
• Quick Look at VBA
• Know How to Write Simple VBA Code
• VBA Subroutine
• Write Subroutine and Function
• Inserting a VBA Module
• Insert a Module into Excel Project Explorer as Container of Subroutines and Functions
• Entering and Executing VBA Routine
• Create and Run Subroutine that Created
• Understanding with Variables
• Know what Variable is
• Understanding with Data Types
• Know Available DATA Type in VBA
• Dimensioning a Variable
• Write Code to Declare Variable
• Using Variable
• Use Variable to Capture data
• Advantages of Variable
• Know the Advantages of Declaring Variable During Coding Time
• Working with Object Variable
• Declare Variable of Excel Objects
• Using Optional Variable and Variants
• Declare Variable of Variant Type
• Forcing Variable Declaration
• Set Option Explicit to Force must Declare Variable
• Calling Routines
• Call Other Subroutines form a Subroutine
• Passing Data
• Pass Data to Other Subroutines for Manipulation
• Displaying Message Using MsgBox
• Show Message Box on Screen During Run time to Display Information
• Entering Data Using InputBox
• Gather User Input During Run Time
• Understanding the Scope of Variable, Procedure, Module and Project
• Knowing Variable, Procedure, Module and Project
• Branching and Looping
• Write the VBA Syntax to Control the flow of Execution of Subroutine
• If – Then – Else – Elself
• For – Next
• Do – Loop
• Select Case
• For – Each – Next
• With

Module 3 – Designing And Creating Worksheet

Based Forms

The User Interface is one of the most Important Aspects of a Custom Application; it Governs how the user Interact with the Application. This Module Cover Numerous Objects that can be used to Design Custom Interfaces in Excel.

Lesson

• Introduction to Control and Forms
• Know Controls and Forms in VBA
• Placing Controls on a Sheet
• Able to Create user form and place controls on it
• Setting Control Properties
• Set Control Properties
• Writing Event Handling Code
• Write Code to Handle what to do when an event has happened to the control on the user form
• Running Event
• Able to Write Code to Execute Event that Happened
• Understanding the Common Properties and event of Controls
• Know Common Properties and Event of Controls
• Using Checkbox Control
• Able to Create Checkbox to Get User Response
• Using Option Button Control
• Able to Create Option Button to get User Response
• Using Toggle Button Control
• Able to Create Toggle Button to Run Event
• Using Listbox Control
• Able to Create listBox to Provide Options to User
• Using ComboBox Control
• Able to Create ComboBox to Provide Options to User
• Using ScrollBar and SpinButton Controls
• Able to Create ScrollBar and SpinButton on Userform
• Using TextBox Control
• Able to Create TextBox to get user input and to set Value od Textbox
• Using Label, Image and Frame Controls
• Able to use Label, Image and Frame Controls
• Using Userform
• Write code to use designed userform

Module 4 – Debug Techniques

Debugging is the most Important and Probably the Least Understood Aspect of Programming. No one Writes Perfect Code on the first Try. Being able to efficiently locate and correct the mistakes you’ve made is a significant part of what Separates a great programmer from a skilled amateur.

Lesson

• Identifying Code Errors and Program Bugs
• Able to Identify and Correct Program Bugs
• Basic Debugging Techniques
• Able to use Message Box to Help to Check Code Outcome
• Able to use Immediate Window
• Able to use Locals Window
• Able to use Watch Window
• Able to use debug.Print Command
• Establishing Breakpoints and Stepping Through Code
• Able to use Breakpoints to Run the Code Line by Line to Locate Code Errors

Module 5 – Case Study

This Module integrates all the relevant knowledge and builds some spreadsheets with VBA as well as customizes to manipulate data retrieved from client’s SRT system.

Lesson

Project:

• Calculate Statistic
• Event Programming
• Basic User Form
• Modify Current Code to cater new scenarios