Macros and Programming courseware and training manuals

We're kings (and queens) of programming, with courses - and course manuals - covering SQL, VBA, VB and C#:

You can see details of how to license our Macros and Programming courseware (including volume requirements) here.


Basic SQL courseware

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

Chapter Sections Subsections
Designing Databases (5 pages) The Four Stages of Database Design Stage 1 – Deciding what to Include ; Stage 2 – Dividing Data into Tables ; Stage 3 – Choosing a Primary Key for each Table ; Stage 4 – Creating Relationships and a Database Diagram
Many-to-Many Relationships
SQL Server Management Studio (2 pages) Starting to Use Management Studio
Object Explorer Useful Start-up Options
Creating a Database and Tables (11 pages) Creating the Database The Files Created
Creating Tables
Setting an Identity Primary Key
Creating Columns Data Types Explained in this Chapter ; Other Data Types in SQL Server
Whole Numbers Integer Field Types ; Logical Field Types
Other Numerical Fields Decimal and Numeric Field Types ; Float and Real Data Types
Character Data Types Types of Character Storage ; Variable Length Data Types ; Fixed Length Data Types
Date/Time Data Types Entering Dates into a Table
Default and Null Values Allowing Nulls ; Default Values
Database Diagrams Creating a Database Diagram ; Creating Relationships ; Database Diagram Support Objects Error
Queries (9 pages) Basic SELECT Statements Where to Put your Commas
Creating Queries Starting a New Query ; Choosing the Right Database
Running Queries Parsing a Query ; Executing a Query ; Viewing Information on a Query’s Execution ; Cancelling a Running Query ; Redirecting Query Output
Dealing with Errors Displaying Line Numbers
Using IntelliSense Refreshing IntelliSense
Multiple SQL Commands
Saving, Opening and Closing Queries Saving Queries ; Opening Queries ; Closing Queries
Laying Out Queries (5 pages)

