Introduction to Excel VBA

Video Lectures

Displaying all 76 video lectures.
Lecture 1
Getting Started in the VB Editor
Play Video
Getting Started in the VB Editor
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Before you can start writing VBA code for Excel you need to understand how to use the tool that you'll use to do your programming. This video teaches you how to get started using the Visual Basic Editor, including how to get into the application from various versions of Excel, how to work with the various windows, how to modify the default settings and how to work with modules. You won't learn how to write any code here, but this video will ensure your life will be as easy as possible when you do!

You can see written versions of our Excel VBA tutorials online at:
http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training...
Lecture 2
Writing Your First VBA Macro
Play Video
Writing Your First VBA Macro
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - VBA, or Visual Basic for Applications, is a powerful tool that you can use to automate Microsoft Excel. This video introduces you to the basics of writing code in the VB Editor, including how to create a new module, how to write a subroutine, some best practice guidelines for laying out your code and adding comments, and how to write simple VBA instructions. We'll also show you how to run the simple procedure that you'll write, and how to ensure that you can save your code and reopen the file which contains it.

You can see written versions of our Excel VBA tutorials online at:
http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training...
Lecture 3
What to do When Things Go Wrong (Errors and Debugging)
Play Video
What to do When Things Go Wrong (Errors and Debugging)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - If you've written at least one subroutine in VBA you'll probably already be familiar with the feeling of frustration when things don't work! This video teaches you about the different types of error that you're likely to encounter when programming in VBA and what to do when things go wrong. You'll learn the difference between syntax, compile and runtime errors, when each type is likely to happen and we'll even show you a few useful debugging techniques to help you when you're trying to figure out what went wrong!

You can see written versions of our Excel VBA tutorials online at:
http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training...
Lecture 4
Buttons, Toolbars and Keyboard Shortcuts
Play Video
Buttons, Toolbars and Keyboard Shortcuts
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - As a developer you'll be used to running most of your code from within the Visual Basic Editor environment. Your users, on the other hand, are more likely to require a simple interface (most likely involving buttons to click) that they can use to run your code. This video covers the main ways to provide users with the means to run your code including keyboard shortcuts, buttons and drawing objects with attached macros. We'll even show you how to customise the ribbon and toolbars in Excel for that extra, professional touch!

You can see written versions of our Excel VBA tutorials online at:
http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training...
Lecture 5
Selecting Cells (Range, Cells, Activecell, End, Offset)
Play Video
Selecting Cells (Range, Cells, Activecell, End, Offset)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Previous video - Buttons, Toolbars and Keyboard Shortcuts
http://www.youtube.com/watch?v=KZgOwmS3iFY

Next video - Worksheets, Charts and Sheets
http://www.youtube.com/watch?v=M3OE7Z62oGM

http://www.wiseowl.co.uk - You won't get far in Excel VBA without understanding how to refer to cells! This fairly long video covers most of the common, and some uncommon, techniques for selecting and referring to cells. You'll learn how to select cells by cell reference using the Range property, how to refer to cells by row and column number using the Cells property, and how to refer to the currently selected cell using the Activecell property. We also cover how to select multiple cells and how to refer to range names as well as a variety of relative selection tricks such as using the End property to jump to the bottom of a list, using the Offset property to move a specific number of rows and columns, and how to select an entire region of data with one simple instruction. Along the way you'll also learn several techniques for formatting and modifying cells, as well as how to copy and paste data in VBA.

You can see written versions of our Excel VBA tutorials online at:
http://www.wiseowl.co.uk/blog/s161/online-excel-vba-training...
Lecture 6
Worksheets, Charts and Sheets
Play Video
Worksheets, Charts and Sheets
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Previous video - Selecting Cells (Range, Cells, Activecell, End, Offset)
http://www.youtube.com/watch?v=c8reU-H1PKQ

