This page has 0 threads | Add post

Courseware

Advanced VBA course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Microsoft Excel courses).

Click here to download a sample of the first two chapters of this Advanced VBA manual, or review how to license our courseware.

Chapter 1 - VBA RECAP▼

Section	Title	Subsections	Page
1.1	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	8

Chapter 2 - OBJECT ORIENTED PROGRAMMING▼

Section	Title	Subsections	Page
2.1	Object Oriented Programming	The Building Blocks of an Object Oriented Language	13
2.2	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	14
2.3	Collections	Referring to Collections Adding Items to a Collection Counting Items in a Collection	17
2.4	Methods	Applying Methods to Objects Passing Arguments to Methods Returning Values and References from Methods When to use Parentheses	19
2.5	Properties	Writing to a Property Read-Only Properties Property Data Types Reading from a Property Properties and Parameters	21
2.6	Getting Help in VBA	The Object Browser Context Sensitive Help Recording a Macro	23

Chapter 3 - FOR EACH LOOPS▼

Section	Title	Subsections	Page
3.1	Looping Through Collections	The For Each Loop A Basic Example	25
3.2	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	26
3.3	Looping Over the Excel Workbooks Collection	Processing all Open Workbooks	28
3.4	Looping Over a Collection of Excel Range Objects	Specifying the Range to Loop Over Looping Through a Column of Data	29
3.5	Nesting For Each Loops	Looping Over Shapes on All Worksheets Looping Through Sheets in All Open Workbooks	30
3.6	Looping Over Collections in Word	Looping Over Documents Looping Over Paragraphs Looping Over Sentences, Words and Characters	31
3.7	Looping Over Collections in PowerPoint	Looping Over Presentations Looping Over Slides Looping Over Objects on a Slide	33
3.8	Looping Over Collections in Access	Looping Over Controls on a Form Looping Over Access Database Objects	34
3.9	Looping Over Collections in Outlook	Looping Over Inbox Items Looping Over Email Attachments	36

Chapter 4 - CONTROLLING OTHER APPLICATIONS▼

Section	Title	Subsections	Page
4.1	Referencing Object Libraries	Setting a Reference to an Object Library The Default References References and the Object Browser Microsoft Office Version Numbers	37
4.2	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	39
4.3	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	44
4.4	An Example for Outlook	Setting a Reference to the Outlook Object Library The Complete Example	47
4.5	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	48
4.6	Referencing Other VBA Projects	Setting a Reference to a VBA Project Creating Excel Add-Ins Loading Excel Add-Ins	52

Chapter 5 - FILES AND FOLDERS▼

Section	Title	Subsections	Page
5.1	Working with Files and Folders	The Scripting Runtime Library Creating a FileSystemObject	54
5.2	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	55
5.3	Looping Over Files and Folders	Looping Over Files Looping Over Folders Recursively Looping Over Subfolders	57
5.4	Working with Text Files	Creating and Writing to a Text File Opening a Text File Reading from a Text File	59
5.5	Using VBA’s FileSystem Methods	Creating Folders Deleting Files and Folders Copying Files Renaming Files	61

Chapter 6 - FILE DIALOG BOXES▼

Section	Title	Subsections	Page
6.1	Working with File Dialogs	Types of File Dialog Box Displaying a File Dialog Box Performing the Default Action	62
6.2	Customising File Dialogs	Changing the Title and Button Name Setting the Initial Location Allowing Multiple Selections Creating File Filters	64
6.3	Picking Files and Folders	Returning a File or Folder Path Testing Which Button was Clicked Dealing with Multiple Selections Using Multiple File Dialogs	66

Chapter 7 - CLASS MODULES▼

Section	Title	Subsections	Page
7.1	What are Class Modules?	Why Create Classes? Important Terminology Debugging in Class Modules	68
7.2	Designing a Class	Our Example Film Class	70
7.3	Creating a Class	Inserting a Class Module Renaming a Class Module Creating a New Instance of a Class	71
7.4	Creating Basic Properties	Basic Properties Disadvantages of Basic Properties	72
7.5	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	73
7.6	Creating Methods	Writing Methods in a Class Module Using Class Methods	76
7.7	Class Module Events	Creating Class Module Event Handlers Triggering Class Events	77
7.8	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	78

Chapter 8 - COLLECTIONS AND DICTIONARIES▼

Section	Title	Subsections	Page
8.1	What are Collections?	Custom Collections and Dictionaries	80
8.2	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	81
8.3	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	84
8.4	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	86

Chapter 9 - CONNECTING TO DATABASES▼

