Microsoft Excel courseware and training manuals

We train on all manner of Excel courses (everything from pure Excel through PowerPivot to advanced VBA macro programming):

You can see details of how to license our Microsoft Excel courseware (including volume requirements) here.

Excel courseware

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

Chapter Sections Subsections
Formulae and Functions (10 pages) Basic Formulae Operators ; Brackets in Formulae
Creating Formulae
Editing Formulae
Copying Formulae The Easiest Way to Copy a Formula ; Other Ways to Copy a Formula ; How Relative Cell Referencing Works
Functions Basic Functions ; Structure of a Function ; Typing a Function ; Using AutoSum to Create Quick Totals, Averages, Etc. ; The Quickest Way to Sum ; The Function Wizard
Status Bar Calculations
The Office Interface (5 pages) Interacting with Office The File Tab ; The Ribbon ; The Mini Toolbar ; The Quick Access Toolbar
Using the Ribbon Tabs, Groups and Commands ; Using Quick Keys ; Seeing More Options
Excel Basics (4 pages) What is Excel Used for?
Opening and closing Excel Opening/Starting Excel ; Closing/Ending Excel ; The Screen Layout
Structure of a Workbook Workbooks and Worksheets ; Cells, Rows and Columns ; Cell References
Entering and Editing Data (13 pages) Entering Data Typing in Data ; Types of Data ; Disguising Numbers ; Entering Dates ; Using AutoComplete to Enter Data ; Turning AutoComplete off ; Typing the Same Data into Multiple Cells
Editing and Removing Data Editing Data in Cells ; Clearing Cell Contents ; Clearing Formats from Cells
Inserting and Deleting Cells, Rows and Columns Inserting Rows and Columns ; Deleting Rows and Columns ; Inserting Cells ; Deleting Cells
Checking Spelling
AutoCorrect Turning AutoCorrect Off ; Changing the AutoCorrect Settings
Copying and Moving Cells (7 pages) Methods of Copying and Moving Cut, Copy, and Paste ; Drag and Drop ; Replacing and Inserting Cells with Drag and Drop ; Special Options for Pasting ; The Paste Tool ; Paste Special ; Paste Special using the Right-Click Menu ; The Format Painter
AutoFill Copying Using AutoFill ; AutoFill Paste Options ; The Clipboard ; Using the Clipboard ; Copying and Pasting Using the Clipboard ; Clearing the Clipboard
Filling Cells (4 pages) Creating Series Creating a Series from its First Two Values ; Filling a Series with the Right Mouse Button
Custom Lists
Flash Fill Flash Fill when Copying Down ; Flash Fill Tool using the Ribbon ; Other Examples of Flash Fill
Moving and Selecting in Excel (6 pages) Moving Around in Excel Using the Mouse to Move Around ; Keyboard Shortcuts for Moving Around ; Zooming the View
Selecting Cells Selecting Single Cells ; Selecting a Range of Cells ; Selecting a Large Range of Cells ; Selecting Multiple Ranges ; Selecting Entire Rows and Columns ; Selecting an Entire Worksheet ; De-Selecting Cells ; Using the Keyboard to Select Cells
Working with Files (10 pages) Introduction New File Formats ; Rules for Naming Files ; Saving a New Excel Workbook ; The Workbook’s File Path ; Re-saving an Existing Workbook
Closing Workbooks Closing a Workbook Using Excel ; Closing a Workbook Using the Task Bar
Creating New Workbooks
Opening an Existing Workbook Opening a Recently Used Workbook ; Opening a File not Used Recently ; Searching for Files ; Creating New Folders when Saving
Excel File Types Choosing a File Type ; Which File Type to Use
Files with Macros Macro-Enabled File Types ; Macro Security Settings
Styles (5 pages) Overview of Styles Benefits of Styles (1) – Consistent Formatting ; Benefits of Styles (2) – Protection
Applying Styles Adding Styles to the Quick Access Toolbar
Creating Styles Creating Partial Styles
Changing Styles
Merging Styles
Number and Date Formats (12 pages) What are Number Formats? Examples of Number Formats ; Examples of Date Formats
Formatting Dates How Dates and Times are Stored ; Quick Ways to Enter Dates and Times ; Quick Formatting Dates and Times ; Selecting Different Date/Time Formats ; Custom Date Formats ; Custom Date Formatting Codes ; Custom Time Formatting Codes ; Displaying Elapsed Times without Overflow
Standard Number Formats Standard Number Formats using the Ribbon ; Standard Number Formats using Format Cells
Custom Number Formats Creating Custom Number Formats ; General Rules for Custom Number Formats ; Example of a Typical Custom Number Format ; Re-using Custom Number Formats
Tricks with Number Formats Tricks with Text ; Hiding Parts of a Number Format ; Colouring Parts of a Number Format ; Getting Positive and Negative Numbers to Line Up ; Getting Decimal Points to Line Up ; Fractions ; Scaling Large Numbers ; Filling Spaces
Conditional Formatting (7 pages)