http://www.wiseowl.co.uk - In order to navigate a workbook using VBA it's essential to understand how to refer to, activate and select the various types of sheet. This video explains the difference between worksheet, chart and sheet objects and also demonstrates how to select, copy, move, delete and rename them. You'll also see how to change the visibility of the sheets in a workbook, including how to make sheets not just hidden, but VERY hidden!

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Integration Services, Visual Studio, ASP.NET, VB, C# and more!
Lecture 7
Workbooks
Play Video
Workbooks
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - This video explains the basics of working with Workbooks in Excel VBA. You'll lean several techniques for referring to workbooks, including the difference between ActiveWorkbook and ThisWorkbook. You'll also find out how to open existing workbooks and create new ones, including how to create a workbook based on a template. Finally, the video looks at various techniques for saving files, including how to provide a filename and a location, and how to change the file type.
Lecture 8
Variables
Play Video
Variables
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Variables are essential in all sorts of programming languages, and VBA is no exception. This video takes you through the basics of working with simple data-type variables and will explain the difference between non-declared and explicitly-declared variables, including why explicitly-declared variables are worth the effort! You'll see how to choose the best data type for your variables as well as learn about the consequences if you get it wrong. Finally you'll find out what the 'scope' of a variable means and how to set the scope appropriately for the subroutine that you're writing.
Lecture 9
Object Variables
Play Video
Object Variables
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Object variables in VBA allow you to store references to objects in memory. They're slightly more complex to use than basic data-type variables, but well worth the effort and this video explains why! You'll learn how to declare object variables and how to set references to existing objects. The video also shows you how to return references to objects using the methods of other objects with examples including generating new workbooks and worksheets, as well as using the Find method to reference cells.
Lecture 10
Message Boxes
Play Video
Message Boxes
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Message boxes in VBA provide a convenient way to display information to a user. This video shows you all you need to know to get started with showing messages, customising the message box and using it to ask users for input.
Lecture 11
Input Boxes
Play Video
Input Boxes
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - In VBA an Input Box is a convenient way to ask users for information. This video shows you how to display and customise a basic inputbox, and how to capture the result using cells or variables. You'll also see what happens if a user cancels from the inputbox and how to test if that happens. The final part of the video goes into more technical detail on returning inputbox results to variables with different data types.
Lecture 12
Application.InputBox
Play Video
Application.InputBox
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

You can download files here http://www.wiseowl.co.uk/videos/excelvba/application-inputbo...

http://www.wiseowl.co.uk - If you've used the generic InputBox function in VBA you may be frustrated by its limitations. This video shows you how to use the far superior Application.InputBox method. You'll learn how to specify the data type returned by the InputBox and how to use its built-in validation feature to control what users can enter. The video also covers how to return a simple formula and a more complex function from the InputBox and also how to allow users to select a range of cells which can then be referenced by your VBA code. The final part of the video covers a more complex example in which the InputBox returns an array of values which can be looped over and manipulated in various ways.
Lecture 13
With Statements
Play Video
With Statements
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - A With Statement in VBA is a great way to make your code a lot neater and a bit more efficient. This short video shows you how to use With statements in your code and explains why they can be so useful.
Lecture 14
If Statements
Play Video
If Statements
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - An IF Statement allows you to test whether a condition is met and then perform different actions based on the result. This video takes you from writing simple IF statements, through nested Ifs, ElseIfs and even how to combine logical tests using the And and Or operators.
Lecture 15
Select Case Statements
Play Video
Select Case Statements
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Select Case statements in VBA are similar to If statements in that they allow to test whether conditions are met and perform different actions based on the result. This video explains how to use Select Case statements in your code, including the various ways to phrase a logical test, how to separate numbers into bands, how to test for lists of values and how to create nested Select Case statements.
Lecture 16
Do Until and Do While Loops
Play Video
Do Until and Do While Loops
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Do Loops in VBA allow you to carry out a set of instructions repeatedly until some kind of condition is met. This video teaches you about the basics of the Do Loop statement including how to write Do Until and Do While loops, where to place your conditional statements and how to exit from a loop. The final part of the video provides a couple of longer examples using Do Loops to calculate new values for a list of data and then to split the list onto different worksheets.
Lecture 17
Find and FindNext
Play Video
Find and FindNext
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - You can use the Find and FindNext methods in Excel VBA to find values in a worksheet - exactly as the name suggests! This video explains how the methods work, including how to make the search case-sensitive, how to find full or partial matches and how to use Do While loops to find all the instances of something that you're searching for.

