Visual Basic for Applications courseware and training manuals

We divide our Visual Basic for Applications courseware into two parts - basic VBA macros within Excel and more advanced VBA topics:

You can see details of how to license our Visual Basic for Applications courseware (including volume requirements) here.

Excel VBA Macros courseware

Here is a list of all of our Excel VBA Macros manual chapters (you can download the ones marked):

Chapter Sections Subsections
The Visual Basic Editor (10 pages) The Visual Basic Editor Displaying the Developer Ribbon Tab ; Opening the VB Editor
The VBE Screen Opening and Closing Windows ; Repositioning Windows ; Docking Windows ; Controlling Which Windows are Dockable
The Main VBE Windows The Project Explorer ; The Properties Window ; The Object Browser
VBA Help Help in Office 2013 ; Launching the Help System
VBE Settings The Options Dialog Box ; Changing Font Formatting Options ; General Options ; Displaying Extra Toolbars
Useful Keyboard Shortcuts
Writing Simple VBA Code (14 pages) Modules Inserting a Module ; Opening and Closing Modules ; Renaming Modules ; Naming Rules in VBA ; Naming Conventions ; Removing Modules ; Exporting Modules ; Importing Modules ; Copying Modules to Other Projects
Writing Procedures Types of VBA Procedure ; Inserting Procedures ; Starting a Subroutine ; Switching off Syntax Error Messages ; Setting the Scope of a Procedure
Writing Neat Code Commenting Your Code ; Commenting Out Multiple Lines of Code ; Using Blank Lines and Indenting ; Indenting Multiple Lines ; Changing Indenting Settings ; The Continuation Character
Writing Simple VBA Instructions Objects ; Methods and Properties
Tools to Help with Writing Code Choosing Which Tools are Enabled ; Using IntelliSense to Write Code Faster ; Using Tooltips ; Viewing Data Tips
Useful Keyboard Shortcuts
Saving and Opening Files (6 pages) Saving VBA Code Where is Code Stored? ; Saving VBA Code ; Choosing the Correct File Type
The Personal Macro Workbook Creating the Personal Macro Workbook ; Viewing the Personal Macro Workbook in the VBE ; Viewing the Personal Macro Workbook in Excel ; Saving the Personal Macro Workbook ; Where the Personal Macro Workbook is Stored
Opening Files Which Contain VBA Code Choosing to Enable VBA Content ; Macro Security Settings
Trusted Documents Viewing Trusted Document Settings ; Disabling Trusted Documents
Running VBA Code (8 pages) Running Code from Excel Choosing from a List of Macros
Running Code from the VBE Running a Subroutine ; The Debug Toolbar ; Compiling Code ; Stepping Into and Through Code ; Reaching the End of a Procedure ; Interrupting a Running Procedure
When Things Go Wrong Syntax Errors ; Compile Errors ; Run-Time Errors
Useful Keyboard Shortcuts
Basic User Interfaces (8 pages) Keyboard Shortcuts Assigning Keyboard Shortcuts in Excel ; Assigning Keyboard Shortcuts in Code
Form Control Buttons Drawing Form Control Buttons ; Editing Form Control Buttons
ActiveX Command Buttons Drawing ActiveX Command Buttons ; Attaching Code to the Click Event
AutoShapes and Pictures Inserting Shapes and Pictures ; Assigning a Macro to a Shape or Picture
The Excel Ribbon Modifying the Quick Access Toolbar ; Creating Ribbon Tabs ; Altering Images on Ribbon Tools ; Resizing Ribbon Tools ; Resetting Ribbon Customisations ; Importing and Exporting Ribbon Customisations
Workbooks and Worksheets (10 pages) Working with Workbooks Referring to a Workbook by Name ; Referring to a Workbook by Index Number ; Referencing the Most Recently Opened Workbook ; The Difference between ActiveWorkbook and ThisWorkbook ; Opening a Closed Workbook ; Creating New Workbooks ; Saving Workbooks
Referring to Worksheets Referring to Worksheets by Name ; Why IntelliSense Doesn’t Work with Worksheets ; Referring to Worksheets by Index Number ; Using Sheet Codenames ; Referring to the Active Sheet ; The Difference between Worksheets and Sheets
Activating vs. Selecting Sheets Selecting Multiple Sheets ; Extending the Sheet Selection ; Activating a Sheet in a Selection ; Referencing Multiple Selected Sheets
Creating New Sheets Adding New Sheets ; Controlling the Position of New Sheets ; Adding Multiple Sheets
Deleting Sheets Deleting Single or Multiple Sheets ; Supressing Warning Messages ; Deleting all Charts or Worksheets
Working With Ranges (14 pages) Referring to a Range Object Referring to a Single Cell ; Referring to a Block of Cells ; Using Range Names to Refer to Cells ; Referring to Non-Contiguous Ranges ; An Old-Fashioned Way to Refer to Ranges
The Cells Property Referring to a Cell with Row and Column Numbers ; Referring to a Block of Cells ; Referring to Every Cell on a Worksheet
Rows and Columns The Range, Rows and Columns Properties ; The EntireRow and EntireColumn Properties
Referring to the Active or Selected Cell ActiveCell and Selection ; Activate vs. Select
The Offset Property Offsetting from the ActiveCell ; Offsetting a Block of Cells
The End Property The Four Directions of the End Property ; Finding the Start of a List ; Finding the Bottom Right Corner of a Table ; Finding the Next Blank Cell in a Column ; Selecting from the Top to the Bottom of a List ; Dealing with Blank Cells
Referring to Regions of Cells The CurrentRegion Property ; The CurrentArray Property
Resizing Ranges The Resize Method ; Resizing with the Range Property
Referring to Special Cells Specifying the Type of Values
Referring to Used Cells Referring to the Used Range ; The Last Used Cell ; The Last Populated Cell
Colours in VBA (2 pages)