Download this chapter 		Creating Conditional Formats Applying a Pre-set Conditional Format ; Creating your own Conditional Format – a Case Study
Editing and Deleting Conditional Formats Changing the Order in which Rules Apply
More Sophisticated Rules Picking out Statistical Trends ; Using Formulae in Rules
Viewing Conditionally Formatted Cells/Rules Viewing All Conditionally Formatted Cells ; Viewing all Conditional Formatting Rules
Absolute and Relative References (3 pages) Relative Referencing
Absolute References When Relative Referencing doesn’t Work ; The Solution – Use Absolute References ; Fixing Just the Row or Column
Range Names (10 pages) What are Range Names? Going to a Range Name
Creating Range Names Creating Range Names using the Name Box ; Defining Range Names
Creating Formulae using Range Names Conditions under which Range Names Work ; Creating Formulae using Range Names ; Range Formulae using Columns ; Range Formulae using Single Cells
Changing Range Names Editing and Deleting Range Names ; Avoiding the Need to Change Range Names
Creating Range Names from Selected Cells
Scope of Range Names Choosing the Scope of a Range Name ; Duplicating Range Names by Copying Worksheets
Managing Range Names Listing out Range Names ; Applying Range Names
Quirks of Range Names Getting Aggregation Functions to Work ; Viewing Range Names ; 3-Dimensional Ranges
Cell Comments (5 pages) Overview of Comments The Comments Section of the Ribbon
Working with Comments Creating Comments ; Changing the Default User Name ; Editing and Deleting Comments ; Formatting Comments ; Printing Comments
Ways to Show Comments Hiding Red Triangles ; Showing/Hiding All Comments ; Selecting Cells Containing Comments
Printing (12 pages) Views of a Worksheet Page Layout View ; Page Break Preview ; Print Preview
Printing Choosing to Print ; Print Settings
Printing Areas of a Worksheet Setting a Print Area ; Setting Multiple Print Areas
Page Breaks Viewing Page Breaks ; Moving Page Breaks ; Inserting Page Breaks ; Removing Page Breaks
Fitting Worksheets to Pages Page Orientation ; Page Margins in Excel ; Changing Margins Using the Ribbon ; Changing Margins in Print Preview ; Scaling a Page ; Using Page Breaks to Scale a Page ; The Page Setup Dialog Box
Headers and Footers Adding a Header and Footer ; Inserting Headers and Footers with the Ribbon
Printing Titles Setting Print Titles
Formatting worksheets (12 pages) Methods of Formatting Using the Ribbon ; Using the Mini-Toolbar ; Using the Format Cells Dialog Box
Formatting Text Fonts and Sizes ; Simple Font Formatting
Cell Alignment Horizontal Alignment ; Vertical Alignment ; Other Alignments ; Rotating Text ; Indenting
Making Text Fit into Cells Column Widths and Row Heights ; Shrinking Text ; Wrapping Text ; Merging Cells
Formatting Numbers Choosing a Number Format ; More Number Formatting Options
Cell Backgrounds Fill Colours ; Fill Effects
Cell Borders Applying Borders ; Drawing Borders ; Border Styles and Colours ; Borders Using the Dialog Box
A Note about Gridlines
Multiple Worksheets (8 pages) Moving between Worksheets
Common Tasks Selecting and De-selecting Worksheets ; Renaming Worksheets ; Changing Worksheet Tab Colours ; Hiding and Unhiding Worksheets
Inserting and Deleting Worksheets Inserting Single Worksheets ; Inserting Multiple Worksheets ; Deleting Worksheets
Moving and Copying Worksheets Moving Worksheets within a Workbook ; Copying Worksheets within a Workbook ; Moving and Copying to a Different or New Workbook
Group Mode
Summing across Sheets
Consolidating Data (6 pages) Consolidation – Overview
Consolidation using Formulae
Consolidation by Position
Consolidation by Label Matching by Left Column ; Matching by Top Row ; Matching by Top Row and Left Column ; Consolidating the Data using your Label Choice
Linking to Source Data
Data Validation (8 pages) What is Data Validation?
Creating Validation Rules Step 1 – Saying what is Allowed ; Step 2 – Defining your Punishment for Mistakes
Referencing Formulae in Validation
Dropdown Lists Step 1 - Creating a Range Name ; Step 2 – Setting the List
Input Messages
Copying and Clearing Validation Settings Copying Validation ; Clearing Validation Settings
Highlighting Validated Cells Highlighting Cells which have Validation Applied ; Circling Invalid Data
Protection (8 pages) Types of Protection
Protecting Worksheets Unlocking Cells ; A Note on Unprotecting Cells using Styles ; Protecting and Unprotecting Worksheets
Protecting a Workbook’s Structure
Password-Protecting Workbooks
Preventing Accidental Changes to a Workbook
Hiding Worksheets Very Hidden Worksheets
Hiding Cells and Formulae Hiding Cells ; Hiding Formulae
Templates (6 pages) Why Use Templates?
Avoiding Templates
Preparing to Use Templates
Creating a Template
Setting the Default Template Step 1 - Finding your XLSTART Folder ; Step 2 - Moving or Copying your Template ; Step 3 – Calling your Template BOOK.XLTX ; Step 4 – Stop the Start-up Screen Appearing
Tables (14 pages) Definition of a Table
Creating and Removing Tables Creating Tables from Existing Data ; Creating Tables from Scratch ; Removing Tables
Sorting Tables Simple Sorting – 3 Methods ; Sorting by Two or More Columns ; Sorting by Colour
Simple Filtering Step 1 – Enabling Filtering ; Step 2 – Filtering by Columns
Removing Filters Clearing a Filter from a Single Column ; Clearing All Filters ; Removing Filtering
Filtering Specific Types of Data Filtering Numbers ; Filtering Text ; Filtering Dates
Removing Duplicate Rows
Advanced Filtering (Using OR and AND) Step 1 – Creating a Criteria Range ; Step 2 – Creating an Extract Range ; Step 3 – Applying Advanced Filtering
Formatting Tables (6 pages) Formatting Tables Changing the Scope of Formatting ; Applying a Format ; When Formatting appears not to Work
Creating Table Styles Step 1 - Creating the Table Style ; Step 2 – Formatting Parts of your Style ; Step 3 – Setting the Stripe Size
Subtotals (5 pages) What Subtotals Do
Creating Subtotals Step 1 – Sorting the Data Correctly ; Step 2 – Checking you can Create Subtotals ; Step 3 – Creating Subtotals ; Step 4 – Grouping and Outlining ; Step 5 – Copying the Visible Cells Only
Removing Subtotals
Pivot Tables (16 pages) Overview
Creating Pivot Tables Step 1 – Ensure you have a Table ; Step 2 – Create your Pivot Table ; Step 3 – Configure your Fields
Deleting Pivot Tables
Drilling Down
Working with Pivot Tables Renaming Fields ; Changing how Values are Calculated ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Refreshing Pivot Tables Getting Pivot Tables to Refresh when you Open Workbooks
Working with Dates Grouping by Date
Filtering and Slicers Filtering a Pivot Table ; Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
IF Functions (7 pages) Overview of IF Functions Relational Operators
Creating IF Functions Typing IF Functions ; Using the Function Wizard
Nesting IF Conditions Step 1 – Define the Conditions ; Step 2 – Create the Nested IF Function ; An Alternative: the IFS function
The SWITCH Function
Combining IF with Other Logical Functions
The VLOOKUP Function (7 pages) Types of Lookup Table Exact Matches or Not? ; Horizontal or Vertical?
Exact Match Lookup Tables Syntax of the Exact VLOOKUP Function ; Creating a Range Name for your Lookup Table ; Creating the Lookup Formula
Inexact Matches (Continuous Value Lookups) Creating the Lookup Table ; Giving the Lookup Table a Range Name ; Creating the VLOOKUP Formula
Trapping Errors Method 1 - Preventing Errors Happening with Data Validation ; Method 2 - Adding Outliers to your Input Table ; Method 3 – Converting Invalid Values using IF ; Method 4 – Trap Errors when they Occur
Other Lookup Functions (8 pages) Combining MATCH and INDEX Reasons to Prefer MATCH/INDEX to VLOOKUP ; The Range Names for our Example ; The Syntax of MATCH ; Using MATCH to Find Row/Column Numbers for our Example ; Syntax of the INDEX Function ; Creating the INDEX Function for our Example ; Combining MATCH and INDEX in a Single Function
The CHOOSE Function
The OFFSET Function Basic Offsetting ; Setting the Number of Rows/Columns Returned
The INDIRECT Function Example: Picking Out Figures from a Chosen Worksheet
Date and Time Functions (3 pages)