By Andrew Gould
Lecture 18
For Next Loops
Play Video
For Next Loops
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - You can use a For Next Loop to tell your program to repeat a set of instructions a number of times. This video teaches you how to construct a For Next statement, how to control how many times your loop will run, as well as which direction it will run in. The video also includes several practical examples of using a For Next Loop to process the items in a collection such as worksheets, workbooks and cells.
Lecture 19
For Each Loops
Play Video
For Each Loops
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - A For Each Loop is used to loop over a collection of objects and there are lots of good reasons for wanting to do that in Excel VBA! This video takes you from the basics of the For Each Next statement and using object variables through several examples of looping over worksheets, workbooks, chartobjects and cells and, finally, into writing nested loops for even more power.
Lecture 20
Creating Functions
Play Video
Creating Functions
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - A VBA function is a procedure that can return a value and this video teaches you how to create them. You'll learn how to declare a function and how to add parameters, including optional parameters and default values. You'll also see several ways to call the functions you've written, including from other VBA procedures and from an Excel worksheet. The video also talks about how to rewrite existing subroutines to replace repetitive code with functions.
Lecture 21
Error Handling (On Error, Resume, GoTo)
Play Video
Error Handling (On Error, Resume, GoTo)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Almost every procedure you write in VBA has the potential to go wrong at some point! Writing error handling code allows your procedures to fail gracefully instead of dumping the user out into the run time error dialog box. This video teaches you all about the On Error statement including how to ignore errors, how to trigger a custom error handler and how to resume running your code after an error has occurred. Towards the end of the video you'll also encounter the Err object which allows you to interrogate the error that has occurred and even raise your own custom errors.
Lecture 22
Event Procedures
Play Video
Event Procedures
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Writing Event Procedures allows you to create subroutines which run automatically in response to events in a workbook. This video teaches you how to access the events of an object and start writing event procedures for both workbooks and worksheets. You'll also see how to cancel certain events and how to disable events, as well as how to use the parameters of an event procedure.
Lecture 23
Application Events
Play Video
Application Events
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - In an earlier video in the series we've seen how to write event-handlers for workbooks and worksheets; this video teaches you how to access events of the Excel application itself! You'll learn the significance of the WithEvents keyword, a couple of the basic application events such as the new workbook event, and how to use a basic class module to keep your application event handlers neatly organised.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 24
User Forms
Play Video
User Forms
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Designing a User Form in VBA is a great way to allow you users to interact with your workbook. This video teaches you how to create forms, how to add controls and manipulate their properties and how to add code the events of the form to make it respond to user actions. You'll also get an insight into how much code goes into performing even very simple validation to control what your users can do with the form.
Lecture 25
Files and Folders (FileSystemObjects)
Play Video
Files and Folders (FileSystemObjects)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - The Scripting Runtime Object Library allows you to easily write code in an Excel VBA project which can manipulate the file and folder structure of your computer. It's and incredibly useful, although potentially quite dangerous thing to be able to do and this video will show you how. You'll learn how to reference the Scripting Runtime Library, what a FileSystemObject is and how to use it and how to perform various methods such as create folders, copy and move files and even how to delete them. Towards the end of the video you'll see how to loop over a collection of files in a single folder and then, as an encore, how you can loop through the complete set of folders and subfolders from a given starting point.
Lecture 26
Text Files (FileSystemObjects)
Play Video
Text Files (FileSystemObjects)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - The Scripting Runtime Object Library allows you to write VBA code to create, open, read from and write to text files using very simple methods. This video teaches you how to work with tab-delimited and comma-separated value files including how to create them, append data to them and how to read from them. The video also includes a section on using text functions for parsing the contents of a text file and the final section talks about combining these techniques with workbook events to create a simple change log.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Integration Services, Visual Studio, ASP.NET, VB, C# and more!
Lecture 27
File Dialogs
Play Video
File Dialogs
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Using FileDialogs in VBA provides a convenient method for selecting files and folders and performing useful actions on them. This video covers how to use the standard FileOpen and SaveAs dialogs, and how to perform their default actions. The video also covers the use of the FilePicker and FolderPicker dialogs and how to use FileSystemObjects to process a collection of selected files to perform actions such as copying files to another folder.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Integration Services, Visual Studio, ASP.NET, VB, C# and more!
Lecture 28
Arrays
Play Video
Arrays
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - An array is a lot like a variable, only with an array you can store more than one value under the same variable name. This video explains how to work with arrays in VBA, including how to declare basic, fixed-size arrays, populate and read from an array and how to detect the lower and upper bounds of an array. The second half of the video demonstrates more sophisticated arrays including dynamic arrays and multi-dimensional arrays, as well as covering some techniques for speeding up calculations by using arrays. You'll also see how to resize arrays dynamically, and how to transpose an array.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio, SQL Server, Reporting Services, Analysis Services, Integration Services, Visual Studio, ASP.NET, VB, C# and more!
Lecture 29
Constants and Enumerations (Const, Enum)
Play Video
Constants and Enumerations (Const, Enum)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Constants in VBA are values which don't change - there are lots of built-in examples and you can also create your own. This video teaches you how to declare and use constants in your VBA procedures. You'll also learn how to create collections of constants called Enumerations, how to reference them in your code and also how to use them as a new data type for variables and parameters of procedures.
Lecture 30
Creating Word Documents
Play Video
Creating Word Documents
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Writing VBA code in Excel doesn't only allow you to control Excel, you also have access to all of the other Office applications. This video looks at how to control Microsoft Word by writing code in Excel. You'll learn how to reference the Word Object Library, several methods for creating a new instance of the Word application, how to create new documents and how to copy data from Excel into Word. You'll also learn the importance of testing for which version of Word your users are running and how to write version-independent code that will work with (almost) any version of Word.
Lecture 31
Creating and Updating Linked Word Tables
Play Video
Creating and Updating Linked Word Tables
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Using Excel VBA to create a linked table in a Word document is pretty straightforward. Where things get tricky is when you move or rename the original Excel file. This video explains how to create the original linked table followed by how to update all of the links in a Word document by looping over its Fields collection. The end of the video also shows how to attach the code to the save events of the workbook so that you don't even have to remember to run it yourself!

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 32
Creating PowerPoint Presentations
Play Video
Creating PowerPoint Presentations
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Writing VBA code in Excel doesn't only allow you to control Excel, you also have access to all of the other Office applications. This video looks at how to control Microsoft PowerPoint by writing code in Excel. You'll learn how to reference the PowerPoint Object Library, several methods for creating a new instance of the PowerPoint application, how to create new presentations and insert slides and how to copy data from Excel into PowerPoint. You'll also learn the importance of testing for which version of PowerPoint your users are running and how to write version-independent code that will work with (almost) any version of PowerPoint.
Lecture 33
Creating Outlook Emails
Play Video
Creating Outlook Emails
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Writing VBA code in Excel doesn't only allow you to control Excel, you also have access to all of the other Office applications. This video looks at how to create emails in Microsoft Outlook by writing code in Excel. You'll learn how to reference the Outlook Object Library, several methods for creating a new instance of the Outlook application and how to create new emails. You'll see how to set basic properties of an email, such as the address and subject and also how to add attachments and make sure that the email includes a signature. The video spends a lot of time talking about the different email formats and how to set the body text of the email according to which format you've chosen; this includes how to write plain text emails, and how to construct basic HTML emails including how to format them. Towards the end the video also shows how to write emails using the Word Editor, allowing you to copy and paste information directly from Excel.
Lecture 34
ADO (ActiveX Data Objects) Querying a Database
Play Video
ADO (ActiveX Data Objects) Querying a Database
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Connecting to databases in VBA sounds like it should be complicated, but using the ActiveX Data Objects, or ADO, Object Library makes life an awful lot easier! This video teaches you how to reference the library and how to create a connection to almost any type of database by providing the correct connection string (this video covers Microsoft Access and Microsoft SQL Server). We'll also show you how to grab a set of data from a database using recordset objects and even how to write SQL queries in your VBA procedures. The final part of the video talks about how you can take user input to modify the query to allow you to create a simply querying tool in VBA.

