This page has 0 threads | Add post

Courseware

Introducton to Excel VBA course manual

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

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

Chapter 1 - THE VISUAL BASIC EDITOR▼

Section	Title	Subsections	Page
1.1	The Visual Basic Editor	Displaying the Developer Ribbon Tab Opening the VB Editor	11
1.2	The VBE Screen	Opening and Closing Windows Repositioning Windows Docking Windows	12
1.3	The Main VBE Windows	The Project Explorer The Properties Window	14
1.4	VBE Settings	The Options Dialog Box Changing Font Formatting Options	15

Chapter 2 - WRITING SIMPLE VBA CODE▼

Section	Title	Subsections	Page
2.1	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	16
2.2	Writing Procedures	Types of VBA Procedure Inserting Procedures Starting a Subroutine Switching off Syntax Error Messages Setting the Scope of a Procedure	20
2.3	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	23
2.4	Writing Simple VBA Instructions	Objects Methods and Properties	26
2.5	Tools to Help with Writing Code	Choosing Which Tools are Enabled Using IntelliSense to Write Code Faster Using Tooltips Viewing Data Tips	27

Chapter 3 - SAVING AND OPENING FILES▼

Section	Title	Subsections	Page
3.1	Saving VBA Code	Where is Code Stored? Saving VBA Code Choosing the Correct File Type	29
3.2	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	31
3.3	Opening Files Which Contain VBA Code	Choosing to Enable VBA Content Macro Security Settings	33
3.4	Trusted Documents	Viewing Trusted Document Settings Disabling Trusted Documents	34

Chapter 4 - RUNNING VBA CODE▼

Section	Title	Subsections	Page
4.1	Running Code from Excel	Choosing from a List of Macros	35
4.2	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	36
4.3	When Things Go Wrong	Syntax Errors Compile Errors Run-Time Errors	40

Chapter 5 - BASIC USER INTERFACES▼

Section	Title	Subsections	Page
5.1	Keyboard Shortcuts	Assigning Keyboard Shortcuts in Excel Assigning Keyboard Shortcuts in Code	42
5.2	Form Control Buttons	Drawing Form Control Buttons Editing Form Control Buttons	43
5.3	ActiveX Command Buttons	Drawing ActiveX Command Buttons Attaching Code to the Click Event	44
5.4	AutoShapes and Pictures	Inserting Shapes and Pictures Assigning a Macro to a Shape or Picture	45
5.5	The Excel Ribbon	Modifying the Quick Access Toolbar Creating Ribbon Tabs	46

Chapter 6 - WORKING WITH RANGES▼

Section	Title	Subsections	Page
6.1	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	48
6.2	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	50
6.3	Rows and Columns	The Range, Rows and Columns Properties The EntireRow and EntireColumn Properties	51
6.4	Referring to the Active or Selected Cell	ActiveCell and Selection Activate vs. Select	52
6.5	The Offset Property	Offsetting from the ActiveCell Offsetting a Block of Cells	53
6.6	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	53
6.7	Referring to Regions of Cells	The CurrentRegion Property The CurrentArray Property	57
6.8	Referring to Special Cells		58
6.9	Referring to Used Cells	Referring to the Used Range The Last Used Cell	59

Chapter 7 - COLOURS IN VBA▼

Section	Title	Subsections	Page
7.1	Excel Colours	The Two Colour Properties of a Range The ColorIndex Colours	60
7.2	Colour Numbers and Names	VBA Colour Constants Excel’s RGB Constants The RGB Function	61

Chapter 8 - DISPLAYING MESSAGES▼

Section	Title	Subsections	Page
8.1	The MsgBox Function	Syntax and Parameters of a Message Box	62
8.2	Displaying Messages	Displaying a Simple Message A Note on Using Parentheses Concatenating a Message Changing Text Alignment Using Multiple Lines Customising the Title	63
8.3	Icons and Buttons	Modifying the Buttons Setting the Default Button Displaying Icons Combining Buttons and Icons	66

Chapter 9 - USER INPUTS▼

Section	Title	Subsections	Page
9.1	Asking Users for Input	Where to Store User Input	68
9.2	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	69
9.3	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	71
9.4	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 Selecting Cells Returning a Reference to a Range	74

Chapter 10 - VARIABLES AND DATA TYPES▼

Section	Title	Subsections	Page
10.1	Data Types in VBA	Summary of the VBA Data Types	77
10.2	Declaring and Using Variables	The Dim Statement Writing To and Reading From Variables	78
10.3	Declared vs. Non-Declared Variables	Non-Declared Variables Explicitly-Declared Variables Forcing Explicit Variable Declaration	79
10.4	Variables and Data Types	The Variant Data Type Declaring Multiple Variables Choosing the Correct Data Type	81
10.5	Converting Variable Data Types	Implicit Data Type Conversion The Problem with Implicit Type Conversion Explicit Data Type Conversion Checking for Dates and Numbers	83
10.6	The Scope of Variables	Procedure Level Variables Module Level Variables Project Level Variables	85
10.7	Constants	Declaring a Constant The Scope of Constants	87