Download this chapter 		Returning Date Information Returning Parts of a Date ; Returning today’s Date/Time ; Returning Parts of a Time ; Converting Numbers/Text into Dates/Times
Manipulating Dates Returning Different Dates ; Returning the Difference between Dates
Text Functions (4 pages) List of Text Functions Searching and Replacing Text ; Extracting one String from Another ; Converting Strings ; Other Text Functions ; Joining Multiple Items ; Converting Numbers and Dates to Text
Text Functions – a Case Study
Information Functions (2 pages) The CELL and INFO Functions The INFO Function ; The CELL Function
Sheet Number / Numbers The SHEET Function ; The SHEETS Function
Inspection and Error Functions (2 pages) Inspection Functions Non-Error Inspection Functions ; Error Inspection Functions
Handling Errors General Error Trapping using IFERROR ; Avoiding the Error in the First Place
Conditional Statistics (4 pages) Overview of Conditional Statistical Functions Using IF instead of Conditional Statistical Functions ; The Special Functions Available
SUMIFS, COUNTIFS, etc Syntax of the Functions ; Worked Example ; Example with Multiple Criteria and Wildcards ; AVERAGEIF, COUNTIF and SUMIF ; Using with a Summation Range ; Combining the Criteria and Summation Range
The SUMPRODUCT Function (4 pages) Our Example
Solutions without SUMPRODUCT Summing the Results of IF Functions ; Using SUMIFS
The SUMPRODUCT Function What Summing Products Means ; Vectors of TRUE / FALSE values ; Forcing SUMPRODUCT to Calculate True/False Correctly ; Weighted Averages using SUMPRODUCT
Dynamic Range Names (4 pages) Examples of Dynamic Range Names
Making a Range Name Dynamic The Principle: the OFFSET Function ; Creating the Range Name ; Using Dynamic Range Names
Dynamic Range Names and Charts Creating the Range Names ; Making the Chart Refer to these Ranges
Grouping (Outlining) (4 pages) Using Grouping
Creating Grouping/Outlining Creating Automatic Outlines ; Manually Grouping Rows/Columns
Problems with Grouping/Outlining Changing Outlining Position ; Using Grouping/Outlining with Protection
Removing Outlining/Grouping Removing Outlining/Grouping from Selected Rows/Columns ; Removing All Outlining
Recording Macros (12 pages) Introducing Macros
Displaying the Developer Ribbon Tab
Recording a Macro Step 1 - Preparing to Record ; Step 2 – Starting to Record ; Step 3 – Record your Macro ; Recording Locations and the Personal Macro Workbook
Working with Recorded Macros Running a Recorded Macro ; Starting to Edit a Macro ; Using the VBA Code Editor
Macro Workbooks (Saving and Opening) Saving a Workbook Containing Macros ; Opening a Workbook Containing Macros
Relative References An Example Needing Relative References ; Absolute Code ; Switching to Relative Recording
Ways to Run Macros Assigning Macros to Shapes or Pictures ; Customising the Quick Access Toolbar ; Attaching Macros to Buttons
Charts (18 pages) Overview
Selecting Data The Golden Rules for Selecting Chart Data ; Selecting a Simple Data Range ; Selecting Multiple Ranges ; Selecting Non-Contiguous Ranges
Creating Charts
Chart Location Embedded Charts ; Printing Embedded Charts ; Separate Sheet Charts ; Switching between Embedded and Separate Sheet Charts
Chart Types, Templates and Defaults Changing a Chart’s Type (Including Chart Templates) ; Creating Chart Templates ; Setting the Default Chart
Formatting Charts
Formatting Parts of a Chart The Parts of a Chart (Chart Elements) ; Selecting Part of a Chart ; Selecting a Data Point or Legend Entry ; Formatting Part of a Chart ; Using the Formatting Task Bar ; Worked Example: Setting the Gradient Fill for a Chart Title
Showing/Hiding Parts of a Chart
Specific Chart Element Consideration Working with Data Tables ; Data Labels ; Axes, Scaling, Tick Marks and Gridlines
Editing Data Changing the Data being Charted Visually ; Switching Rows and Columns ; Filtering Data and Labels
Sparklines (6 pages) Introduction to Sparklines What Sparklines Are ; Sparklines can Contain Data
Creating Sparklines
Grouping and Ungrouping Sparklines Ungrouping Sparklines ; Grouping Sparklines
Removing Sparklines
Editing Sparklines Changing the Sparkline Type ; Changing Line and Marker Colours ; Sparkline Markers ; Changing the Axis Settings
Advanced Charts (1) (11 pages) Overview
Combination Charts
Trendlines Some Types of Trendline ; Adding Trendlines to Charts ; Choosing the Trendline Type ; Displaying Regression Statistics ; Extrapolating Trendlines
Scatter and Bubble Diagrams Scatter Diagrams ; Displaying a Regression Line ; Bubble Charts ; Customising Bubble Charts
Stock Charts
Lines and Up/Down Bars Up/Down Bars ; Series Lines
Error Bars
Picture Charts Tiling Pictures
Advanced Charts (2) (8 pages) Sunburst Charts
Treemap Charts
Waterfall or Bridge Charts
Histograms
Box and Whiskers Charts Creating a Box and Whiskers Chart ; Interpreting the Box and Whiskers
Pareto Charts
Modelling Guidelines (6 pages) Guideline 1 – Use a Cover Sheet
Guideline 2 – Separate Inputs and Calculations Checking for Rogue Formulae
Guideline 3 – Use Styles
Guideline 4 – Use Range Names
Guideline 5 – Keep Formulae Simple
Guideline 6 – Copy Formulae Across
Guideline 7 – Use Consistent Period Headings
Masking (2 pages) Masking
Logical Functions
Cashflow (3 pages) Template for a Cashflow Calculation
Calculating Interest Step 1 – Splitting the Cash Stream into Positive and Negative Amounts ; Step 2 – Calculating the Deposit/Loan Rate ; Step 3 – Calculating the Deposit/Loan Amount
Investment Appraisal (5 pages) Discount Rates
Discounted Cashflow
NPV and IRR Functions NPV Function ; IRR Function ; Using IRR or NPV to Assess an Investment ; Non-Periodic Cashflows using XNPV and XIRR ; Modified Internal Rate of Return Function
Array formulae (5 pages) What Array Formulae Do Distinguishing Features of Array Formulae
Working with Arrays Creating an Array Formula ; Editing and Deleting Arrays ; Selecting Arrays
Matrices
Array Constants Creating and Using a Range Name to Store the Constants
Scenarios (6 pages) Overview of Scenarios
Creating a Scenario Base case scenarios ; Starting the Scenario Manager ; Creating a Scenario
Showing Different Scenarios Showing Different Scenarios using the Ribbon ; Switching Scenarios using the Quick Access Toolbar
Editing, Deleting and Merging Scenarios Editing or Deleting Scenarios ; Merging Scenarios
Summarising Scenarios
Data Tables (5 pages) Overview of Data Tables One-Way and Two-Way Data Tables ; Example of a Data Table
One-Way Data Tables Step 1 – Create your Formulae ; Step 2 – Create your Inputs ; Step 3 – Create the Data Table Itself
Two-Way Data Tables Step 1 – Create your Formula ; Step 2 – Create your Inputs ; Step 3 – Create the Data Table
Goal-Seeking (2 pages) Goal-Seeking Example
Using Goal-Seeking
Excel Short-Cut Keys (8 pages) CTRL and SHIFT Key Combinations SHIFT and CTRL keys ; General CTRL Keys in Excel ; CTRL Keys with Letters
Function Keys F1 to F6 Keys ; F7 to F12 Keys
Other Special Keys Special Keys – Part 1 ; Special Keys – Part 2 ; Special Keys – Part 3

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