You can download the Access Movies database here:
http://www.wiseowl.co.uk/files/execise-question-files/qf-110...

You can download the script to create the SQL Server Movies database used in this video at the following link:
http://www.wiseowl.co.uk/files/execise-question-files/qf-898...
Lecture 35
ADO (ActiveX Data Objects) Modifying Data with Recordsets
Play Video
ADO (ActiveX Data Objects) Modifying Data with Recordsets
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - We've previously looked at how to query a database using ActiveX Data Objects (ADO) to get data from a recordset into Excel. This video shows how you can do the reverse: take data from Excel and add it to an ADO recordset. We'll also look at how to modify existing records in a database as well as how to delete records. We'll cover all of these techniques by connecting Excel to both a Microsoft Access and a Microsoft SQL Server database to demonstrate how flexible ADO really is.

You can download the Access Movies database here:
http://www.wiseowl.co.uk/files/execise-question-files/qf-110...

You can download the script to create the SQL Server Movies database used in this video at the following link:
http://www.wiseowl.co.uk/files/execise-question-files/qf-898...
Lecture 36
ADO (ActiveX Data Objects) SQL Statements (INSERT, UPDATE, DELETE)
Play Video
ADO (ActiveX Data Objects) SQL Statements (INSERT, UPDATE, DELETE)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

You can download the files needed for the video here:
http://www.wiseowl.co.uk/videos/excelvba/ado-commands.htm

http://www.wiseowl.co.uk - The ADO Command object essentially allows you to write SQL statements directly into your VBA code and execute them on whichever database you've connected to - powerful stuff! This video focusses on the INSERT, UPDATE and DELETE statements to allow you to modify data based on information contained in your Excel workbooks. We'll cover how to build dynamic SQL statements and how to avoid the main danger of dynamic SQL. You'll also get an idea of how transactions work in databases, allowing you to revert your data back to a previous state if things go wrong with your code!
Lecture 37
Creating Add Ins
Play Video
Creating Add Ins
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Creating an Excel VBA Add-In is a great way to make your custom functions available to other Excel workbooks and VBA projects. This video teaches you how to create an add-in from scratch, including how to write VBA functions, where to save your add-in, how to enable an add-in to make it available to other workbooks and how to reference an add-in from a VBA project.