Download this chapter 		Excel Colours The Two Colour Properties of a Range ; The ColorIndex Colours
Colour Numbers and Names VBA Colour Constants ; Excel’s RGB Constants ; The RGB Function
Displaying Messages (6 pages) The MsgBox Function Syntax and Parameters of a Message Box
Displaying Messages Displaying a Simple Message ; A Note on Using Parentheses ; Concatenating a Message ; Changing Text Alignment ; Using Multiple Lines ; Customising the Title
Icons and Buttons Modifying the Buttons ; Setting the Default Button ; Displaying Icons ; Combining Buttons and Icons
User Inputs (12 pages) Asking Users for Input Where to Store User Input
Asking a Question with a Message Box The Possible Results of a Message Box ; Storing the Result of a Message Box ; Testing Which Button was Clicked
The VBA InputBox Function Syntax and Parameters of the InputBox Function ; Using an Input Box to Ask a Question ; Setting a Default Value ; What Happens if You Click Cancel? ; Inputting Different Types of Data ; Dealing with Data Type Mismatches
The Excel-Specific InputBox Method Using the Excel Input Box ; Customising the Title and Default Value ; Setting the Data Type of the Input Box ; Entering an Invalid Value ; Returning Boolean Values ; Clicking Cancel ; Selecting Cells ; Returning a Reference to a Range ; Creating a Formula ; Returning an Array of Values
Variables and Data Types (14 pages) Data Types in VBA Summary of the VBA Data Types
Declaring and Using Variables The Dim Statement ; Writing To and Reading From Variables
Declared vs. Non-Declared Variables Non-Declared Variables ; Explicitly-Declared Variables ; Forcing Explicit Variable Declaration
Variables and Data Types Data Type Names vs. Type-Declaration Characters ; The Variant Data Type ; Declaring Multiple Variables ; Choosing the Correct Data Type
Converting Variable Data Types Implicit Data Type Conversion ; The Problem with Implicit Type Conversion ; Explicit Data Type Conversion ; Checking for Dates and Numbers ; Determining the Data Type of a Value
The Scope of Variables Procedure Level Variables ; Module Level Variables ; Project Level Variables ; Static Variables and Procedures
Constants Declaring a Constant ; The Scope of Constants
Working with Data (9 pages) Manipulating Data The Three Main VBA Data Types ; The Basic VBA Operators ; Manipulating Values using Functions ; Why Some Functions End with a $ Sign ; Using Excel’s Worksheet Functions
Working with Numbers Testing if a Value is a Number ; Useful Numeric Functions
Working with Dates Testing if a Value is a Date ; Writing Dates in the VBE ; Arithmetic with Dates ; Useful Date Functions ; Intervals for Date Functions ; Setting the First Day of the Week ; Formatting Dates
Working with Strings Concatenating Strings ; Character Codes ; Special Character Constants ; Case Sensitivity ; Useful String Functions
Testing Conditions (9 pages) The If Statement Single-Line If Statements ; The Else Clause ; Block If Statements ; Nested Ifs ; The ElseIf Statement
Logical Tests and Operators Comparison Operators ; Logical Tests and Boolean Values
Combining Conditions The Or Operator ; The And Operator ; The Xor Operator
Comparing Strings Testing if Two Strings are Equal ; Converting the Case of Text ; Making All Text Comparisons Case-Insensitive ; Relative Comparisons with Strings ; The Like Operator and Wildcards
Conditional Functions The IIf Function ; The Switch Function
The Select Case Statement A Basic Select Case Statement ; Testing Multiple Values ; Testing a Range of Values
For Next Loops (4 pages) The For Next Loop Looping a Set Number of Times ; The Step Statement ; Exiting from a For Next Loop ; Nesting For Next Loops ; Looping a Variable Number of Times ; Looping Through Collections
Conditional Loops (4 pages) The Do Loop Exiting from a Do Loop
Do Until Loops Writing a Do Until Loop ; The Loop Until Statement ; Breaking Out of a Loop
Do While Loops Writing a Do While Loop ; The Loop While Statement
The While Wend Statement Writing a While Wend Loop ; Disadvantages of While Wend
How VBA Works (12 pages) Object Oriented Programming The Building Blocks of an Object Oriented Language
Objects Referring to Objects by Name ; Referring to Objects by Index Number ; Qualifying References to Objects ; Using Keywords to Reference Objects ; Using Object Codenames ; Using Object Variables
Collections Referring to Collections ; Adding Items to a Collection ; Counting Items in a Collection
Methods Applying Methods to Objects ; Passing Arguments to Methods ; Returning Values and References from Methods ; When to use Parentheses
Properties Writing to a Property ; Read-Only Properties ; Property Data Types ; Reading from a Property ; Properties and Parameters
Getting Help in VBA The Object Browser ; Context Sensitive Help ; Recording a Macro
For Each Loops (6 pages) Looping Through Collections The For Each Loop ; A Basic Example
Looping Over Worksheets, Charts and Sheets Protecting all Worksheets ; Excluding Worksheets ; Looping Through Chart Sheets ; Looping Through All Sheets ; Looping Through Objects on a Sheet
Looping Over the Workbooks Collection Processing all Open Workbooks
Looping Over a Collection of Range Objects Specifying the Range to Loop Over ; Looping Through a Column of Data
Nesting For Each Loops Looping Over Shapes on All Worksheets ; Looping Through Sheets in All Open Workbooks
Writing Modular Code (7 pages) Modular Code Our Example
Breaking a Procedure into Parts Creating Module Level Variables ; Getting Input from the User ; Retrieving the Related Values ; Building and Showing a Message ; Putting it all Together
Procedures and Parameters Our Example ; Defining Parameters ; Calling a Procedure which has Parameters ; Optional Parameters ; Assigning Default Values to Parameters ; Testing for Missing Arguments
Debugging Modular Code Viewing the Definition of a Procedure ; Stepping Over a Procedure Call
Creating Functions (2 pages) Functions vs. Subroutines Returning a Value from a Function ; Returning a Reference from a Function ; Calling a Function ; Using Functions in a Worksheet ; Defining Function Parameters
Debugging (10 pages) Debugging Code Errors vs. Bugs ; The Debug Toolbar
Running Code Running a Procedure from Start to End ; Running a Procedure in Break Mode ; Stepping Through Code ; Changing the Next Instruction ; Editing Code in Break Mode
Breakpoints Setting and Removing Breakpoints ; The Stop Statement ; Breaking Conditionally
The Immediate Window Executing Instructions in the Immediate Window ; Asking Questions in the Immediate Window ; Printing to the Immediate Window
The Locals Window Observing Variables
The Watch Window Adding an Expression to Watch ; Types of Watch ; Adding a Quick Watch ; Editing and Removing Watches
The Call Stack Displaying the Call Stack ; Using the Call Stack
Useful Keyboard Shortcuts
Handling Errors (12 pages) Run-Time Errors in VBA
Error Handling in VBA Identifying Potential Run-Time Errors ; The On Error Statement
Using the On Error Statement Ignoring Run-Time Errors ; Disabling an Error Handler
Creating a Custom Error Handler Redirecting Your Code ; Writing the Error-Handling Section ; Exiting a Procedure before the Error-Handling Code ; The Complete Example ; Creating Multiple Error Handlers
Resuming After an Error Resuming at the Original Line ; Resuming at the Next Line ; Resuming at a Specified Line ; Why use Resume and Not GoTo?
The Err Object Getting the Error Number and Description ; A Catch-All Approach to Error-Handling
Raising Custom Errors Raising a Custom Error
Errors in Multiple Procedures Creating a Top-Level Error Handler
Events (14 pages)