Download this chapter
Using Case
Indentation and Word Wrap Changing Tab Settings ; Word Wrap
Comments Commenting Out Blocks of Code
Colours in SQL Changing the Default Colours
Auto-formatting SQL
The SELECT Statement (10 pages) SELECT Statement Syntax Mnemonic for Order of Commands
Qualified Tables and Columns Dragging Tables/Columns onto a Query ; Specifying the DBO Schema and Database
Table Aliases Reason 1 for Aliases – Easier to Refer to Field Names ; Reason 2 for Aliases – Joins ; Changing a Table Alias
Column Aliases Basic Column Aliases ; Other Ways to Create Column Aliases ; Aliases in WHERE and ORDER BY Clauses
Ordering Rows Simple Sorting ; Sorting by Multiple Columns
Miscellaneous SELECT Tricks Selecting All Columns Using * ; Selecting Unique Rows ; Showing Top and Bottom Rows ; Including Ties
Using UNION to Combine Results
Query Designer (6 pages) Starting Query Designer What Query Designer is and does ; Starting Query Designer
Using Query Designer Choosing Tables ; Adding/Removing Tables ; The Parts of Query Designer ; The Non-Existent Results Pane/Execute Button ; Working with Columns ; Finishing Work in Query Designer
Editing Generated SQL
Advanced Features Inner and Outer Joins ; Grouping
Criteria using WHERE (8 pages) The WHERE Clause Relational Operators
Criteria with Numbers Using Comparisons ; Finding Numbers in a Given Range
Criteria using Text Exact Matches ; Wildcard Matches using LIKE ; Using Special Characters with LIKE ; Ranges and Wildcards ; Using Relational Operators with Text ; Case Sensitivity
Criteria for Dates Using Dates in Criteria ; Using Dates with Wildcards
Combining Criteria
Nulls An Example of Testing for Nulls ; Entering Nulls into a Table
Exporting to Excel (5 pages) Copying and Pasting Copying Column Headers by Default
Exporting Data Step 1 – Getting the Query ; Step 2 – Starting to Export Data ; Step 3 – Choosing the Source ; Step 4 – Choosing the Destination ; Step 5 – Specifying the Data to Export ; Step 6 – Specifying how to Export ; Step 7 – Finishing the Export
Calculations (14 pages) Creating Calculated Columns Giving Calculated Columns Aliases ; Using Column Aliases in ORDER BY Clauses ; Column Aliases Don’t Work in WHERE Criteria
Using SQL Functions Typing an SQL Function ; Getting the Full List of Functions
Casting Data Types The Need for Casting ; The CAST Function ; The CONVERT Function
Numerical Calculations Mathematical Symbols and BODMAS ; The Modulus Operator (%) ; Mathematical Functions ; The Importance of Casting Numbers for Calculations
Text Calculations Concatenating Text, and the Need for Data Conversion ; Functions to Turn Numbers into Text ; Functions to Search for and Replace Text ; Functions for Extracting Text ; Changing the Case of Text ; Functions for Trimming Text ; Other Text Functions ; Worked Example – 1 ; Worked Example – 2 ; Worked Example – 3
Dealing with Nulls The ISNULL Function ; The COALESCE Function
The CASE Statement (4 pages) The Searched Case Expression Example: Film Bands ; Example: Film Era ; Using CASE in WHERE Criteria
The Simple Case Statement
Nested CASE Statements
Date Calculations (11 pages) How Dates and Times Work How SQL Server Stores Dates and Times ; Displaying Dates/Times ; GETDATE – the Current Date/Time ; Dates Prefer American Format
Formatting Dates using FORMAT The Available Codes ; Using the Culture Argument ; How Slow is the Format Function?
Formatting Dates using CONVERT
Parts of a Date: DATEPART and DATENAME Displaying a Day Suffix
Getting the Difference between Dates Subtracting One Date from Another ; The DATEDIFF Function
Calculating Ages Correctly Using DateDiff ; Dividing Someone’s Age in Days by 365 ; Getting the Exact Age
Adding Dates using DATEADD
Joins (12 pages) Overview of Joins What is a Join? ; The Types of Join
Understanding your Database How Relationships Work (Reminder)
Easy Joins, using Query Designer
Inner Joins The Syntax of an Inner Join ; Our Example – Joining the Film and Director Tables ; Joining more than One Table ; Variations on Inner Join Syntax ; Composite Joins ; Joining by Expressions
Outer Joins Outer Joins using Query Designer ; Outer Joins in SQL ; Left and Right Outer Joins ; Picking Out Unmatched Rows ; Full Outer Joins
Cross Joins A Practical Example of Cross Joins
Self-Joins
Summarising Data (11 pages) Simple Summarising Syntax of a Simple Summary
Counting Counting All of a Table’s Rows ; Counting Non-Null Columns ; Counting Unique Values
Grouping Why you Need GROUP BY ; The GROUP BY Clause ; Grouping by Multiple Columns ; Grouping by Expressions ; Grouping without Aggregating
Filtering Results using HAVING
Casting Data for (eg) Averages
Dealing with Nulls The Default Treatment of Nulls ; Forcing SQL to Include Nulls
Additional Options when Grouping Using ALL to Show Missing Rows ; Using CUBE to Show All Combinations ; Using GROUPING to Show Levels
Views (10 pages) Why Views are Useful Use 1: Pre-Joining Tables ; Use 2: Virtually Renaming Columns
Views using the Designer Starting the Designer ; Choosing Columns ; Sorting and Filtering ; Adding Grouping ; Executing a View ; Saving and Closing Views ; Seeing your View in Object Explorer ; Running a View ; Changing a View
Scripting Views Creating a New View ; Changing an Open View in Script ; Changing a View’s Script from Object Explorer
Switching between the Designer and Scripting
CTEs and Derived Tables (5 pages) Multi-Stage Queries
Derived Tables
Single CTEs (Common Table Expressions) Syntax of Single CTEs ; The CTE for our Example
Multiple CTEs Syntax of Multiple CTEs ; Example of a Multiple CTE
Subqueries (4 pages) Single-Value Subqueries Example: Showing the Name of the Longest Film
ANY, ALL, IN and NOT IN
Correlated Subqueries Correlated Subqueries: Definition and Example ; Alternatives to Correlated Subqueries ; Considering Speed ; Using EXISTS to Check whether Rows are Returned
Ranking and Percentiles (3 pages)

Download this chapter
Ranking and Numbering Simple Row Numbering
Leading and Lagging Example of LAG: Actors Born One Week Apart
Percentiles Percentile Rankings

Advanced SQL courseware

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

Chapter Sections Subsections
The Movies Database (1 page) Our Example Database
Stored Procedures (10 pages) Overview What is a Stored Procedure? ; Advantages and Disadvantages
Creating Stored Procedures Typing in a Stored Procedure ; Creating a Stored Procedure using a Template ; Executing the Query to Create your Stored Procedure ; Viewing your Stored Procedure
Altering a Stored Procedure Altering an Open Stored Procedure ; Altering a Procedure in a Database
Executing Stored Procedures Refreshing your Local Cache ; Altering and Executing a Stored Procedure Together ; Selecting a Stored Procedure Name to Run It
Renaming and Deleting Stored Procedures Renaming/Deleting a Procedure with the Menu ; Deleting a Procedure in Script ; Renaming a Procedure in Script
System Stored Procedures Listing System Stored Procedures ; Useful System Stored Procedures
Getting Help on SQL Context-Sensitive Help ; Tips on Googling
Variables (8 pages)

