Wise Owl Courseware
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:
- 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.
- 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.