Download this chapter 		Event Handlers Objects Which Have Events ; Event Procedures vs. Normal Procedures
Creating a Simple Event Handler Accessing the Object’s Code ; Choosing the Event ; Writing the Code ; Triggering the Event
Workbook Events The Before Close Event ; The Before Save Event ; The Before Print Event ; The New Sheet Event ; New Chart
Worksheet Events The Selection Change Event ; The Change Event ; Checking if the Target is Within a Specific Range ; Dealing with Multiple Changed Cells ; Referencing Cells in Worksheet Modules
ActiveX Controls Drawing ActiveX Controls ; Changing Properties of the Control ; Adding Code to the Control’s Events ; Prevent Controls from Taking the Focus
Application-Level Events Declaring a Variable with Events ; Viewing the Events of the Application ; Enabling the Application’s Events
Creating User Forms (16 pages) User Forms Creating a Working Form ; Our Example
Creating a User Form Inserting a User Form into a Project ; Switching Between Form Views ; Removing Forms
Form Properties Changing the Properties of a Form ; Some Common Form Properties ; Choosing Colours ; Setting Font Properties
Form Controls The Toolbox ; Drawing a Control on a Form ; Our Example ; Naming Controls
Manipulating Controls Selecting a Control ; Selecting Multiple Controls ; Resizing Controls ; Moving Controls ; Deleting Controls ; Copying and Pasting Controls
Laying Out Controls The Form Grid ; The UserForm Toolbar ; The Dominant Control ; Aligning Controls to Each Other ; Distributing Controls ; Aligning Controls to the Form ; Making Controls the Same Size ; Changing the Z-Order of Controls ; Arranging Buttons
Grouping Controls Grouping a Set of Controls ; Using Frames to Group Controls
Control Properties Naming Controls ; Naming Conventions for Controls ; Size and Position Properties ; Formatting Properties
Running User Forms (4 pages) Running a Form Choosing to Run a Form ; Closing a Running Form
Navigating a Form Tab Order ; Accelerator Keys ; Keyboard Shortcuts ; The Default and Cancel Buttons
Adding Code to Forms (12 pages) Making Forms Work Our Example
Running User Forms Running a Form as a Developer ; Running a Form as a User
Adding Code to a Form Viewing a Form’s Code
Referring to Forms and Controls Referring to a Form ; The UserForms Collection ; Looping Over the UserForms Collection ; Referring to Controls on a Form ; Looping Over the Controls Collection
Form and Control Events Initialising a Form ; Clicking the Cancel Button ; Clicking the Add to List Button ; Writing Modular Code in Forms
Validating User Inputs The Data Events of a Text Box ; Deciding on Your Validation Rules ; Creating Basic Validation Code ; Selecting the Text in a Text Box ; Ideas for Less-Intrusive Validation ; Resetting the Formatting Properties ; Using Hidden Labels ; Validation at the Form Level ; Setting the Focus to a Control ; Looping over Controls ; Validating Every Text Box in One Pass
Advanced Form Controls (16 pages) Beyond the Basics The Advanced Controls Available
Frames Drawing Frames and Controls ; Looping Through Controls in a Frame
Combo Box and List Box Controls Setting the Row Source ; The List Property ; Adding Items Individually ; Removing and Clearing Items ; Referring to the Selected Item ; Changing the List Style ; Restricting Choices in a Combo Box ; Allowing Multiple Selections in a List Box ; Referring to Multiple Selected Items ; Working with Multiple Columns
Option Buttons Grouping Option Buttons ; Framing Option Buttons ; Setting a Default Option for a Group ; Using the Value of an Option Button ; The Click Event
Check Boxes and Toggle Buttons Check Box and Toggle Button Values ; The Click Event
Spin Buttons and Scroll Bars Drawing Spin Buttons and Scroll Bars ; Scrolling Properties ; The Value Property ; The Change Event ; The SpinUp and SpinDown Events
MultiPage Controls Selecting Parts of a MultiPage Control ; Working with Pages ; The Index and Value Properties ; Looping Through Pages and Controls
TabStrip Controls Working with Tabs ; Drawing Tab Strips and Controls ; Using Tabs in Code ; Looping Over the Tabs Collection
Additional Form Controls (3 pages) Additional Form Controls Adding Items to the Toolbox ; Organising the Toolbox
Examples of Extra Controls The Date and Time Picker ; The RefEdit Control ; Progress Bars
Controlling Other Applications (12 pages) Referencing Object Libraries Setting a Reference to an Object Library ; The Default References ; References and the Object Browser ; Microsoft Office Version Numbers
An Example for Word Setting a Reference to the Word Object Library ; Declaring a Variable for Word ; Creating a New Instance of Word ; Showing and Activating Word ; Creating a New Document ; Writing and Formatting Text in Word ; Copying from Excel to Word ; Saving the Document and Closing Word ; The Complete Example
An Example for PowerPoint Setting a Reference to the PowerPoint Object Library ; Opening PowerPoint and Creating a Presentation ; Creating a Title Slide ; Copying from Excel to PowerPoint ; Moving and Resizing PowerPoint Objects ; Saving the Presentation and Closing PowerPoint ; The Complete Example
An Example for Outlook Setting a Reference to the Outlook Object Library ; The Complete Example
Controlling Applications without References The CreateObject Function ; Using Object Variables ; Converting Constants to Numbers
Crib Sheet (5 pages) VBA Reference Creating Procedures ; Selecting and Activating Things ; Selecting a Range Relatively ; Messages and Inputs ; Declaring Variables ; Conditional Statements ; Looping