Download this chapter
Declaring Variables Syntax for Declaring a Variable
Using Variables Setting the Value of a Variable ; Showing the Values of Variables ; Scope of Variables ; Incrementing and Concatenating Variables ; The Importance of Casting
Using Variables with Subqueries An Alternative Approach: Aggregate Functions
Storing Column Values in Variables Storing a Single Row’s Values ; Accumulating Numbers ; Accumulating Text
Global Variables Special Considerations when using @@ROWCOUNT
Variable and Parameter Data Types (3 pages) Numeric Data Types Integer Variable/Parameter Types ; Decimal and Numeric Types
Character Data Types Types of Character Storage ; Variable Length Data Types ; Fixed Length Data Types
Date/Time Data Types
Stored Procedure Parameters (9 pages) Overview Syntax of Parameters
Simple Parameters Step 1 – Specifying the Parameters ; Step 2 – Coding the Parameters ; Step 3 – Referencing the Parameters ; Using Text Wildcards as Parameters
Running Procedures using Parameters Positional Arguments ; Named Arguments ; Right-clicking to Execute a Procedure
Default Parameter Values Setting Default Values to Null ; The Perfect Stored Procedure?
The RETURN Statement
Output Parameters
Conditions and Loops (5 pages) IF Conditions Simple Conditions ; Using BEGIN … END ; Using ELSE ; Nesting Conditions and Indentation ; Using CASE to Avoid IF
Looping using WHILE The Syntax of WHILE Loops ; Breaking out of Loops
Scalar Functions (7 pages) Overview Syntax of a Scalar Function
Writing a Scalar Function Specifying Input Parameters and Return Types ; Writing the Function Itself
Running a Function Calling a Function on its Own ; Calling a Function within a SELECT Statement
Worked Examples Example One – Returning a Person’s Status ; Example Two – Profitability ; Example Three – Categorisation (by Oscar Type)
Limitations of Functions Assessing Function Speed
Error Handling (7 pages) About Errors
TRY / CATCH Syntax of TRY / CATCH ; Example of a Simple Error Trap ; Nesting TRY Statements
Error Functions T-SQL Error Functions ; Error Severity Levels ; Showing Errors within a TRY / CATCH Block
Customising Error Messages Viewing the Full List of Error Messages ; Creating your Own Errors ; Customising your own Error Messages
Deleting Data (3 pages) Deleting (Dropping) Tables Dropping a Table if it Exists ; Using Error Trapping to Check Existence ; Using SYS.OBJECTS and OBJECT_ID
Deleting Rows Differences between TRUNCATE and DELETE FROM
Updating Data (3 pages) The UPDATE Command An Example – Changing Genres for Films
Updating using JOIN The Obvious Answer doesn’t Work ; The Correct Syntax
Inserting Data (9 pages) Three Possible Ways to Insert
Creating Tables from Existing Data (SELECT INTO) Step 1 – Getting the Data for your New Table ; Step 2 – Making a New Table ; Step 3 – Checking the Table Created
Inserting Multiple Rows into an Existing Table Step 1 – Understanding the Syntax ; Step 2 – Working out what to do ; Step 3 – Mapping the Columns ; Step 4 – Creating the Query
Inserting Single Rows Syntax of INSERT INTO … VALUES ; Example Code to Insert a New Row ; Inserting a Batch of Single Rows
INSERT INTO – More Possibilities Missing out Columns ; Using a Stored Procedure’s Output ; Outputting Inserted Rows ; Getting Inserted Row Numbers with @@IDENTITY
Creating Tables (10 pages) Setting Up our Example The Example Used in this Chapter ; Creating and Dropping Databases
Creating Tables
Setting Primary Keys Creating a Primary Key when Creating Tables ; Creating a Primary Key Afterwards
Setting a Default Value for a Column
Preventing Null Values in a Column
Putting Checks or Constraints on a Column
Foreign Keys and Relationships Our Example ; Foreign Keys ; Creating a Foreign Key Constraint
Two Reasons/Ways to Index a Column Creating an Index to Speed Up Queries ; Enforcing Uniqueness with an Index
A Complete Example
Transactions (5 pages) The Concept Syntax of a Transaction
A Simple Example
Case Study – Recategorising Films The Problem ; The Algorithm ; The Procedure
Errors and Transactions
Temporary Tables (8 pages) Overview of Temporary Tables Local and Global Temporary Tables ; How Temporary Tables are Stored
Creating and Deleting Temporary Tables Creating Temporary Tables ; Deleting Temporary Tables
Scope of Temporary Tables Temporary Tables are Tied to the Queries Creating Them ; Visibility of Temporary Tables ; Scope of Temporary Tables in Stored Procedures
Case Study – Successful People Step 1 – Busy Actors (Creating the Table) ; Step 2 – Busy Directors (Inserting Rows) ; Final Answer with Problems Solved
Table Variables (2 pages) About Table Variables
Case Study Revisited
Comparing Table Types (2 pages) Differences between Table Variables and Temporary Tables Speed ; Limitations of Table Variables ; Limitations of Temporary Tables
Table-Valued Functions (5 pages) The Two Types of Table-Valued Functions Types of Table-Valued Functions ; Where to Find Them
In-line Table-Valued Functions Syntax of In-Line TVFs ; Where Stored Procedures Fall Short ; The In-Line TVF Solution ; Joins with Table-Valued Functions
Multi-Statement Table-Valued Functions Syntax of an MSTVF ; Example of an MSTVF
Cursors (2 pages) About Cursors Reasons to Use Cursors ; The Syntax of a Basic Cursor
Example of a Cursor
Debugging in SQL (4 pages) Example Used
Debugging Starting and Stopping Debugging ; Stepping Through Code ; Setting and Unsetting Breakpoints ; Viewing Variable Values by Hovering ; Viewing Variables in the Locals Window
Dynamic SQL (2 pages)