You can learn about writing Excel VBA functions in this video http://youtu.be/Bsfe-2VcvZg
Lecture 38
Type Declarations (UserDefined Types)
Play Video
Type Declarations (UserDefined Types)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - The Type statement in VBA allows you to define your own static data structures which you can then use in your variable declarations. This video explains how to declare a type, how to employ the type in variable declarations, how to read to and write from the variable, as well as a couple of fun features such as using enumerations within a type declaration and nesting types.
Lecture 39
Class Modules
Play Video
Class Modules
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Class Modules in VBA allow you to define your own classes of objects. This video explains why that's a useful and shows you how to go about creating a class. You'll learn about the two class events: initialise and terminate, you'll see how to define properties, including how to create read-only properties and set default values. You'll also find out how easy it is to create methods for your class and how to use your class in your regular code.

You can view a written version of our class modules tutorial at http://www.wiseowl.co.uk/blog/s237/classes.htm

If you're interested in learning how to create the Flappy Bird game in Excel you can follow a written tutorial here http://www.wiseowl.co.uk/blog/s398/flappy-bird-excel-vba-ind...
Lecture 40
Using Class Modules in Other Projects
Play Video
Using Class Modules in Other Projects
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - One small limitation of VBA Class Modules is that, by default, you can only use them in the VBA Project in which they were created. This video explains how you can make your class modules available to other VBA projects. You'll learn about the Instancing property of a class, how to set references to VBA projects, how to write a function to return a new instance of a class and how to create an Excel Add-In as a convenient way to distribute your class modules.
Lecture 41
Custom Collections
Play Video
Custom Collections
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Excel VBA is full of collections and you can even create your own custom collections using the Collection object. This video teaches you how to create a new custom collection, how to add items to it, how to reference those items individually and how to loop over all of the items in the collection. You'll also see how you can add items based on a custom class module to a collection.
Lecture 42
Typed Collections
Play Video
Typed Collections
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - One of the issues with the Collection class in VBA is that it's untyped. This means you can add any kind of object to the collection which can lead to issues later on. This video explains how to use class modules to create strongly typed collections. You'll learn how to create a private collection variable in a class module, how to create a custom Add method to restrict the type of item that can be added to the collection and how to use a custom collection class in your other procedures.
Lecture 43
Dictionaries
Play Video
Dictionaries
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - The Dictionary class is similar to the Collection class in that it allows you to store a custom collection of objects. This video teaches you how to use dictionaries, including how to reference the Scripting Runtime Object Library, how to create a new Dictionary and add, remove, count and reference the items in it. You'll also learn about the Items and Keys arrays contained within a Dictionary and how to loop over these arrays in order to process all of the items that the Dictionary contains.
Lecture 44
Disabling Screen Updates
Play Video
Disabling Screen Updates
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - You can give your VBA code a quick performance boost by simply disabling screen updates while your code runs. This video explains how to achieve that, as well as how to create a basic timer system to test the results.

You can download the example file for this video here:
http://www.wiseowl.co.uk/files/blogs/s2454/d5.zip

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 45
Saving as a PDF
Play Video
Saving as a PDF
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - This video explains how to use the ExportAsFixedFormat method to save various elements of an Excel file as a PDF document. You can apply this technique to the entire workbook, single worksheets and charts and even to a specific range of cells.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 46
Data Labels in Scatter Charts
Play Video
Data Labels in Scatter Charts
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Labelling data points in a scatter chart in Excel 2010 or earlier is much more limited than in later versions of Excel. This video explains how to use VBA to label the points with any value you like using simple looping techniques. You'll learn how to label the points in a single chart, multiple charts and multiple data series in a chart.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 47
ByRef and ByVal
Play Video
ByRef and ByVal
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - Passing values to other procedures is something you'll do commonly in VBA and the ByRef and ByVal keywords control exactly what happens to those values. This video explains the theory of passing information by reference and by value, as well as covering a couple of practical examples to demonstrate how to use the ByRef and ByVal keywords effectively.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 48
ByRef and ByVal (When to use Parentheses)
Play Video
ByRef and ByVal (When to use Parentheses)
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - When to use parentheses (round brackets) is one of the apparently annoyingly inconsistent features of VBA. There are some clearly defined rules which dictate when you should and shouldn't use parentheses and this video attempts to explain them!

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 49
Making Excel Talk
Play Video
Making Excel Talk
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - One of Excel's more unusual features is its ability to speak to you! Not intelligently, of course, but you can make Excel vocalise a specific phrase or the contents of cells, or any other expression you can think of. This video shows you how to access the speech feature in Excel and use it to create a couple of small but fun examples.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 50
Finding the Last Used Row, Column and Cell
Play Video
Finding the Last Used Row, Column and Cell
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - There are several techniques you can use in VBA to find the last row, column or cell in a worksheet. This video shows you a range of options including using the End and Offset properties; the CurrentRegion property of a Range and the UsedRange property of a Worksheet; the LastCell option of the SpecialCells method and, finally, the trusty Find method.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 51
Querying Web Pages using Query Tables
Play Video
Querying Web Pages using Query Tables
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould
Download files here http://www.wiseowl.co.uk/videos/excelvba/web-query-tables.ht...