Chapter 11 - WORKING WITH DATA▼

Section	Title	Subsections	Page
11.1	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	88
11.2	Working with Numbers	Testing if a Value is a Number Useful Numeric Functions	90
11.3	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	91
11.4	Working with Strings	Concatenating Strings Character Codes Special Character Constants Case Sensitivity Useful String Functions	94

Chapter 12 - TESTING CONDITIONS▼

Section	Title	Subsections	Page
12.1	The If Statement	Single-Line If Statements The Else Clause Block If Statements Nested Ifs The ElseIf Statement	97
12.2	Logical Tests and Operators	Comparison Operators Logical Tests and Boolean Values	100
12.3	Combining Conditions	The Or Operator The And Operator The Xor Operator	101
12.4	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	102
12.5	Conditional Functions	The IIf Function The Switch Function	104
12.6	The Select Case Statement	A Basic Select Case Statement Testing Multiple Values Testing a Range of Values	105

Chapter 13 - FOR NEXT LOOPS▼

Section	Title	Subsections	Page
13.1	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	106

Chapter 14 - CONDITIONAL LOOPS▼

Section	Title	Subsections	Page
14.1	The Do Loop	Exiting from a Do Loop	109
14.2	Do Until Loops	Writing a Do Until Loop The Loop Until Statement Breaking Out of a Loop	110
14.3	Do While Loops	Writing a Do While Loop The Loop While Statement	111

Chapter 15 - HOW VBA WORKS▼

Section	Title	Subsections	Page
15.1	Object Oriented Programming	The Building Blocks of an Object Oriented Language	112
15.2	Objects	Referring to Objects by Name Referring to Objects by Index Number Qualifying References to Objects Using Keywords to Reference Objects Using Object Codenames Using Object Variables	113
15.3	Collections	Referring to Collections	116
15.4	Methods	Applying Methods to Objects Passing Arguments to Methods Returning Values and References from Methods When to use Parentheses	117
15.5	Properties	Writing to a Property Read-Only Properties Property Data Types Reading from a Property Properties and Parameters	119
15.6	Getting Help in VBA	The Object Browser Context Sensitive Help Recording a Macro	121

Chapter 16 - FOR EACH LOOPS▼

Section	Title	Subsections	Page
16.1	Looping Through Collections	The For Each Loop A Basic Example	123
16.2	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	124
16.3	Looping Over the Workbooks Collection	Processing all Open Workbooks	126
16.4	Looping Over a Collection of Range Objects	Specifying the Range to Loop Over Looping Through a Column of Data	127
16.5	Nesting For Each Loops	Looping Over Shapes on All Worksheets Looping Through Sheets in All Open Workbooks	128

Chapter 17 - MODULAR CODE AND FUNCTIONS▼

Section	Title	Subsections	Page
17.1	Modular Code	Our Example	129
17.2	Breaking a Procedure into Parts	Creating Module Level Variables Getting Input from the User Retrieving the Related Values Building and Showing a Message Putting it all Together	130
17.3	Procedures and Parameters	Our Example Defining Parameters Calling a Procedure which has Parameters Optional Parameters Assigning Default Values to Parameters Testing for Missing Arguments	132
17.4	Writing Functions	Returning a Value from a Function Returning a Reference from a Function Calling a Function Using Functions in a Worksheet Defining Function Parameters	135

Chapter 18 - DEBUGGING▼

Section	Title	Subsections	Page
18.1	Debugging Code	The Debug Toolbar	137
18.2	Running Code	Running a Procedure from Start to End Running a Procedure in Break Mode Stepping Through Code Changing the Next Instruction Editing Code in Break Mode	138
18.3	Debugging Modular Code	Viewing the Definition of a Procedure Stepping Over a Procedure Call	140
18.4	Breakpoints	Setting and Removing Breakpoints The Stop Statement Breaking Conditionally	141
18.5	The Immediate Window	Executing Instructions in the Immediate Window Asking Questions in the Immediate Window Printing to the Immediate Window	142
18.6	The Locals Window	Observing Variables	143
18.7	The Watch Window	Adding an Expression to Watch Types of Watch Adding a Quick Watch Editing and Removing Watches	144
18.8	The Call Stack	Displaying the Call Stack Using the Call Stack	146

Chapter 19 - HANDLING ERRORS▼

Section	Title	Subsections	Page
19.1	Run-Time Errors in VBA		147
19.2	Error Handling in VBA	Identifying Potential Run-Time Errors The On Error Statement	148
19.3	Using the On Error Statement	Ignoring Run-Time Errors Disabling an Error Handler	149
19.4	Creating a Custom Error Handler	Redirecting Your Code Writing the Error-Handling Section Exiting a Procedure before the Error-Handling Code The Complete Example	150
19.5	Resuming After an Error	Resuming at the Original Line Resuming at the Next Line Resuming at a Specified Line Why use Resume and Not GoTo?	152
19.6	The Err Object	Getting the Error Number and Description A Catch-All Approach to Error-Handling	154

Chapter 20 - EVENTS▼

