Course Curriculum For Excel VBA Training

Introduction
  • Welcome to the Course – Introduction 
Overview of VBA and of the tool
  • The power of Excel with VBA, The VBA code, Recording, Saving and Running Macros 
  • Overview of the Excel Application as a Tool (Version 2016) 
  • Macro Security
  • Protect your work 
  • VBE – The VBA editor, VBA toolbar (Part I) 
  • VBE – The VBA editor, VBA toolbar (Part II) 
  • VBE – The VBA editor, VBA toolbar – Version 2016 
  • The Developer Ribbon 
  •  
The VBA language
  • Understanding the code, Remove the concept “VBA is HARD!”, The Debugging tool
  • VBE to analyze recorded macros, Objects, Methods, Properties, Code cleaning and 
Functions, subroutines, constants and variables
  • Private and Public functions and subroutines, variables by val and optional vari 
  • Functions, subroutines, constants and variables 
  • Recap on Functions and Subroutines 
  • Variables, Private and Public variables
  • Recap on Variable and Constants 
Declare functions 
  • Options: Explicit, Implicit 
  • Declare variables, declare functions in libraries, Types 
  • Dimension variables (Dim, Redim, Redim Preserve) 
  • Dimension variables 
Objects
  • Objects, Excel Objects (Worksheets as objects, Workbooks as objects) 
  • Worksheet properties and events 
  • Workbook properties and events 
  • Cells and Ranges as objects, VBA object in Excel sheets 
  • Assign events to objects in Excel sheets 
  • Objects in VBA and Excel 
Ranges and cells
  • The Range Object 
  • Range properties and selection tips, Refer to Ranges in other sheets 
  • The Offset property, Columns and Rows to refer to Ranges 
  • Retrieve the address in ranges, Size and resize ranges 
  • Columns and Rows to refer to Ranges, Join multiple ranges 
  • Merge ranges, select ranges 
  • Intersect ranges 
  • IsEmpy function 
  • CurrentRegion property 
  • Areas Collection properties 
  • Evaluate positions in Excel sheets with the range properties (End, Row, Column) 
  • identify ranges and cells in the object hierarchy, the value property 
  • Format ranges – create an automatic routine
  • Special cells 
  • Cells and Ranges
VBA Dialogs
  • The Msgbox object, Msgbox properties, Predefined Dialogs 
  • Standard Excel Dialogs, Customize a dialog box 
  • VBA constants, VBA constants for dialogs 
  • Combine VBA constants for complex statements in dialogs 
  • The InputBox 
  • How to find VBA constants and their references 
  • Dialog Boxes in VBA 
Special functions
  • User-defined functions, Excel functions in VBA functions 
  • Custom Excel functions, complex functions
Loops
  • For..Next Loops 
  • Do Loops, While..Wend loops, Do While Loops, Do Until Loops 
  • For Each – The VBA loop: loop in objects 
  • Loops in VBA
Operations and logical instructions
  • Operators, The MOD instruction, The logical instructions 
  • The IF instruction, IF, ELSE, ELSEIF… 
  • With instruction 
  • Instruction to interrupt the execution of the code 
  • Select case instruction 
  • Conditional Instructions
Formulas in ranges and Excel objects
  • Formulas, R1C1 style formulas, Display R1C1 style formulas in Excel, 
  • Retrieve ranges in Excel objects 
  • Conditional formatting of R1C1 formulas 
  • Range properties to be used to retrieve information, The R1C1 reference style 
  • Excel Formulas 
Object events and properties
  • Events and properties in worksheets, workbooks and charts 
  • The Workbook object 
  • The Chart object 
  • The Worksheet object, Default events (used and not used) 
  • Custom events and properties within Excel objects 
  • The Application object 
  • Excel Objects events and properties 
Forms
  • User Forms, Create a Userform 
  • The objects, the code, the controls 
  • Form events, User Interaction Methods 
  • Referring to controls within a form, Design a form 
  • Create interactions between controls 
  • Example of a complex Form 
  • Example of Form with overview of the controls and their properties and methods 
  • Tabs in Forms, Execute a Form from an Excel sheet 
  • Manage multiple forms and access the information of commands in a form 
  • Add references to other object in a form: the example with the calendar and the 
  • Example with tabs and formatted commands 
  • Richtext box in forms, show and hide forms 
  • Add images and reference to websites in a form 
  • Add commands at run time 
  • Scrollbars and textboxes 
  • Add help information to commands, change cursor icon 
  • Form properties: tabindex and management of the focus of the commands 
  • Change the layout of Forms using APIs 
  • Some examples 
  • Summary of the User Form 
Charts in VBA
  • Charts, Embedded Charts and ChartSheets, How to create a chart using VBA 
  • Locate Charts, Customize Charts, Label Charts, Identify the Chart Points 
  • Retrieve Data from a Chart, The Chart Types, Format the Series Collections 
  • Interactive Charts 
  • Sources and Axes, Format Charts 
  • Summary of Charts and Chart Objects

Wrap up

For any inquiry, please contact Atal @9818569825 or write to me at atal.upadhyay@gmail.com. Best yet cheapest training guaranteed.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.