http://www.wiseowl.co.uk - Querying web pages in Excel VBA is simple when you use Query Tables. This video shows you how to create a query table linked to a web page, how to choose which tables from the web page to return, and how to pass values to a URL query string to create dynamic, updatable web queries in your Excel workbooks.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 52
Browsing to Websites and Scraping a Web Page
Play Video
Browsing to Websites and Scraping a Web Page
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould
Download files here http://www.wiseowl.co.uk/videos/excelvba/scraping-web-pages....

http://www.wiseowl.co.uk - Scraping a web page in VBA involves getting a reference to an HTML document and then processing its elements to extract the useful parts and write them to another location in a readable format. This video shows you how to do that using both Internet Explorer and basic XML HTTP requests. You'll learn a bit about the Document Object Model, as well as how to identify HTML elements by name and by tag type. You'll also see how to loop through various collections of HTML elements and their children using an example involving exchange rate tables.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 53
Web Scraping Across Multiple Pages
Play Video
Web Scraping Across Multiple Pages
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould
Download example files here http://www.wiseowl.co.uk/videos/excelvba/scraping-multiple-w...

http://www.wiseowl.co.uk - What's better than scraping one web page? Scraping lots of them with the same procedure, of couse! This video explains how to loop over multiple pages using Microsoft's HTML and XML object libraries. You'll learn about HTML tags and classes, the Document Object Model and how to loop over elements on a page.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 54
Downloading Files from Websites
Play Video
Downloading Files from Websites
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould
Download files here http://www.wiseowl.co.uk/videos/excelvba/downloading-files.h...

Excel VBA doesn't have a native method for downloading files from websites but you can declare an API function that will enable you to do this. This video takes you through the process of declaring the API function and using it in your code, along with a bunch of other useful techniques such as using folder pickers, creating folders with FileSystemObjects and opening a Windows Explorer window using the Shell function.
Lecture 55
Introduction to Charts in VBA
Play Video
Introduction to Charts in VBA
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould
Download the sample data file here http://www.wiseowl.co.uk/videos/excelvba/introduction-to-cha...

http://www.wiseowl.co.uk - This video helps you to get started with charts in Excel VBA. You'll learn how to create a new chart sheet and set the chart's data source using a variety of techniques, including how to dynamically set the data source based on conditions. You'll also see lots of ways to change the appearance of the chart, including using chart layouts, chart colour schemes, chart styles and even how to create your own custom chart template.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 56
Embedding Charts in Worksheets
Play Video
Embedding Charts in Worksheets
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould
Download files here http://www.wiseowl.co.uk/videos/excelvba/embedding-charts.ht...

ChartObjects are charts that are embedded in a sheet in an Excel workbook. This video teaches you about the basic differences between charts and chartobjects, as well as how to change one type into another. You'll also learn a couple of neat tricks for positioning chartobjects on a sheet, including how to arrange multiple chartobjects so that they don't overlap.
Lecture 57
Introduction to Pivot Tables in VBA
Play Video
Introduction to Pivot Tables in VBA
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould
Download files here http://www.wiseowl.co.uk/videos/excelvba/pivot-tables-introd...

http://www.wiseowl.co.uk - A pivot table is a fantastic tool for summarising a large quantity of data. This video shows you the basics of creating pivot tables using VBA, starting with the creation of a pivot cache. You'll also see how to create new pivot tables, how to manipulate pivot fields and pivot items and how to apply filters to the pivot table.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 58
Pivot Tables using an Access Database
Play Video
Pivot Tables using an Access Database
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download the Excel workbook here http://www.wiseowl.co.uk/videos/excelvba/pivot-tables-introd...
Download the Access database here http://www.wiseowl.co.uk/training/exercises/ex-2720.htm