Download this chapter
The EXEC Command and Dynamic SQL Why not to Use Dynamic SQL
Example –Parameterising Row Selection
Pivoting Data (8 pages) Overview
The Two Stages of Creating a Pivot Query Step 1 – Assembling the Data ; Step 2 – Pivoting the Assembled Data
Varying the Number of Row Fields Pivot Queries with no Row Headings ; Pivot Queries with Multiple Row Headings
Queries Based on Pivot Queries
Getting and Using Dynamic Columns Step 1 – Get a Comma-Delimited List ; Step 2 – Build up the SQL Statement ; Step 3 – Test the SQL ; Step 4 – Execute the SQL
Triggers (5 pages) Overview of Triggers Syntax of a Trigger
Working with Triggers Creating a Trigger ; Viewing Triggers ; Enabling and Disabling Triggers ; Deleting Triggers
More Sophisticated Triggers Tables Created by Triggers
A Case Study: Transactions in Triggers

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

Visual Basic courseware

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

Chapter Sections Subsections
Windows Forms Applications (4 pages) WinForms and WPF Compared A Typical WinForms Application ; The Same Application in WPF ; How WPF Forms are Stored ; Differences between WinForms and WPF
Coding WinForms and WPF Forms
Visual Studio Basics (10 pages) Setting the Default Start-up Page
Creating Appropriate Settings
Creating Projects Choices of Programming Languages for Projects ; Creating New Folders for Projects
Saving and Closing Files and Projects Closing One Window ; Closing All or Nearly All Windows ; Moving Between Windows ; Saving and Closing a Project
Auto-hidden, Docked and Floating Windows Auto-Hidden Windows ; Docked Windows ; Floating Windows
The Three Most Useful Windows Solution Explorer ; Properties Window ; The Toolbox ; Displaying the Main Windows
Drawing Forms (14 pages)