Advanced VBA courseware

Here is a list of all of our Advanced VBA manual chapters (you can download the ones marked):

Chapter Sections Subsections
VBA Recap (5 pages) VBA Reference Creating Procedures ; Selecting and Activating Excel Objects ; Selecting an Excel Range Relatively ; Messages and Inputs ; Declaring Variables ; Conditional Statements ; Looping ; Exiting from a Loop
Object Oriented Programming (12 pages) Object Oriented Programming The Building Blocks of an Object Oriented Language
Objects Referring to Objects by Name ; Referring to Objects by Index Number ; Qualifying References to Objects ; Using Keywords to Reference Objects ; Using Object Codenames ; Using Object Variables
Collections Referring to Collections ; Adding Items to a Collection ; Counting Items in a Collection
Methods Applying Methods to Objects ; Passing Arguments to Methods ; Returning Values and References from Methods ; When to use Parentheses
Properties Writing to a Property ; Read-Only Properties ; Property Data Types ; Reading from a Property ; Properties and Parameters
Getting Help in VBA The Object Browser ; Context Sensitive Help ; Recording a Macro
For Each Loops (12 pages)

Download this chapter 		Looping Through Collections The For Each Loop ; A Basic Example
Looping Over Excel Worksheets, Charts and Sheets Protecting all Worksheets ; Excluding Objects from a Loop ; Looping Through Chart Sheets ; Looping Through All Sheets ; Looping Through Objects on a Sheet
Looping Over the Excel Workbooks Collection Processing all Open Workbooks
Looping Over a Collection of Excel Range Objects Specifying the Range to Loop Over ; Looping Through a Column of Data
Nesting For Each Loops Looping Over Shapes on All Worksheets ; Looping Through Sheets in All Open Workbooks
Looping Over Collections in Word Looping Over Documents ; Looping Over Paragraphs ; Looping Over Sentences, Words and Characters
Looping Over Collections in PowerPoint Looping Over Presentations ; Looping Over Slides ; Looping Over Objects on a Slide
Looping Over Collections in Access Looping Over Controls on a Form ; Looping Over Access Database Objects
Looping Over Collections in Outlook Looping Over Inbox Items ; Looping Over Email Attachments
Controlling Other Applications (17 pages) Referencing Object Libraries Setting a Reference to an Object Library ; The Default References ; References and the Object Browser ; Microsoft Office Version Numbers
An Example for Word Setting a Reference to the Word Object Library ; Declaring a Variable for Word ; Creating a New Instance of Word ; Auto-Instancing Variables ; Showing and Activating Word ; Creating a New Document ; Writing and Formatting Text in Word ; Copying from Excel to Word ; Saving the Document and Closing Word ; The Complete Example
An Example for PowerPoint Setting a Reference to the PowerPoint Object Library ; Opening PowerPoint and Creating a Presentation ; Creating a Title Slide ; Copying from Excel to PowerPoint ; Moving and Resizing PowerPoint Objects ; Saving the Presentation and Closing PowerPoint ; The Complete Example
An Example for Outlook Setting a Reference to the Outlook Object Library ; The Complete Example
Controlling Applications without References The CreateObject Function ; Using Object Variables ; Converting Constants to Numbers ; Getting a Reference to a Running Application ; Testing the Version of an Application
Referencing Other VBA Projects Setting a Reference to a VBA Project ; Creating Excel Add-Ins ; Loading Excel Add-Ins
Connecting to Databases (14 pages) ActiveX Data Objects A Brief Version History ; Referencing the ADO Library
Connecting to an External Database Setting the Connection String
Creating Connections in Access Referencing the CurrentProject’s Connection
ADO Recordsets Creating a Recordset ; Setting the Source of the Recordset ; Setting the Lock Type ; Setting the Cursor Type ; Opening and Closing a Recordset ; Copying Data into Excel
Moving in a Recordset Moving the Cursor ; Reaching the End of a Recordset ; Looping Over a Recordset ; Referring to Fields
Finding and Filtering Records The Find Method ; Repeated Finds ; Applying a Filter ; Removing a Filter ; Adding Criteria to a SQL Select Statement ; Creating Dynamic SQL Statements
Modifying Data Adding New Records ; Editing Existing Records ; Deleting Records
ADO Commands Creating a New Command Object ; Setting the Command Text ; Executing the Command
Using DAO Referencing the Correct Object Library ; Opening a Database ; Creating a Recordset
Files and Folders (8 pages) Working with Files and Folders The Scripting Runtime Library ; Creating a FileSystemObject
Basic File and Folder Techniques Testing if a File or Folder Exists ; Creating a Folder ; Copying and Moving Files and Folders ; Deleting Files and Folders ; Renaming Files and Folders ; Getting a Reference to a File or Folder
Looping Over Files and Folders Looping Over Files ; Looping Over Folders ; Recursively Looping Over Subfolders
Working with Text Files Creating and Writing to a Text File ; Opening a Text File ; Reading from a Text File
Using VBA’s FileSystem Methods Creating Folders ; Deleting Files and Folders ; Copying Files ; Renaming Files
File Dialog Boxes (6 pages) Working with File Dialogs Types of File Dialog Box ; Displaying a File Dialog Box ; Performing the Default Action
Customising File Dialogs Changing the Title and Button Name ; Setting the Initial Location ; Allowing Multiple Selections ; Creating File Filters
Picking Files and Folders Returning a File or Folder Path ; Testing Which Button was Clicked ; Dealing with Multiple Selections ; Using Multiple File Dialogs
Class Modules (12 pages) What are Class Modules? Why Create Classes? ; Important Terminology ; Debugging in Class Modules
Designing a Class Our Example Film Class
Creating a Class Inserting a Class Module ; Renaming a Class Module ; Creating a New Instance of a Class
Creating Basic Properties Basic Properties ; Disadvantages of Basic Properties
Creating Full Properties Assigning a Value to a Property ; Reading a Value from a Property ; Assigning an Object to a Property ; Writing Additional Code in Properties ; Read-Only Properties
Creating Methods Writing Methods in a Class Module ; Using Class Methods
Class Module Events Creating Class Module Event Handlers ; Triggering Class Events
Sharing Class Modules Step 1 – Rename the VBA Project ; Step 2 – Make the Class Public ; Step 3 – Create a Function to Return an Instance of the Class ; Step 4 – Reference the Class Project ; Step 5 – Consume the Class
Collections and Dictionaries (12 pages) What are Collections? Custom Collections and Dictionaries
Untyped Collections Creating a New Collection ; Adding Items to a Collection ; Adding Custom Classes to a Collection ; Referencing Collection Items ; Removing Items from a Collection ; Looping Over Collections
Typed Collections The Problem with Untyped Collections ; Creating a Collection Class ; Populating a Typed Collection ; Looping Over a Typed Collection ; Referencing Items in a Typed Collection
Dictionaries Referencing the Scripting Runtime Library ; Creating a New Dictionary ; Adding Items to a Dictionary ; Referring to Dictionary Items ; Automatically Creating Keys ; Checking if a Key Exists ; The Compare Mode ; Removing Items from a Dictionary ; Replacing Dictionary Values ; Replacing Dictionary Objects ; Looping Over Dictionaries
Arrays (7 pages) Overview of Arrays Viewing the Contents of Arrays
Declaring Arrays Setting the Dimensions of an Array ; Changing the Base of Arrays ; Declaring Multi-Dimensional Arrays
Populating Arrays Assigning Values to an Array ; Assigning Objects to Arrays
Reading from Arrays Referring to a Specific Element ; Looping Over an Array ; The Bounds of an Array ; Using For Each Loops
Dynamic Arrays Declaring an Empty Array ; Re-Dimensioning an Array ; Preserving the Contents of an Array
Arrays in Excel Assigning a Range to an Array ; Calculating in an Array ; Assigning an Array to a Range
Modular Code, Parameters and Functions (11 pages) Modular Code Our Example
Breaking a Procedure into Parts Creating Module Level Variables ; Getting Input from the User ; Retrieving the Related Values ; Building and Showing a Message ; Putting it all Together
Procedures and Parameters Our Example ; Defining Parameters ; Calling a Procedure which has Parameters ; Optional Parameters ; Assigning Default Values to Parameters ; Testing for Missing Arguments ; ParamArrays
Passing Arguments ByRef and ByVal Passing Arguments by Reference ; Passing Arguments by Value ; Passing Arguments in Parentheses
Functions vs. Subroutines Returning a Value from a Function ; Returning a Reference from a Function ; Calling a Function ; Using Functions in a Worksheet ; Defining Function Parameters
Debugging Modular Code Viewing the Definition of a Procedure ; Stepping Over a Procedure Call
Constants and Enumerations (4 pages) Working with Constants Declaring Constants ; Referencing Constants
Enumerations Declaring Enumerations ; Referencing Enumerations ; Using Enumerations as Data Types ; Converting an Enumeration to Text ; Enumerations for Colours
Recursive Programming (3 pages) Recursive Procedures Why Use Recursive Procedures? ; Viewing the Call Stack
Organisational Hierarchies Creating the Base Organisation Chart ; Recursively Adding Nodes
Debugging (10 pages) Debugging Code Errors vs. Bugs ; The Debug Toolbar
Running Code Running a Procedure from Start to End ; Running a Procedure in Break Mode ; Stepping Through Code ; Changing the Next Instruction ; Editing Code in Break Mode
Breakpoints Setting and Removing Breakpoints ; The Stop Statement ; Breaking Conditionally
The Immediate Window Executing Instructions in the Immediate Window ; Asking Questions in the Immediate Window ; Printing to the Immediate Window
The Locals Window Observing Variables
The Watch Window Adding an Expression to Watch ; Types of Watch ; Adding a Quick Watch ; Editing and Removing Watches
The Call Stack Displaying the Call Stack ; Using the Call Stack
Useful Keyboard Shortcuts
Handling Errors (12 pages)