Power BI for Excel courseware

Here is a list of all of our Power BI for Excel manual chapters (you can download the ones marked):

Chapter Sections Subsections
Introduction to PowerPivot (2 pages) The Course Database
The Power BI Components
Creating Data Models (12 pages) Going into PowerPivot
Importing Tables Step 1 – Connecting to the Database ; Step 2 – Choosing Data Tables ; Step 3 – Importing the Data ; Step 4 – Saving your Work (the xVelocity Engine)
Importing More Tables using an Existing Connection
Filtering Rows and Columns on Import Ignoring Columns ; Filtering Rows
Data and Diagram View Zooming In and Out in Diagram View
Hiding Columns and Tables from Client Views Hiding Columns ; Hiding Tables
Renaming Tables and Columns Renaming Columns ; Renaming Tables
Changing what you’ve Imported for a Table
Formatting Table Columns
Relationships (3 pages) Viewing Existing Relationships
Creating Relationships Creating Relationships in Diagram View ; Creating Relationships Manually
Pivot Tables using PowerPivot (16 pages) Creating the Data Model
Creating a Pivot Table Step 1 – Starting the Pivot Table ; Step 2 – Choosing Fields
Drill Down and Quick Explore Drill Down ; Deleting drill down results ; The Quick Explore Option
Working with Pivot Tables Renaming Fields ; Changing how Values are Calculated ; Changing Number Formatting ; Moving Rows and Columns ; Sorting Pivot Tables
Filtering
Slicers and Timelines Creating Slicers ; Removing Slicers (or their Filters) ; Changing the Number of Slicer Columns ; Changing the Style of a Slicer ; Inserting Timelines ; Controlling Multiple Pivot Tables with Slicers/Timelines
Formatting Pivot Tables
Choosing what to Display Hiding Field Captions ; Inserting Blank Rows ; Subtotals and Totals ; Changing Report Layout ; Repeating Item Labels
Using Excel Workbooks (4 pages)

