Excel VBA training courses: classroom and online
As well as providing introductory and advanced VBA training, we've also published this online training course teaching how to code macros using Visual Basic for Applications (VBA) within Excel. The tutorial covers everything from basic recording through to creating classes - something for everyone!

Posted by Andy Brown on 16 August 2011 | 5 comments

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Excel Visual Basic Course - VBA Macros Training

This free tutorial provides an on-line training course in how to write Visual Basic for Applications macros within Excel.  (Note that we also run classroom-based training courses in VBA and also in Excel in London and other UK cities).

Getting Started

A good place to start when learning VBA for Excel is to learn how to record macros, and then start writing basic code yourself:

Tutorial Learn how to ...
Recording macros in Excel VBA Record macros in Excel (and also various techniques for playing them back and for modifying the code generated by Excel).
Writing Visual Basic macros in Excel Start writing VBA code within Excel to automate your workbooks
Commenting code The art and science of writing comments for your VBA code.
Short-cut keys How to assign a short-cut key to run any macro.

Selecting Cells

You can't do much in Excel VBA without knowing how to move around a worksheet!

Tutorial Learn how to ...
Selecting cells - absolute Go to a cell or range in Excel.
Selecting cells - relative Select cells and ranges relative to your starting position.

Talking to Users

The next thing to learn is how to display input and message boxes (as part of this module you'll also learn about variables):

Tutorial Learn how to ...
Variables Use variables and constants in your code
Message boxes Use the MsgBox command to display pop-up messages.
Input boxes Use the InputBox function to get data from your user.

Visual Basic Terms

The next thing to understand is how VBA is written, using objects, collections, properties and methods:

Tutorial What it's about
The Excel Object Model VBA is what's called an object-orientated programming language - learn what this means!

Loops and Logic

Any programming language allows you to test conditions and do things iteratively (in loops), and Visual Basic for Applications is no different:

Tutorial Learn how to ...
Testing conditions Use IF statements and SELECT ... CASE to test conditions.
Looping Learn the four main types of loop built into VBA.

Event-Handling

In Excel VBA you can attach code to events which happen for a workbook or worksheet (such as running code whenever you open a particular file):

Tutorial Learn how to ...
Event-handling macros Attach code to events for Excel workbooks and worksheets.
Event sinks Create event sinks to capture application-level events (such as a user creating a new workbook). 

Debugging and Error-Handling

Sometimes your programs will have bugs in (!) - these tutorials show how to minimise the chances of these occurring, and track them down when they do:

Tutorial Learn how to ...
Debugging in VBA Stepping through code, setting breakpoints, using the call stack and other options for debugging macros.
Error-handling Using the Err object to display errors, and trapping for errors using ON ERROR and RESUME.

Referencing Applications

You can link to other applications such as Word or PowerPoint from within Excel,:

Tutorial Learn how to ...
Linking to Word, etc Write code using references to manipulate documents in Word, presentations in PowerPoint, etc.
CreateObject and GetObject An alternative way to create and use references (of interest mostly just for techies!).
Talking to Internet Explorer You can get VBA programs to fill in forms in Internet Explorer, if you reference the right object library.
Programming references How to write code to create references programmatically (not for the faint-hearted).

Working with Files and Folders

You can use FileDialogs to choose files to open or save, TextStream objects to read and write files and create a FileSystemObject to copy, move or delete files:

Tutorial Learn how to ...
FileDialogs Use FileDialogs to make it easier for users to choose files.
Reading and writing files Read from a file line-by-line (or write to one) using a TextStream.
Manipulating files and folders Create a reference to the Microsoft Scripting Runtime object library to get access to all of the files on your hard disk!

Working with Databases

How to use ActiveX Data Objects (ADO) to link to SQL Server or Access databases from within Excel (or do it the other way round, and link from Access to Excel, say):

Tutorial Learn how to ...
Working with records List records, add records or edit or delete records in a table in Access, SQL Server, etc.

Advanced Programming Concepts

When you're familiar with basic programming, it's time to learn advanced concepts, such as passing arguments and using recursion:

Tutorial Learn how to ...
Passing arguments Pass arguments to subroutines (using ByRef and ByVal) and understand optional arguments and ParamArray.
Recursion What recursive programming is, and how to use it to list files or to list out hierarchical data structures.

Functions

In addition to the built-in Excel functions, you can write your own, and you can also incorporate functions within your code to make it easier to write and read:

Tutorial Learn how to ...
Creating functions Write your own functions, and then call them from within your code or incorporate them into Excel.
Sharing functions using add-ins Create add-ins to share code that you've written between different workbooks.

Additional Data Structures

Variables aren't the only way to hold data in VBA programs!

Tutorial Learn how to ...
Arrays Learn how to create static and dynamic arrays, and how to use multi-dimensional arrays.
Enumerations Learn how to write strongly-typed code with enumerations (and indeed what strongly-typed coding means!).

User forms

Create your own dialog boxes, by drawing user forms on screen and integrating them with your macros:

Tutorial Learn how to ...
Drawing user forms Use the toolbox and properties window to draw user forms on screen.
User forms and macros Integrate macros and user forms (including validating data entered using code).
Advanced controls Learn how to create advanced controls such as combo boxes, list boxes, option buttons, MultiPages and SpinButtons.

Classes

Classes within Excel VBA are your own customised objects, to which you breathe life by writing methods and properties:

Tutorial What it covers
Creating classes An explanation of what classes are, and how to create and use class modules in VBA.
Collections Creating your own collections within Visual Basic (both typed and untyped).

Security

Learn how to distinguish between macros you trust and macros you don't: digital certificates, trust centres and more!

Tutorial What it covers
Macro security How macro security and digital certificates work in Excel VBA.
Deleting certificates How to delete digital certificates from your computer.

 

One final thought - Wise Owl also run classroom-based Excel and VBA training courses in the UK (and overseas, if you're willing to pay expenses!).

This blog has 5 comments

Comment added on 12 April 2012 at 19:17 GMT
This tutorial is straight forward, simple and easy to understand, and humorous too! Thank you to Wise Owl and the author for putting this up. I very appreciate it.
Comment added on 15 October 2012 at 15:12 GMT
Andy,

I have never come across such a lucid reference for VBA, that too arranged step by step. I frequently go back to walkenbach's book, however the size & details of the book scares me.

Thanks a ton and keep up the good work. And please keep this updated.
Comment added on 18 October 2012 at 03:29 GMT
Thank you!

I'm quite grateful that you went to the trouble of putting this Excel VBA tutorial together.  The examples are short and sweet.  

I have very limited programming experience.

There's only so much I can get done efficiently using Excel's standard tools and functions, and I now find myself at the edge of this, staring off into the Lake of Everything Else That One Can Get Done If One Knows Some VBA.  

Thanks to your help, I'm now dipping my toes in this lake.

Great stuff.
Reply from Andy Brown
Careful - the lake is infested with sharks, and isn't as deep as it seems at first glance ...
Comment added on 25 October 2012 at 08:56 GMT
Andy, this is an extremely well written VBA and Macros introduction course. Logical steps, digestable chunks, understandable language (no technical mumbo jumbo). This was and still is a great help for me. Thanks!
Comment added on 26 September 2013 at 23:38 GMT
Excellent tutorial really very well done. I am a building contractor who learned in a few hours to design a form and program it to do just about everything I would like. A few things I still need help with but what a great starting point you have here. You just saved my PC from a very large sledgehammer. Thank you.

A full-blown discussion forum is being built for this site, which will allow you once more to add comments and discussion threads.