This video explains how to create a pivot table based on an Access database. You'll learn how to create connections to external data sources, how to edit connection strings and how to create a pivot cache using the external connection. The video also shows you how to create queries in Microsoft Access, along with how to write basic SQL statements in your VBA code to create flexible queries that you can use to create pivot tables.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 59
Pivot Tables using a SQL Server Database
Play Video
Pivot Tables using a SQL Server Database
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download Excel workbook here http://www.wiseowl.co.uk/videos/excelvba/pivot-tables-introd...
Download SQL script here http://www.wiseowl.co.uk/training/exercises/ex-2363.htm

This video explains how to create an Excel pivot table based on a SQL Server database. You'll learn how to create connections to external data sources, how to edit connection strings and how to create a pivot cache using the external connection. The video also shows you how to create views in SQL Server Management Studio, along with how to write basic SQL statements in your VBA code to create flexible queries that you can use to create pivot tables.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 60
Pivot Tables and Consolidation Ranges
Play Video
Pivot Tables and Consolidation Ranges
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - This video explains how to create an Excel pivot table based on multiple ranges of data spread across several worksheets. You'll learn how to use the Pivot Table Wizard to create a pivot table based on multiple consolidation ranges, as well as how to write code to achieve the same results. The video explains how to use an array to hold references to the different ranges of cells involved and how to populat this array dynamically for all of the data sheets in a workbook.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 61
PowerPivot Data Models
Play Video
PowerPivot Data Models
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download the Excel workbook here http://www.wiseowl.co.uk/videos/excelvba/powerpivot-data-mod...
Download the Access database here http://www.wiseowl.co.uk/training/exercises/ex-2720.htm
Download SQL script here http://www.wiseowl.co.uk/training/exercises/ex-2363.htm

The PowerPivot Data Model allows you to combine data from a variety of sources into one single object in a workbook - you can then use this model to create pivot tables using the joined data. This video explains how to create and edit the PowerPivot data model using dat from Excel worksheets, Access databases and SQL Server databases. You'll learn how to create a data model, how to add and remove tables and how to import or create relationships between the different tables in the model. The final part of the video explains how to create a single data model which combines data from Excel, Access and SQL Server.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 62
Pivot Charts
Play Video
Pivot Charts
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download the Excel workbook here http://www.wiseowl.co.uk/videos/excelvba/pivot-tables-introd...

This video explains how to create and manipulate pivot charts in Excel VBA. You'll learn how to create a pivot chart based on a pivot table, how to create embedded chart objects and separate chart sheets and how to modify the fields of the pivot chart. The video also shows you how to apply and remove filters on the chart and how to quickly format the chart and chage its appearance.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 63
Pivot Table Slicers
Play Video
Pivot Table Slicers
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download the Excel workbook here http://www.wiseowl.co.uk/videos/excelvba/pivot-tables-introd...

Slicers provide an interesting way to apply filters to pivot tables. This video explains how to write VBA code to create a slicer cache and a slicer, along with how to change the slicer's position and dimensions. You'll learn how to use a slicer to filter a pivot table and how to connect a slicer to multiple pivot tables to enable filtering of multiple pivots with a single slicer.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 64
Pivot Table Date Fields and Timelines
Play Video
Pivot Table Date Fields and Timelines
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download the Excel workbook here http://www.wiseowl.co.uk/videos/excelvba/pivot-tables-introd...

You can do several useful things with dates in Pivot Tables, including grouping, filtering and applying timelines. This video shows you how to do all those things using VBA.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 65
Introduction to Shapes
Play Video
Introduction to Shapes
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

You can download the file created in this video from this page http://www.wiseowl.co.uk/videos/excelvba/intro-to-shapes.htm

By Andrew Gould

http://www.wiseowl.co.uk - In this video you'll learn the basic of working with shapes in VBA. You'll see how to reference objects in the shapes collection using a variety of techniques. You'll learn about the properties which determine a shape's size and position and how to set these relative to other objects. You'll also learn how to add a variety of shapes to a worksheet, including basic autoshapes, pictures and form control buttons, as well as how to attach an action to a shape so that it runs a macro when a user clicks it.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 66
Formatting Shapes
Play Video
Formatting Shapes
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

http://www.wiseowl.co.uk - The previous video in the series explained how to draw shapes on a worksheet - this one explains how to make them look more interesting by applying a variety of formatting effects to them.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 67
Adding Text to Shapes
Play Video
Adding Text to Shapes
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