Download this chapter 		Linking to Excel Tables Creating Tables ; Naming a Table ; Listing Tables ; Linking to Tables
Importing Excel Workbooks
Links to Other Data Sources (5 pages) Formats Supported
Linking to Access
Importing Cubes
Importing from the Clipboard Creating a New Table from the Clipboard ; Appending Existing Data from the Clipboard
Importing from a CSV File
Getting Data (8 pages) A Potted History of Get & Transform
What Get & Transform Does
Getting Data Step 1 - Creating the Query ; Step 2 – Load or Edit?
Managing Queries
Linking to a Website
Linking to an XML File
Other Data Sources
Transforming Data (12 pages) Editing a Query The Query Editor
Editing Query Steps The Hard Way – the M Language ; The Easy Way – Query Steps
Adding Simple Transforms Removing Columns ; Reordering Columns ; Filtering Rows ; Sorting Columns
Splitting Columns, etc. – a Case Study Step 1 – Splitting by Number of Characters ; Step 2 – Splitting by Delimiter ; Step 3 – Replacing Values ; Step 4 – Changing Data Types ; Step 5 - Merging Columns ; Step 6 – Inserting a Start Date Column ; Step 7 – Getting the Month Number ; Step 8 – Aggregating the Data ; Step 9 – Renaming Query Steps ; Step 10 – Running your Query
Managing your Queries Renaming Queries ; Grouping Queries
Recent Data Sources
Calculated Columns (7 pages) What are Calculated Columns? Differences between Calculated Columns and Excel Formulae
Creating Calculated Columns An Alternative Method, using the Keyboard
Working with Calculated Columns Moving Calculated Columns ; Sorting by Calculated Columns
The PowerPivot Function Wizard
Using RELATED to Link Tables
The BLANK Function Blank Arithmetic
Aggregator Columns and SWITCH Using SWITCH
Measures (10 pages) Query Context
Implicit Measures Viewing Implicit Measures
Using AutoSum Using an AutoSum Measure in a Pivot Table
Creating Measures in Excel Step 1 - Starting to Create the Formula ; Step 2 – Choosing the Table ; Step 3 – Completing the Dialog Box
Step 4 – Checking your Formula Step 5 – Using your Formula
Creating Measures in PowerPivot Step 1 – Choosing where to Start ; Step 2 – Creating the Formula ; Step 3 – Renaming the Measure ; Step 4 – Using the Measure
Measures vs. Columns – the Differences Difference 1 - Measures Aggregate ; Difference 2 - Use in Pivot Tables ; Difference 3 - Key Performance Indicators (KPIs)
Basic DAX Formulae (6 pages) Aggregation Functions
X-Suffix Functions Simple Aggregate Functions don’t Work with Expressions ; Aggregate Functions with X Suffix ; Our Example SUMX Function
Calculating Ratios Counting Rows ; Creating Ratios: Measures Needed ; The Final Pivot Table ; Summing Ratios doesn’t Work
DAX Variables
The CALCULATE Function (4 pages) Syntax of the CALCULATE Function
Replacing Filters Replacing a Single Filter ; Replacing More than One Filter
Removing Filters Removing a Single Filter ; Removing Multiple Filters
Using VALUES to Edit a Constraint On its own, VALUES is pointless ; Amending the Query Context Filter
The FILTER Function (3 pages) Syntax of the FILTER Function
Filtering to Show Subsets of Data
Removing Filters using ALL Removing and Partially Reinstating Filters
Ranking (3 pages) The RANKX Function Syntax of the Rank Function ; Entering RANKX in a Calculated Column
Using RANKX in Measures Step 1 – Creating an Intermediate Measure ; Step 2 – using ALL ; Changing the Sort Order ;
The EARLIER Function (2 pages) Case Study: Ranking using EARLIER
Case Study: Group Statistics
Calendars (13 pages) The Need for a Separate Date Table The Excel Approach – Date Functions ; The PowerPivot Approach – Create a Separate Table
Creating a Calendar Table Creating a Calendar in Excel ; Creating a Calendar Table in SQL Server ; Creating a Calendar Automatically
Using your Calendar Change 1 – Mark your Calendar as a Date Table ; Change 2 - Changing Column Data Types ; Change 3 - Creating Relationships
Creating Pivot Tables using Dates Sorting Months Correctly ; Sorting Months by Financial Year
Multiple Date Tables Solution 1 – Multiple Calendars ; Solution 2 – Multiple Relationships
Worked Example – Working Days Calculation Step 1 – Create a Bank Holiday Table ; Step 2 – Create a Relationship for the Bank Holiday Table ; Creating a Calculated Column for Working Days
Date Functions (9 pages) Formulae Covered The Underlying Principle
Period to Date The Year-to-Date Function Family ; Dates to Date Functions ; Fiscal Year Calculations
Comparing with Previous Periods The SAMEPERIODLASTYEAR Function ; Using DATEADD to Compare with any Previous Period
Parallel Periods Comparing with the Previous Year’s Year-to-Date
Moving Averages
Semi-Additive Measures Opening and Closing Balances – the Problem ; A Partial Solution using the LastDate/FirstDate Function ; Using LastNonBlank or FirstNonBlank
Hierarchies (3 pages) Using Hierarchies Advantages and Disadvantages of Hierarchies
Creating Hierarchies Step 1 – Getting the Correct Fields in a Single Table ; Step 2 – Creating the Hierarchy ; Step 3 – Including Fields in your Hierarchy ; Step 4 – Using a Hierarchy
KPIs (5 pages)

Download this chapter 		About KPIs The Three Parts of a KPI
Creating a KPI Step 1 – Create the Measures Needed ; Step 2 – Starting to Create the KPI ; Step 3 – Customising your KPI ; Step 4 – Displaying Parts of the KPI
Absolute Targets
Perspectives (2 pages) Overview of Perspectives What Perspectives Are ; Using Perspectives in PowerPivot
Creating and Editing Perspectives
Power View (4 pages) Enabling Power View
What is Power View?
Creating a Power View Visualisation
Why Not to Use Power View

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