Learning Programming with Microsoft Excel Macros and VBA

Kick start programming journey with Macros in Excel, while learning Objects programming, Looping, Variables, Data Types, Conditional Statements, etc. This course will explore the MS Excel program capabilities with Macros and VBA offerings, emphasising foundational features of the VBA. Avoid boring and repetitive routine tasks in MS Excel with Macros recording and execution. Discover how to programmatically approach tasks in Microsoft Excel by manipulating objects in-depth and performing repetitive mundane tasks with VBA codes.

Course Contents

Day One

The VBA Anatomy

• What is VBA?
• Recording macros
• Testing macros
• Examining macros
• Modifying macros
• Saving workbooks containing macros
• Understanding macro security

The Visual Basic Editor (VBE)    

Day Two

The Excel Object Model Anatomy

• EXCEL is an object
• The object hierarchy
• Collections
• Referring to objects
• Navigating the objects hierarchy
• Object Properties
• Object Methods
• Object Events
• The VBA Help system
• The Object browser
• Automatically listing Properties & Methods.

The VBA Sub and Function Procedures

• Sub vs Functions
• The Sub Procedures
• The Function Procedures
• Executing Sub Procedures
• Executing the Sub Procedures directly
• Executing procedure from Macro dialog box
• Executing a macro with shortcut key
• Executing procedure for button, shape, or icon
• Executing Function procedures
• Calling the Function from a Sub procedure
• Calling a Function from a worksheet formula.

The Excel Macro Recorder

• Recording basics
• Recording in relative mode
• Recording in absolute mode
• Recording options.

Day Three

Essential VBA Language Elements

•   Using comments
•   Understanding variables
•   VBA data types
•   Declaring and scoping variables
•   Working with constants
•   Working with strings
•   Working with dates
•   Assignment statement.

Range Objects

•   Ways of referring to a Range
•   Cells property
•   Offset property
•   The Value property
•   The Text property
•   The Count property
•   The Columns & Rows property
•   The Address property
•   The HasFormula property
•   The Font property
•   The Interior property
•   The Formula property
•   Working with some Range Object Methods
•   The Select method
•   The Copy and Paste method
•   The Clear method
•   The Delete method.

Day Four

Controlling Program Flow and Making Decisions

•   Understanding the Flow
•   The GoTo Statement
•   The IF-Then Structure
•   The Select Case Structure
•   For-Next loop
•   Do-While loop
•   Do-Until loop
•   For Each-Next loop.

Day Five

Using VBA Worksheet Functions

•   Understanding functions
•   Using VBA built-in functions
•   VBA function examples
•   VBA functions that do more than return values
•   Using worksheet functions in VBA
•   Using custom functions.