Download this chapter
Creating a New Form
Setting Useful Form Properties
Controls and the Toolbox Definition of Controls ; Using the Toolbox
Adding Controls to a Form
Naming Controls Modified Hungarian Notation ; A List of Three-Letter Prefixes
Selecting Controls Selecting a Single Control ; Selecting Several Controls ; Selecting All Controls ; The Dominant Control ; Changing the Dominant Control
Formatting Controls and Forms Resizing Controls ; Changing how Controls Look ; Moving Controls ; Changing Alignment (within Controls) ; Changing Control Alignment Relative to the Form ; Aligning Controls against Each Other ; Making Controls the Same Size ; Spacing Controls Equally
Navigating in a Form Accelerator Keys ; Tab Order ; Default Buttons
Tooltips
Copying and Editing Forms (2 pages) Copying Forms
How Visual Basic Creates Forms
Running Applications (4 pages) Setting the Default Project/Form
Building a Project Manually Building a Project or Projects ; Dealing with Build Errors
Running and Stopping an Application Running an Application ; Stopping an Application
Form events (6 pages) Events Event Terminology
Attaching Code to a Control or Form Attaching Code to a Control’s Default Event ; Attaching Code to any Event
Switching Between Form Design and Code View Using the Keyboard ; Using Solution Explorer
Those Strange Event Arguments Renaming an Event Subroutine ; Every Event Passes Two Arguments ; Argument 1 – The Object that Called the Event ; Argument 2 – The Event Arguments
Running Code on Form Load
Coding in Visual Basic (10 pages) Using AutoCompletion Methods and Properties
ME and MY Using ME to Make Typing Easier ; Using MY to Access Things
Commenting Code Commenting Out Code
Laying Out Your Code Continuation Characters ; Indentation of Code ; Using Code Regions
Find and Replace
Handling Syntax Errors Viewing Syntax Errors ; Using the Error List Window ; Showing Line Numbers ; What the Yellow and Green Lines Mean
Displaying Messages
Code Snippets Using Code Snippets
Variables and Data Types (14 pages) Why Use Variables?
Declaring Variables Syntax of DIM Statement ; Declaring Variables ; Using Modified Hungarian Notation ; Default Values for Variables ; Problems with Declaring Variables within Clauses
Setting Values in Variables Declaring Integer Variables and Adding/Subtracting ; Accumulating Text in String Variables
Variable Data Types Mapping VB Data Types to the CLR Runtime ; A Lazy Person’s Data Types ; Logical Values ; Integers ; Decimal (Floating Point) Numbers ; Strings and Text ; Dates and Times ; Objects
Converting Variables Conversion Using Convert.To ; ToString – Special Case for String Conversions ; Casting Using CType
Forcing Variable Declaration and Correct Casting Option Strict and Option Explicit ; Setting the Options Globally
Variable Scope
Notes on Working with Specific Data Types Working with Strings ; Splitting Strings ; Formatting Dates ; Working with Numbers – Possible Operations
Testing Data Types
Enumerations and Constants (4 pages) Enumerations What are Enumerations, and Why Use Them? ; Declaring Enumerations ; Giving Enumerations Data Type and Values ; Referring to Enumerations as Integers and Strings
Constants
Using Resources Using a Resource
Conditions and Loops (6 pages) Conditions Using IF for Conditions ; Using the More Elegant Select … Case
Looping in Visual Basic Looping Over a Collection/Array ; Looping a Given Number of Times ; Looping While a Condition is True ; Looping Until a Condition is True ; Variations on Loops
Arrays (4 pages) Overview of Arrays Declaring Arrays ; Setting Array Values
Listing Array Elements
Basing ComboBoxes or ListBoxes on Arrays Sorting Arrays
Splitting strings into arrays
Simple Form Controls (8 pages) Types of Text Box Multiple Line Text Boxes ; Formatted Text Boxes ; Masked Text Boxes ; NumericUpDown Controls
Entering Dates DateTimePicker Controls ; MonthCalendar Controls
WebBrowser Controls
Using GroupBoxes and Panels Using Panels ; Using GroupBoxes ; Container Tab Order
Properties (4 pages) Designing Properties Three Types of Property
Creating Properties Read-Only Property (Product Name) ; Write-Only Property (Results) ; Read-Write Property (Quantity)
Using Properties
Data Structures and Lists (6 pages) Overview Example of List Code
Basic List Commands Creating a List ; Adding Items to a List ; Counting the Items in a List ; Displaying All of the Items in a List (FOR EACH) ; Removing Items from a List
Finding Particular Items in a List The Various Find Methods ; Lambda Function Syntax for Find Methods ; Getting a Subset of a List – Method 1: Using FindAll ; Getting a Subset of a List – Method 2: Using GetRange
Working with Strings Joining List Items Together ; Splitting Text into a List
Modular Programming (4 pages) What is Modular Code? A Coding Example
Passing Arguments What is an Argument? ; Passing Arguments by Value (ByVal) ; Passing Arguments by Reference (ByRef)
Optional Arguments An Alternative Method: Using Different Signatures
Functions (2 pages) Procedures versus Functions Specifying a Function ; Code for Functions
Incorporating Functions in Programs
Validation of Forms (6 pages) Overview of Validation Controls to Use to Avoid Validation
Validating Controls and Forms Validation Events ; Configuring your Cancel Button ; A Typical Validation Routine
Error Providers Adding Error Providers ; Setting and Cancelling ErrorProviders in Code
Validation the Hard Way
Creating menus (8 pages) Anatomy of a Menu
Creating a Menu Adding a MenuStrip Control to your Form ; Creating Menu Items ; Adding Separators to Menus ; Naming your Menus ; Assigning Macros to Menus
Short-Cut Keys Assigning Shortcut Keys to Menus ; Adding Additional Shortcut Keys
Checking and Enabling Menus
Context (Short-Cut) Menus Working Out Which Context Menus You Need ; Creating a Context Menu ; Assigning Context Menus to Controls ; Coding Context Menus
Toolbars and Status Bars (6 pages) Toolbars – What You Should Know
Creating Toolbars Creating and Naming a Toolbar ; Positioning the Toolbar below the Menu ; Initialising the Tools on your Toolbar ; Adding your own Tools
Assigning Code to Toolbars
Status Bars Creating Status Bars ; Adding a Status Label to the Status Bar
Adding a Progress Bar to your Status Bar
Coding menus and toolbars (8 pages) About this Chapter Our Example – Googlewhacking ; Sharing Code between Menus, Toolbars, Keys and Context Menus
Enabling/Disabling Our Tools and Menus Properties to Determine if Actions are Possible ; A Routine to Conditionally Enable/Disable Menus and Toolbars ; Running our Enabling/Disabling Code
Getting the Actions to Work Creating the Action Routines ; Assigning the Actions to the Toolbar ; Assigning Actions to the Menu
Keyboard Shortcuts
Working with files and folders (10 pages) The Different File and Folder Dialog Boxes
Adding File or Folder Dialog Boxes to a Form Adding Dialog Box Controls Graphically ; Creating and Displaying Dialog Boxes Programmatically
Parts of a File Dialog Box
Customising a File Dialog Box File Filters ; File Extensions ; Allowing Read-Only Files ; Selecting Multiple Files ; Setting and Restoring the Initial Directory ; Checking if Files/Folders Exist ; Checking How to Proceed if Files Exist
Browsing for a Folder
Reading and Writing Files Our Example ; Referencing the System.IO Namespace ; Writing Out a Mortgage Application Line ; Reading in the Mortgage Application File
Debugging and Error Handling (12 pages) Catching Errors Syntax of the Try … End Try Statement ; Our Example for Validating Numbers ; Catching Specific Errors
Reacting to Errors
Debugging Setting (and Unsetting) a Breakpoint ; Starting to Debug ; Changing the Next Executable Line ; Stepping Through Code ; Viewing Variables ; Stopping Debugging
The Immediate and Output Windows Displaying the Windows ; Requesting Current Program Information ; Changing Variable Values while Debugging ; Writing to the Immediate or Output Window ; Useful Debug Commands ; Controlling which Window Debug Goes To
Other Debugging Tools Conditional Breakpoints ; Watching Variables ; The Call Stack
Linking to Databases (4 pages) Choosing a Database Application
Our Example
Data Sources, TableAdapters and DataSets DataSets ; DataSources ; How TableAdapters Work ; Standard Methods for TableAdapters
Data Sources (4 pages) Creating DataSources Starting the Data Source Wizard ; Creating a Connection to the Database ; Choosing Tables and Views
Viewing and Editing Data Sources and Datasets The Data Sources Window ; Editing Datasets
ComboBoxes and ListBoxes (4 pages) ComboBoxes and ListBoxes The Difference between ComboBoxes and ListBoxes ; Creating ComboBoxes or ListBoxes
Data Bound ComboBoxes and ListBoxes Display and Value Members ; Making a Combo Box Data Bound
Sorting a ComboBox or ListBox Starting to Configure the TableAdapter ; Changing the Sort Order of the Query
DataGridViews (10 pages) Introduction to DataGridViews Creating a DataGridView the Easy Way
Creating a DataGridView Manually
Adding, Editing and Deleting Columns
Formatting and Using Styles How Default Styles Work ; Setting an Overall Default Style ; Formatting Alternating Rows a Different Colour ; Setting a Column and Row Heading Style ; Setting Styles for a Specific Column ; Formatting Numbers or Dates
Changing DataGridView Size Making a DataGridView Stretchy ; Making the Last Column of Variable Width ; What Happens when you AutoSize Columns/Rows
Sorting DataGridViews
Disabling Inserting and Deleting
Making DataGridViews Editable (4 pages) Writing Changes to a Database
Checking your TableAdapter has Update Statements Configuring the TableAdapter ; Method 1 – Generate your SQL Update Statements ; Method 2 – Ensure the Statements are Created
Writing Changes to your Database Why Doesn’t My Update Method Work?
Reacting to DataGridView Events (10 pages) Overview of DataGridView Events Useful Standard Event Properties
Typical Event-Driven Tasks Using an Enumeration to Number Columns ; Conditional Formatting ; Setting Default Values for New Records ; Asking for Confirmation when Deleting Rows
Validating Data Entered Validating Row by Row ; Handling Errors by Individual Cell ; Checking Whole Rows ; Showing Error Icons ; Checking Parsing has Worked
The Order of Validation Events
Advanced DataGridView topics (14 pages) The Ultimate VB Application
Adding Buttons to a DataGridView Creating your Button ; Setting Properties for the Button ; Coding your Button
Adding a Bound Combobox Adding the New Dropdown Column ; Linking the New Column to a Table
Getting the DataGridView to Filter Creating the Filter ComboBox ; Hiding the DataGridView Initially ; Adding a Filter to the DataGridView ; The TableAdapter Query Configuration Wizard ; Filtering the DataGridView when the ComboBox Changes
Creating a Single Client Editing Form Creating a Single Record Form ; The BindingNavigator Control ; Attaching Code to the DataGridView Control ; Attaching Code to Load the Single Record Form ; Saving a Record in the Single Record Form
Deploying Applications (2 pages) Publishing Applications
Installing a Published Application