You can download the example here http://www.wiseowl.co.uk/videos/excelvba/add-text-to-shapes....

http://www.wiseowl.co.uk - You can add text to most shapes that you can draw in Excel. This video explains the basics of working with text in shapes, including how to add to text to a basic shape, how to format text inside shapes and how to change the text alignment and orientation.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 68
Working with Strings
Play Video
Working with Strings
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

You can download the sample data used in this video here http://www.wiseowl.co.uk/videos/excelvba/working-with-string...

http://www.wiseowl.co.uk - This video explains the basics of working with text, or strings, in VBA. You'll learn about the String data type, how to use the $ type-declaration character and how to create variable and fixed-length strings. You'll see how to concatenate multiple values into a single string, including some string constants for special characters such as tab spaces and new lines - ever wondered why there are so many ways to create a new line in VBA? This video explains why! The final part of the video talks about comparing strings, including how to deal with case-sensitive comparisons and how to use wildcard characters to match paterns of text.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 69
Splitting Strings
Play Video
Splitting Strings
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download files here http://www.wiseowl.co.uk/videos/excelvba/splitting-strings.h...

http://www.wiseowl.co.uk - This video explains how to use VBA functions to split strings into their constituent parts. You'll learn about the Left, Right and Mid functions for extracting parts of a string, how to identify the position of one string within another using the InStr and InstrRev functions and how to use the Split function to break a single string into multiple parts and store them in an array.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 70
The Join Function and Concatenating Strings
Play Video
The Join Function and Concatenating Strings
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download files here http://www.wiseowl.co.uk/videos/excelvba/join-and-concatenat...

http://www.wiseowl.co.uk - This video explains how to join or concatenate strings in VBA. You'll learn about to different operators you can use to do this, and why one is better than the other. You'll also learn how to use the Join function to take the contents of an array and concatenate them into a single string. The final part of the video shows a couple of practical uses for these techniques with an example that writes data to text files.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 71
Replacing Strings
Play Video
Replacing Strings
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download files here http://www.wiseowl.co.uk/videos/excelvba/replacing-strings.h...

http://www.wiseowl.co.uk - In this video you'll learn how to use the Replace function to substitute characters within a string. As a practical example, we'll look at how to generate file names from cell contents and replace the illegal characters.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 72
Converting the Case of Text
Play Video
Converting the Case of Text
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download files here http://www.wiseowl.co.uk/videos/excelvba/converting-case.htm

http://www.wiseowl.co.uk - This video explains how to change the case of text using VBA. You'll see how to make string comparisons case insensitive using the Option Compare statement as well as the UCase, LCase and StrComp functions. You'll learn how to convert strings to Proper Case or Title Case. The video also explains how to create custom functions for converting text into Sentence Case and to toggle the case of individual characters in a string.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 73
The Asc and Chr Functions
Play Video
The Asc and Chr Functions
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download files here http://www.wiseowl.co.uk/videos/excelvba/asc-chr-functions.h...

http://www.wiseowl.co.uk - This video explains how to use the Asc and Chr functions in VBA.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 74
The IIf Function
Play Video
The IIf Function
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download files here http://www.wiseowl.co.uk/videos/excelvba/iif-function.htm

http://www.wiseowl.co.uk - The IIF function in VBA is, apart from the spelling, exactly like the If function used in Excel worksheets. This video provides a quick overview of how the function works, comparing it with the If statement and creating a practical example to separate a list of data into different worksheets.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 75
The Switch Function
Play Video
The Switch Function
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

Download files here http://www.wiseowl.co.uk/videos/excelvba/switch-function.htm

http://www.wiseowl.co.uk - The Switch function allows you to perform multiple logical tests in a single, succinct expression in VBA. For simple conditions, you can use Switch as an elegant replacement for long-winded Select Case statements. This video shows how the Switch function works, inluding how to use it to create a user-defined function to help separate a list of data into different sheets.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Lecture 76
Working with Dates
Play Video
Working with Dates
If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos, you can click this link http://www.wiseowl.co.uk/donate?t=1 to make a donation. Thanks for watching!

By Andrew Gould

You can download the example code from this page http://www.wiseowl.co.uk/videos/excelvba/dates.htm

http://www.wiseowl.co.uk - This video explains the basics of working with dates in VBA. You'll learn about the Date data type and how to write unambiguous dates in your VBA code. You'll also learn about Excel's leap year bug and why some VBA dates don't match those in Excel. You'll see how to return the current date and time, as well as various techniques to format dates. The final part of the video describes a variety of date calculations and functions including the difference between DateDiff and DateDif, calculating working days using NetWorkDays and how to calculate age in years accurately.

Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more