Excel VBA Training

Curriculum:

Introduction:

• Event-oriented programming
• How to add Developer Tab into your Excel instance
• Code Window, Properties Window, Project Explorer, Tool Bars

Variables And Constants:

• Objects -Properties and Methods
• Variables
• Data Types
• Difference between variables and Objects

Option Explicit:

• Explicit variable declarations using the Option Explicit statement
• Implicit variable declarations

Variables And Scoping:

• Scope Of Variables in Sheets, User Forms, Modules

VBA Functions:

• Built-in Functions
• Sending Messages To The User, Lcase, Ucase, Len, Trim, Format etc

Conditional Branching:

• The If Statement, Using If For Single Conditions
• Using If For Multiple Conditions, If Then Else End If, If Then Else ElseIfEnd If, The Select
• Case Statement

User Defined Procedures And Functions:

• Creating and calling User-Defined Procedures and Functions
• Modifying Procedures and Functions

Iterations (Loops):

• Iterations -FOR Loops
• While ...Do...Until... Loop Statements for looping With Known Or Specified number of iterations
• Endless Loop for Unknown Iterations by using Exit Do statement
• Error Handling
• Error Handling and how it differs from Validation
• Creating Error Handlers In Event Procedures
• Technique to get user input by using The InputBox
• Using The Resume statement, Resume Next statement
• Using The Err Object to get more information about errors
• User Defined Errors by using Raise method

Validations:

• Validation
• Validation using Text Box Events
• Validation on Save using Button

List Boxes:

• Adding List Boxes to A Form
• Simple List Boxes and Multiple List Boxes

Check Boxes, Option Buttons, Frame:

• Adding Controls such as Text Boxes, Labels, Combo Box, Option Buttons, Check Boxes, Coding on various events
• Adding Different Sets of Option Buttons to a Form without mismatch of options by using Frame Control
• Important properties, methods and events of the above controls

Debugging:

• Run Time, Design Time, and Break Mode
• Debugging Techniques such as stepping into, stepping over, and stepping out Run
• Run To Cursor
• Set Next Statement
• Quick Watch and Three types of watches

File System Objects:

• File System Objects (FSO/Microsoft Scripting Runtime Object) Exploring files and folders,interactionof excel file with withText files as well as excel files using FSO method

Multiple Forms, Form Events:

• Programming User Forms by Handling Form Events
• Handling multiple forms

Workbooks Properties And Methods:

• Workbook Methods And Properties, Worksheet Methods And Properties

Range Object Properties And Methods:

• Using Range Objects
• Range Methods And Properties
• Manipulating worksheets and data in the worksheets within same workbook
• Manipulating worksheets and data in the worksheets in different workbooks

Collections

• Collections
• User controls, Forms, Workbooks, Worksheets, Folders, Files

Arrays:

• Simple Arrays, Dynamic Arrays

Formatting Cells, Formulas:

• FormattingCells, Colors, Font etc

Application Object:

• Screen updating, DislayAlerts, Dialog box