Visual C# courseware

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

Chapter Sections Subsections
Windows Forms Applications (4 pages) WinForms and WPF Compared A Typical WinForms Application ; The Same Application in WPF ; How WPF Forms are Stored ; Differences between WinForms and WPF
Coding WinForms and WPF Forms
Visual Studio Basics (10 pages) Setting the Default Start-up Page
Creating Appropriate Settings
Creating Projects Choices of Programming Languages for Projects ; Creating New Folders for Projects
Saving and Closing Files and Projects Closing One Window ; Closing All or Nearly All Windows ; Moving Between Windows ; Saving and Closing a Project
Auto-hidden, Docked and Floating Windows Auto-Hidden Windows ; Docked Windows ; Floating Windows
The Three Most Useful Windows Solution Explorer ; Properties Window ; The Toolbox ; Displaying the Main Windows
Drawing Forms (14 pages)

Download this chapter
Creating a New Form
Setting Useful Form Properties
Controls and the Toolbox Definition of Controls ; Using the Toolbox
Adding Controls to a Form
Naming Controls Modified Hungarian Notation ; A List of Three-Letter Prefixes
Selecting Controls Selecting a Single Control ; Selecting Several Controls ; Selecting All Controls ; The Dominant Control ; Changing the Dominant Control
Formatting Controls and Forms Resizing Controls ; Changing how Controls Look ; Moving Controls ; Changing Alignment (within Controls) ; Changing Control Alignment Relative to the Form ; Aligning Controls against Each Other ; Making Controls the Same Size ; Spacing Controls Equally
Navigating in a Form Accelerator Keys ; Tab Order ; Default Buttons
Tooltips
Copying and Editing Forms (2 pages) Copying Forms
How Visual Studio Creates Forms
Running Applications (4 pages) Setting the Default Project/Form
Building a Project Manually Building a Project or Projects ; Dealing with Build Errors
Running and Stopping an Application Running an Application ; Stopping an Application
Form events (8 pages) Events Event Terminology
Attaching Code to a Control or Form Attaching Code to a Control’s Default Event ; Creating a New Event-Handler for an Event ; Handling an Event with an Existing Routine
Switching Between Form Design and Code View Using the Keyboard ; Using Solution Explorer
Those Strange Event Arguments Renaming an Event Subroutine ; Every Event Passes Two Arguments ; Argument 1 – The Object that Called the Event ; Argument 2 – The Event Arguments
Running Code on Form Load
Coding in C# (10 pages) Using AutoCompletion Methods and Properties
THIS and ENVIRONMENT Using THIS to Make Typing Easier ; Using ENVIRONMENT to Get Information
Commenting and Uncommenting Code Commenting Out Code
Laying Out Your Code Statements Spanning Multiple Lines ; Indentation of Code ; Using Code Regions
Find and Replace
Handling Syntax Errors Viewing Syntax Errors ; Using the Error List Window ; Showing Line Numbers ; What the Yellow and Green Lines Mean
Displaying Messages The Parts of a Message Box you can Customise
Code Snippets Using Code Snippets
Variables and Data Types (14 pages) Why Use Variables?
Declaring Variables Declaring Variables ; Using Modified Hungarian Notation ; Default Values for Variables ; Problems with Declaring Variables within Clauses
Setting Values in Variables Declaring Integer Variables and Adding/Subtracting ; Accumulating Text in String Variables
Variable Data Types Mapping C# Data Types to the CLR Runtime ; A Lazy Person’s Data Types ; Logical Values ; Integers ; Decimal (Floating Point) Numbers ; Strings and Text ; Dates and Times ; Objects
Converting Variables Conversion Using Convert.To ; ToString() – Special Case for String Conversions ; Casting Data Types
Variable Scope
Notes on Working with Specific Data Types Working with Strings ; Escape Characters ; Splitting Strings ; Formatting Dates ; Working with Numbers – Possible Operations
Testing Data Types
Enumerations and Constants (4 pages) Enumerations What are Enumerations, and Why Use Them? ; Declaring Enumerations ; Giving Enumerations Data Type and Values ; Other Examples of Enumerations
Constants
Using Resources Using a Resource
Conditions and Loops (6 pages) Conditions Using IF for Conditions ; Using the More Elegant SWITCH statement
Looping in C# Looping Over a Collection/Array ; Looping a Given Number of Times ; Looping While a Condition is True
Breaking Out of Loops
Arrays (4 pages) Overview of Arrays Setting Array Values
Listing Array Elements
Basing ComboBoxes or ListBoxes on Arrays Sorting Arrays
Splitting strings into arrays
Simple Form Controls (8 pages) Types of Text Box Multiple Line Text Boxes ; Formatted Text Boxes ; Masked Text Boxes ; NumericUpDown Controls
Entering Dates DateTimePicker Controls ; MonthCalendar Controls
WebBrowser Controls
Using GroupBoxes and Panels Using Panels ; Using GroupBoxes ; Container Tab Order
Properties (4 pages) Designing Properties Three Types of Property
Creating Properties Read-Only Property (Product Name) ; Write-Only Property (Results) ; Read-Write Property (Quantity)
Using Properties
Lists (6 pages) Overview Example of List Code
Basic List Commands Creating a List ; Adding Items to a List ; Counting the Items in a List ; Displaying All of the Items in a List (FOR EACH) ; Removing Items from a List
Finding Particular Items in a List The Various Find Methods ; Lambda Expression Syntax for Find Methods ; Getting a Subset of a List – Method 1: Using FindAll ; Getting a Subset of a List – Method 2: Using GetRange
Working with Strings Joining List Items Together ; Splitting Text into a List
Modular Programming (4 pages) What is Modular Code? A Coding Example
Passing Arguments What is an Argument? ; Types of Argument ; Examples of Each Type of Argument
Optional Arguments An Alternative Method: Using Different Signatures
Functions (2 pages) Procedures versus Functions Specifying a Function ; Code for Functions
Incorporating Functions in Programs
Validation of Forms (6 pages) Overview of Validation Controls to Use to Avoid Validation
Validating Controls and Forms Validation Events ; Configuring your Cancel Button ; A Typical Validation Routine
Error Providers Adding Error Providers ; Setting and Cancelling ErrorProviders in Code
Validation the Hard Way
Creating menus (8 pages) Anatomy of a Menu
Creating a Menu Adding a MenuStrip Control to your Form ; Creating Menu Items ; Adding Separators to Menus ; Naming your Menus ; Assigning Macros to Menus
Short-Cut Keys Assigning Shortcut Keys to Menus ; Adding Additional Shortcut Keys
Checking and Enabling Menus
Context (Short-Cut) Menus Working Out Which Context Menus You Need ; Creating a Context Menu ; Assigning Context Menus to Controls ; Coding Context Menus
Toolbars and Status Bars (6 pages) Toolbars – What You Should Know
Creating Toolbars Creating and Naming a Toolbar ; Positioning the Toolbar below the Menu ; Initialising the Tools on your Toolbar ; Adding your own Tools
Assigning Code to Toolbars
Status Bars Creating Status Bars ; Adding a Status Label to the Status Bar
Adding a Progress Bar to your Status Bar
Coding menus and toolbars (8 pages) About this Chapter Our Example – Googlewhacking ; Sharing Code between Menus, Toolbars, Keys and Context Menus
Enabling/Disabling Our Tools and Menus Properties to Determine if Actions are Possible ; A Routine to Conditionally Enable/Disable Menus and Toolbars ; Running our Enabling/Disabling Code
Getting the Actions to Work Creating the Action Routines ; Assigning the Actions to the Toolbar ; Assigning Actions to the Menu
Keyboard Shortcuts
Working with files and folders (10 pages) The Different File and Folder Dialog Boxes
Adding File or Folder Dialog Boxes to a Form Adding Dialog Box Controls Graphically ; Creating and Displaying Dialog Boxes Programmatically
Parts of a File Dialog Box
Customising a File Dialog Box File Filters ; File Extensions ; Allowing Read-Only Files ; Selecting Multiple Files ; Setting and Restoring the Initial Directory ; Checking if Files/Folders Exist ; Checking How to Proceed if Files Exist
Browsing for a Folder
Reading and Writing Files Our Example ; Referencing the System.IO Namespace ; Writing Out a Mortgage Application Line ; Reading in the Mortgage Application File
Debugging and Error Handling (12 pages) Catching Errors Syntax of the Try / Catch Statement ; Our Example for Validating Numbers ; Catching Specific Errors
Reacting to Errors
Debugging Setting (and Unsetting) a Breakpoint ; Starting to Debug ; Changing the Next Executable Line ; Stepping Through Code ; Viewing Variables ; Stopping Debugging
The Immediate and Output Windows Displaying the Windows ; Requesting Current Program Information ; Changing Variable Values while Debugging ; Writing to the Immediate or Output Window ; Useful Debug Commands ; Controlling which Window Debug Goes To
Other Debugging Tools Conditional Breakpoints ; Watching Variables ; The Call Stack
Linking to Databases (4 pages) Choosing a Database Application
Our Example
Data Sources, TableAdapters and DataSets DataSets ; DataSources ; How TableAdapters Work ; Standard Methods for TableAdapters
Data Sources (4 pages) Creating DataSources Starting the Data Source Wizard ; Creating a Connection to the Database ; Choosing Tables and Views
Viewing and Editing Data Sources and Datasets The Data Sources Window ; Editing Datasets
ComboBoxes and ListBoxes (4 pages) ComboBoxes and ListBoxes The Difference between ComboBoxes and ListBoxes ; Creating ComboBoxes or ListBoxes
Data Bound ComboBoxes and ListBoxes Display and Value Members ; Making a Combo Box Data Bound
Sorting a ComboBox or ListBox Starting to Configure the TableAdapter ; Changing the Sort Order of the Query
DataGridViews (10 pages) Introduction to DataGridViews Creating a DataGridView the Easy Way
Creating a DataGridView Manually
Adding, Editing and Deleting Columns
Formatting and Using Styles How Default Styles Work ; Setting an Overall Default Style ; Formatting Alternating Rows a Different Colour ; Setting a Column and Row Heading Style ; Setting Styles for a Specific Column ; Formatting Numbers or Dates
Changing DataGridView Size Making a DataGridView Stretchy ; Making the Last Column of Variable Width ; What Happens when you AutoSize Columns/Rows
Sorting DataGridViews
Disabling Inserting and Deleting
Making DataGridViews Editable (4 pages) Writing Changes to a Database
Checking your TableAdapter has Update Statements Configuring the TableAdapter ; Method 1 – Generate your SQL Update Statements ; Method 2 – Ensure the Statements are Created
Writing Changes to your Database Why Doesn’t My Update Method Work?
Reacting to DataGridView Events (10 pages) Overview of DataGridView Events Useful Standard Event Properties
Typical Event-Driven Tasks Using an Enumeration to Number Columns ; Conditional Formatting ; Setting Default Values for New Records ; Asking for Confirmation when Deleting Rows
Validating Data Entered Validating Row by Row ; Handling Errors by Individual Cell ; Checking Whole Rows ; Showing Error Icons ; Checking Parsing has Worked
The Order of Validation Events
Advanced DataGridView topics (14 pages) The Ultimate C# Application
Adding Buttons to a DataGridView Creating your Button ; Setting Properties for the Button ; Coding your Button
Adding a Bound Combobox Adding the New Dropdown Column ; Linking the New Column to a Table
Getting the DataGridView to Filter Creating the Filter ComboBox ; Hiding the DataGridView Initially ; Adding a Filter to the DataGridView ; The TableAdapter Query Configuration Wizard ; Filtering the DataGridView when the ComboBox Changes
Creating a Single Client Editing Form Creating a Single Record Form ; The BindingNavigator Control ; Attaching Code to the DataGridView Control ; Attaching Code to Load the Single Record Form ; Saving a Record in the Single Record Form
Deploying Applications (2 pages) Publishing Applications
Installing a Published Application

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