Section	Title	Subsections	Page
9.1	ActiveX Data Objects	A Brief Version History Referencing the ADO Library	92
9.2	Connecting to an External Database	Setting the Connection String	93
9.3	Creating Connections in Access	Referencing the CurrentProject’s Connection	94
9.4	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	95
9.5	Moving in a Recordset	Moving the Cursor Reaching the End of a Recordset Looping Over a Recordset Referring to Fields	98
9.6	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	100
9.7	Modifying Data	Adding New Records Editing Existing Records Deleting Records	103
9.8	ADO Commands	Creating a New Command Object Setting the Command Text Executing the Command	104
9.9	Using DAO	Referencing the Correct Object Library Opening a Database Creating a Recordset	105

Chapter 10 - ARRAYS▼

Section	Title	Subsections	Page
10.1	Overview of Arrays	Viewing the Contents of Arrays	106
10.2	Declaring Arrays	Setting the Dimensions of an Array Changing the Base of Arrays Declaring Multi-Dimensional Arrays	107
10.3	Populating Arrays	Assigning Values to an Array Assigning Objects to Arrays	108
10.4	Reading from Arrays	Referring to a Specific Element Looping Over an Array The Bounds of an Array Using For Each Loops	109
10.5	Dynamic Arrays	Declaring an Empty Array Re-Dimensioning an Array Preserving the Contents of an Array	111
10.6	Arrays in Excel	Assigning a Range to an Array Calculating in an Array Assigning an Array to a Range	112

Chapter 11 - MODULAR CODE, PARAMETERS AND FUNCTIONS▼

Section	Title	Subsections	Page
11.1	Modular Code	Our Example	113
11.2	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	114
11.3	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	116
11.4	Passing Arguments ByRef and ByVal	Passing Arguments by Reference Passing Arguments by Value Passing Arguments in Parentheses	119
11.5	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	121
11.6	Debugging Modular Code	Viewing the Definition of a Procedure Stepping Over a Procedure Call	123

Chapter 12 - CONSTANTS AND ENUMERATIONS▼

Section	Title	Subsections	Page
12.1	Working with Constants	Declaring Constants Referencing Constants	124
12.2	Enumerations	Declaring Enumerations Referencing Enumerations Using Enumerations as Data Types Converting an Enumeration to Text Enumerations for Colours	125

Chapter 13 - RECURSIVE PROGRAMMING▼

Section	Title	Subsections	Page
13.1	Recursive Procedures	Why Use Recursive Procedures? Viewing the Call Stack	128
13.2	Organisational Hierarchies	Creating the Base Organisation Chart Recursively Adding Nodes	129

Chapter 14 - DEBUGGING▼

Section	Title	Subsections	Page
14.1	Debugging Code	Errors vs. Bugs The Debug Toolbar	131
14.2	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	132
14.3	Breakpoints	Setting and Removing Breakpoints The Stop Statement Breaking Conditionally	134
14.4	The Immediate Window	Executing Instructions in the Immediate Window Asking Questions in the Immediate Window Printing to the Immediate Window	135
14.5	The Locals Window	Observing Variables	136
14.6	The Watch Window	Adding an Expression to Watch Types of Watch Adding a Quick Watch Editing and Removing Watches	137
14.7	The Call Stack	Displaying the Call Stack Using the Call Stack	139
14.8	Useful Keyboard Shortcuts		140

Chapter 15 - HANDLING ERRORS▼

Section	Title	Subsections	Page
15.1	Run-Time Errors in VBA		141
15.2	Error Handling in VBA	Identifying Potential Run-Time Errors The On Error Statement	142
15.3	Using the On Error Statement	Ignoring Run-Time Errors Disabling an Error Handler	143
15.4	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	144
15.5	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?	147
15.6	The Err Object	Getting the Error Number and Description A Catch-All Approach to Error-Handling	149
15.7	Raising Custom Errors	Raising a Custom Error	150
15.8	Errors in Multiple Procedures	Creating a Top-Level Error Handler	152

Chapter 16 - SHAPES▼

Section	Title	Subsections	Page
16.1	Introduction to Shapes	The Shapes Collection	153
16.2	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	154
16.3	Shape Size and Position	Changing the Size and Position Sizing and Positioning Relative to Other Objects	156
16.4	Adding Shapes	Adding a Basic AutoShape Labels and Textboxes WordArt Pictures Form Controls	157
16.5	Formatting Shapes	Changing Shape Colours Colour Gradients Other Formatting Options Setting Default Shape Formats Copying Formats between Shapes Using Shape Styles	160
16.6	Shape Adjustments	Referring to Adjustments Adjusting Adjustments	164
16.7	Adding Text to AutoShapes	The TextFrame and TextFrame2 Objects Adding Text to a Shape	165
16.8	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	166
16.9	Connectors and Lines	Drawing Straight Lines Adding Multi-Point Lines and Curves Drawing Freeform Lines Creating Enclosed Shapes Connectors	168

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

Excel course manuals