VIDEOS BY TOPIC
EXCEL VBA VIDEOS
- Excel VBA Part 1 - The VB Editor
- Excel VBA Part 2 - Writing Your First Macro
- Excel VBA Part 3 - What To Do When Things Go Wrong
- Excel VBA Part 4 - Buttons, Toolbars and Keyboard Shortcuts
- Excel VBA Part 5 - Selecting Cells
- Excel VBA Part 6 - Worksheets, Charts and Sheets
- Excel VBA Part 7 - Working with Workbooks
- Excel VBA Part 8 - Variables in VBA
- Excel VBA Part 9 - Object Variables
- Excel VBA Part 10 - Message Boxes
- Excel VBA Part 11 - Input Boxes
- Excel VBA Part 11a - Application.InputBox
- Excel VBA Part 12 - With Statements
- Excel VBA Part 13.1 - If Statements in VBA
- Excel VBA Part 54.1 - The Iif Function
- Excel VBA Part 14.1- Select Case Statements
- Excel VBA Part 54.2 - The Switch Function
- Excel VBA Part 15.1 - Do Until and Do While Loops
- Excel VBA Part 15.2 - Find and FindNext
- Excel VBA Part 16 - For Next Loops
- Excel VBA Part 17 - For Each Loops
- Excel VBA Part 18 - Creating Functions
- Excel VBA Part 19 - Error Handling
- Excel VBA Part 20 - Event Procedures
- Excel VBA Part 20a - Application Events
- Excel VBA Part 21 - User Forms
- Excel VBA Part 22 - Files and Folders
- Excel VBA Part 23 - Text Files
- Excel VBA Part 24 - File Dialogs
- Excel VBA Part 25 - Arrays
- Excel VBA Part 26 - Constants and Enumerations
- Excel VBA Part 27.1 - Creating Word Documents
- Excel VBA Part 27.2 - Creating and Updating Linked Word Tables
- Excel VBA Part 28 - Creating PowerPoint Presentations
- Excel VBA Part 29 - Creating Outlook Emails
- Excel VBA Part 30 - Querying a Database with ADO
- Excel VBA Part 31 - Modifying Data with ADO Recordsets
- Excel VBA Part 32 - Executing SQL Commands with ADO
- Excel VBA Part 33 - Creating Add Ins
- Excel VBA Part 34 - Type Declarations
- Excel VBA Part 35 - Class Modules
- Excel VBA Part 36 - Class Modules in Other Projects
- Excel VBA Part 37 - Custom Collections
- Excel VBA Part 38 - Typed Collections
- Excel VBA Part 39 - Dictionaries
- Excel VBA - Disable Screen Updates
- Excel VBA - Save as PDF
- Excel VBA - Data Labels in Scatter Charts
- Excel VBA Part 43.1 - ByRef and ByVal
- Excel VBA Part 43.2 - ByRef and ByVal (When to use Parentheses)
- Excel VBA Part 44 - Making Excel Talk
- Excel VBA Part 45 - Finding the Last Used Row, Column and Cell
- Excel VBA Part 46 - Querying Web Pages with Query Tables
- Excel VBA Part 47 - Browsing to Websites and Scraping Web Page
- Excel VBA Part 48 - Scraping Multiple Web Pages
- Excel VBA Part 49 - Downloading Files from Websites
- Excel VBA Part 50.1 - Introduction to Charts in VBA
- Excel VBA Part 50.2 - Embedding Charts in Worksheets
- Excel VBA Part 51.1 - Introduction to Pivot Tables in VBA
- Excel VBA Part 51.2 - Pivot Tables using an Access Database
- Excel VBA Part 51.3 - Pivot Tables using a SQL Server Database
- Excel VBA Part 51.4 - Pivot Tables and Consolidation Ranges
- Excel VBA Part 51.5 - PowerPivot Data Models
- Excel VBA Part 51.6 - Pivot Charts
- Excel VBA Part 51.7 - Pivot Table Slicers
- Excel VBA Part 51.8 - Pivot Table Date Fields and Timelines
- Excel VBA Part 52.1 - Introduction to Shapes
- Excel VBA Part 52.2 - Formatting Shapes
- Excel VBA Part 52.3 - Adding Text to Shapes
- Excel VBA Part 53.1 - Working with Strings
- Excel VBA Part 53.2 - Splitting Strings
- Excel VBA Part 53.3 - The Join Function and Concatenating Strings
- Excel VBA Part 53.4 - Replacing Strings
- Excel VBA Part 53.5 - Converting the Case of Text
- Excel VBA Part 53.6 - The Asc and Chr Functions
VIDEOS BY AUTHOR
VIDEOS BY YEAR
Wise Owl - Excel VBA training videos (page 1 of 4)
Showing videos 1-20 (out of 75)
Posted by Andrew Gould on 10 May 2013
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!
Posted by Andrew Gould on 10 May 2013
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.
Posted by Andrew Gould on 10 May 2013
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!
Posted by Andrew Gould on 10 May 2013
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!
Posted by Andrew Gould on 23 May 2013
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.
Posted by Andrew Gould on 09 November 2013
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!
Posted by Andrew Gould on 17 January 2014
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.
Posted by Andrew Gould on 21 January 2014
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.
Posted by Andrew Gould on 22 January 2014
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.
Posted by Andrew Gould on 23 January 2014
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.
Posted by Andrew Gould on 27 January 2014
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.
Posted by Andrew Gould on 30 January 2014
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.
Posted by Andrew Gould on 31 January 2014
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.
Posted by Andrew Gould on 03 February 2014
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.
Posted by Andrew Gould on 06 February 2014
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.
Posted by Andrew Gould on 07 February 2014
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.
Posted by Andrew Gould on 17 February 2014
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.
Posted by Andrew Gould on 10 February 2014
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.
Posted by Andrew Gould on 24 February 2014
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.
Posted by Andrew Gould on 28 February 2014
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.
First of all I would like to congratulate you for this incredible series of videos about VBA.
Through you, I have been able to improve and learn more and more my knowledge in Excel and VBA.
I would like to know if you are going to, or intend to release, any video about Regular Expressions (RegExp) for Excel / VBA?
It is a very interesting and very powerful subject for queries, validations, ect. But little used or demonstrated by the communities.
Again, thank you for the dedication and high quality of your teaching.
A hug from Brazil!
That's the second question on Regular Expressions I've had this week! The topic has been on my list for some time, I simply haven't had time to plan and create a video on the subject yet. Perhaps I should move this topic a little further up the list and aim to create something in the near future.
Thank you for the kind comments and for watching!
I'm developing an Excel VBA program, but I'm having a problem. I need to download a Captcha image from this site.
I have a workbook with 4 sheets. Three of these sheets have employees that are scheduled daily, the fourth is the actual schedule. What I would like to accomplish is when I schedule that person he or she is highlighted on the other sheets to help identify who we can and cannot use. I am fairly new to VBA, but extremely excited to learn something new. Thank you so much for you time and any assistance woould be greatly appreciated.
The question is a bit vague, and I suspect this answer won't help much. What I'd do is follow through the videos or blogs on our website for now.
However, here's a macro to colour in all the cells in range A1:A3 which contain the name Bill. You could obviously modify this to work with any range and any name.
Dim PersonCell As Range
Dim PersonRange As Range
'set a variable to refer to the range where people can be found
Set PersonRange = Range("A1:A3")
'colour in all the people with the right name
For Each PersonCell In PersonRange.Cells
If PersonCell.Value = "Bob" Then
PersonCell.Interior.Color = vbGreen
Exactly when and how you'd call this macro would depend on exactly what you were trying to achieve.