Download this chapter 		Run-Time Errors in VBA
Error Handling in VBA Identifying Potential Run-Time Errors ; The On Error Statement
Using the On Error Statement Ignoring Run-Time Errors ; Disabling an Error Handler
Creating a Custom Error Handler Redirecting Your Code ; Writing the Error-Handling Section ; Exiting a Procedure before the Error-Handling Code ; The Complete Example ; Creating Multiple Error Handlers
Resuming After an Error Resuming at the Original Line ; Resuming at the Next Line ; Resuming at a Specified Line ; Why use Resume and Not GoTo?
The Err Object Getting the Error Number and Description ; A Catch-All Approach to Error-Handling
Raising Custom Errors Raising a Custom Error
Errors in Multiple Procedures Creating a Top-Level Error Handler
Shapes (18 pages) Introduction to Shapes The Shapes Collection
Referring to Shapes Names and Index Numbers ; Referring to a Range of Shapes ; Referring to Selected Shapes ; Referring to Newly Added Shapes ; Looping Over the Shapes Collection
Shape Size and Position Changing the Size and Position ; Sizing and Positioning Relative to Other Objects
Adding Shapes Adding a Basic AutoShape ; Labels and Textboxes ; WordArt ; Pictures ; Form Controls
Formatting Shapes Changing Shape Colours ; Colour Gradients ; Other Formatting Options ; Setting Default Shape Formats ; Copying Formats between Shapes ; Using Shape Styles
Shape Adjustments Referring to Adjustments ; Adjusting Adjustments
Adding Text to AutoShapes The TextFrame and TextFrame2 Objects ; Adding Text to a Shape
Formatting Text in a Shape Basic Font Formatting ; Changing the Colour of Text ; Formatting Part of the Text ; Aligning Text in a Shape ; Changing Text Orientation
Connectors and Lines Drawing Straight Lines ; Adding Multi-Point Lines and Curves ; Drawing Freeform Lines ; Creating Enclosed Shapes ; Connectors

Notes:

  1. Since Wise Owl print courseware double-sided to save trees, the number of physical pages in each chapter is roughly half of the number of sides quoted.
  2. Downloads are provided as examples only, and should not be used or distributed in any way or form without the prior written permission of Wise Owl.
This page has 0 threads Add post