Section	Title	Subsections	Page
20.1	Event Handlers	Objects Which Have Events Event Procedures vs. Normal Procedures	155
20.2	Creating a Simple Event Handler	Accessing the Object’s Code Choosing the Event Writing the Code Triggering the Event	156
20.3	Workbook Events	The Before Close Event The Before Save Event The Before Print Event The New Sheet Event New Chart	158
20.4	Worksheet Events	The Selection Change Event The Change Event Checking if the Target is Within a Specific Range	161
20.5	ActiveX Controls	Drawing ActiveX Controls Changing Properties of the Control Adding Code to the Control’s Events Prevent Controls from Taking the Focus	163

Chapter 21 - CREATING USER FORMS▼

Section	Title	Subsections	Page
21.1	User Forms	Creating a Working Form Our Example	165
21.2	Creating a User Form	Inserting a User Form into a Project Switching Between Form Views Removing Forms	166
21.3	Form Properties	Changing the Properties of a Form Some Common Form Properties Choosing Colours Setting Font Properties	168
21.4	Form Controls	The Toolbox Drawing a Control on a Form	170
21.5	Manipulating Controls	Selecting a Control Selecting Multiple Controls Resizing Controls Moving Controls Deleting Controls Copying and Pasting Controls	171
21.6	Laying Out Controls	The Form Grid The UserForm Toolbar	173
21.7	Grouping Controls	Grouping a Set of Controls Using Frames to Group Controls	174
21.8	Control Properties	Naming Controls Naming Conventions for Controls Size and Position Properties Formatting Properties	175

Chapter 22 - RUNNING USER FORMS▼

Section	Title	Subsections	Page
22.1	Running a Form	Choosing to Run a Form Closing a Running Form	177
22.2	Navigating a Form	Tab Order Accelerator Keys Keyboard Shortcuts The Default and Cancel Buttons	178

Chapter 23 - ADDING CODE TO FORMS▼

Section	Title	Subsections	Page
23.1	Making Forms Work	Our Example	181
23.2	Running User Forms	Running a Form as a Developer Running a Form as a User	182
23.3	Adding Code to a Form	Viewing a Form’s Code	183
23.4	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	184
23.5	Form and Control Events	Initialising a Form Clicking the Cancel Button Clicking the Add to List Button Writing Modular Code in Forms	186
23.6	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	188

Chapter 24 - ADVANCED FORM CONTROLS▼

Section	Title	Subsections	Page
24.1	Beyond the Basics	The Advanced Controls Available	193
24.2	Frames	Drawing Frames and Controls Looping Through Controls in a Frame	194
24.3	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	195
24.4	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	200
24.5	Check Boxes and Toggle Buttons	Check Box and Toggle Button Values The Click Event	202
24.6	Spin Buttons and Scroll Bars	Drawing Spin Buttons and Scroll Bars Scrolling Properties The Value Property The Change Event The SpinUp and SpinDown Events	203
24.7	MultiPage Controls	Selecting Parts of a MultiPage Control Working with Pages The Index and Value Properties Looping Through Pages and Controls	205

Chapter 25 - ADDITIONAL FORM CONTROLS▼

Section	Title	Subsections	Page
25.1	Additional Form Controls	Adding Items to the Toolbox Organising the Toolbox	207
25.2	Examples of Extra Controls	The Date and Time Picker The RefEdit Control Progress Bars	208

Chapter 26 - CONTROLLING OTHER APPLICATIONS▼

Section	Title	Subsections	Page
26.1	Referencing Object Libraries	Setting a Reference to an Object Library The Default References References and the Object Browser Microsoft Office Version Numbers	210
26.2	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	212
26.3	An Example for PowerPoint	Setting a Reference to the PowerPoint Object Library Opening PowerPoint and Creating a Presentation Creating a Title Slide Copying from Excel to PowerPoint Moving and Resizing PowerPoint Objects Saving the Presentation and Closing PowerPoint The Complete Example	216
26.4	An Example for Outlook	Setting a Reference to the Outlook Object Library The Complete Example	219
26.5	Controlling Applications without References	The CreateObject Function Using Object Variables Converting Constants to Numbers	220

Chapter 27 - VBA AND AI TOOLS▼

Section	Title	Subsections	Page
27.1	Choosing an AI Tool		222
27.2	Getting Simple Code	Our Example Getting the Prompt Right The Generated Code	223
27.3	Uploading Workbooks		225
27.4	A More Advanced Example	Our Example Writing the Prompt Improving the Solution	226
27.5	Optimising and Improving Code	Our Example What we Expect our AI Tool to Find Asking ChatGPT to Improve Code The Changes Made	228
27.6	Debugging		230
27.7	Global Changes and Reformatting	Example: Rewriting a Macro Making Global Changes	231

Chapter 28 - CRIB SHEET▼

Section	Title	Subsections	Page
28.1	VBA Reference	Creating Procedures Selecting and Activating Things Selecting a Range Relatively Messages and Inputs Declaring Variables Conditional Statements Looping	233

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

This page has 0 threads

Add a new post

Choose software

VBA course manuals