Introduction
  • Welcome to the Course - Introduction
  • 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 2013)
  • 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 2013
  • 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