Tailored Courses for IT and Business Professionals


Excel VBA Professional Macro Development

Objectives

This course is designed for experienced spreadsheet users, who may have some knowledge of Macros but no programming experience. The aim of the course is to provide a thorough knowledge of vba programming in Excel. This includes utilising the Macro Recorder, writing simple procedures and functions, and manipulating the most useful excel objects.

Duration

Three Days. Two additional days (days 4 and 5) are available to follow on from the course. These can be taken independently of each other.

Prerequisites

Thorough knowledge and understanding of Microsoft Excel.

Suggested Next courses

Excel vba Statistics and Professional Business Modelling

Topics Covered

Day 1 - Introduction

  • Using the VBA Recorder
    • Recording Macros
    • Watching the Recorder Record
    • Taking advantage of the recorder to save time
    • Using the Recorder to teach you vba
  • 9 Ways to Assign and Run Your Macros
    • The Macro Run Box
    • Shortcut Keys, Menus & Toolbars
    • Graphics and Sheet Buttons
    • Worksheet Events
    • Worksheet cell functions
  • Examples of Useful Macros
  • Writing Custom Functions
  • Beyond Recording Macros - Editing and Writing Code
    • Working with Worksheets and Ranges
    • The Range Object
    • User Interaction - Msgbox and 2 types of Inputbox
    • Common Tasks
    • Introduction to the Object Model
    • Introduction to Variables and Language Structures
  • Programming in the VBA environment
    • Features of the envionment
    • Modules, Worksheets, Dialogs and Charts
    • The VBA Toolbar
    • The Help Reference
    • Runtime, Designtime and Breaktime
    • Understanding Code
    • Making Code Clear: Comments and Indentation
    • The Immediate Window
    • Executing Statements in Break Mode
    • Assigning Values
    • Printing Values to it, from code or self-referentially
    • Debugging
    • Debug Code Window
    • Break Points and Watch Points
    • Stepping Through Code

Day 2 - Features of the VBA Language

  • Language 1: The Object Model
    • Objects, Collections, Events, Properties and Methods
    • The Object Hierarchy
    • Important Objects
    • Active Objects
    • Important Properties and Methods
    • Visual Objects vs Conceptual Objects
    • Statements
    • Assignment & Execution Statements
    • Statements which are both
    • Working with Objects
    • Using Object Variables
    • Using With ... End With
    • Using For Each In Next
    • The Object Browser
  • Language 2: Language Structures
    • Resident Functions and Statements
    • Modules
    • Sub, Function and Event Procedures
    • Control Structures
    • For Next, Do Loop
    • If Then Else ElseIf End If
    • Select Case
    • Error Trapping
    • On Error Goto
    • Using Exit Sub, Exit Function and so on
    • Creating Line Names
    • The Subroutine Call Stack
  • Language 3: Variables
    • Standard Types
    • Type Conversions
    • Variants
    • Null, Empty, Zero, “”, & Nothing
    • Object Variables – Using Arrays of Objects
    • Arrays - Fixed, Dynamic
    • Scope and Lifetime
    • Dim, Public, Private, Static
    • User defined types
    • Using Type ... End Type

Day 3 - Utilising the Power of Vba

  • Building Powerful Interfaces
    • Creating User Interfaces and Forms
    • Form design
    • The Menu Editor
    • Creating and Assigning Control and Form Event Procedures
    • Setting Control properties at Design time and Run time
    • Exchanging data between worksheets and dialogs
    • Adding Controls and Dialogs at Runtime
    • Adding Code to Excel Events
    • Using Controls on Worksheets
    • Review examples
  • Coding Specific Excel Aspects
    • Manipulating Charts and Graphics from code
    • Controlling Printing
    • Processing Files
    • Getting and Manipulating Data
    • Review examples

Day 4 – Object Orienting VBA

  • Programming for Integration
    • Interfacing Excel, Access, Word, and VB Stand-Alone .EXEs
    • OLE Automation
    • Automating embedded objects
    • Aside about DDE
    • Creating Reusable Routines
    • Creating VBA Excel Add-ins
    • Review examples
  • Language 4: Creating your own 'objects'
    • Introduction to programming your own objects
    • Designing your own objects
    • Creating Methods and Properties for your objects
    • Property Let, Property Get, Property Set
    • Controlling Access Levels: Scoping revisited
    • Review examples
  • Language 5: Programming Windows API
    • Accessing the Windows API – INI File Example

Day 5 – Workshop / Case Studies Day

Day 5 is available as an additional option to work in depth with case studies on specific subjects or on particular projects you are developing or you intend to develop.

  • Available Case Studies Include
  • Credit Risk Analysis Models
  • Modelling Complex Systems