Session 1 - The Fundamentals

Session Objectives:

• Record and run a macro
• Run a macro from a toolbar button
• Edit a macro's VBA code
• Generate VBA code in real time
  

This session eases you into VBA by using the macro recorder to create a custom toolbar that will quickly apply styles to different spreadsheet text :-

Along the way you'll learn the basics of how VBA works and actually delve into, and debug, the code that the macro recorder has generated. Finally we will show you the very useful technique of real-time VBA code generation.

Session 2 - The Excel Object Model

Session Objectives:

• Understand object properties
• Understand object methods
• Understand object events
• Understand the Excel object model

Knowing your way around the Excel object model is, of course, fundamental to being able to write Excel VBA code. Many courses will attempt to teach you the object model without actually taking one step back and explaining what OOP (Object Orientated Programming) is all about. In this session we introduce OOP concepts in a simple, down to earth (and maybe even fun) way.

At the end of the session you'll have a good grounding in what objects are, how they work, and most importantly how you can write VBA code that manipulates them. You won't learn this by listening to a lecture - you'll do it by hands-on coding to fully appreciate the concepts you'll learn.

Session 3- An Introduction To VBA

Session objectives:

• Understand procedures and sub-procedures
• Understand variables
• Understand data types
• Understand arguments
• Understand functions
• Learn how to access the Excel object model from VBA code
• Use the VBA help system
• Use the Object Browser

This session will get you up to speed with Visual Basic concepts and syntax in no time at all. Once again our hands-on teaching method, with as little lecture-style instruction as possible, will walk you through all you need to know by coding simple little applications to appreciate how each program feature works. Here's an example we use to demonstrate calling functions with arguments:

The session ends by showing you how to access VBA's extensive help system - you'll be amazed at the extent of information you'll have at your fingertips once you know the secret of how to find it all (and it is far from obvious!).

Session 4 - Working With Excel's Range Object

Session Objectives:

• Obtain a reference to a Workbook object
• Obtain a reference to a Worksheet object
• Obtain a reference to a single worksheet cell
• Understand state
• Understand the Range property
• Record a macro with absolute cell references
• Record a macro with relative cell references
• Use named ranges
• Use the Range object's Cells property
• Expand your understanding of the Excel object model
  

The quirky and often misunderstood Range object is the "key to the kingdom" when writing Excel applications. We devote a whole session to the Range object and completely explain its inner workings. We'll program many examples and teach you all of the different ways of using this object to read from, and write to, worksheet cells. Once again, all of this is taught "hands-on" with lecture-style instruction kept to an absolute minimum. Here's an example of some simple code you'll write to count the cells contained in a range object :-

Session 5 - Working With Chart and Pivot Table Objects

Session Objectives

• Create a chart using VBA code
• Add user interaction with command buttons
• Modify a chart using VBA code
• Expand your understanding of the Excel object model

In this session we begin to do really useful things with all of the theory amassed in sessions 1-4. We create an Excel application that will automaticaly create a chart and pivot table at the click of a button and then allow the user to toggle between pie and bar charts... things that would all be impossible without using hand-crafted VBA code.

Session 6 - Adding Custom Functions to Excel

Session Objectives:

• Write a custom Excel function using Visual Basic
• Return Excel compatible errors from a custom function
• Make a custom function user friendly by adding help text

This session really leverages upon all of your learning so far to build a custom function in Visual Basic that your Excel users will be able to use in exactly the same way as the hundreds of built-in Excel functions such as SUM().

Session 7 - Building Bulletproof Utilities

Session Objectives:

• Use a spin button control to limit user input
• Use a combo box control to limit user input
• Protect a worksheet to prevent your users making unwanted changes

In this session we build a handy Excel utility from scratch. The utility allows your users to calculate the loan repayments by entering data via combo and spin box controls embedded into a worksheet.

Session 8 - Creating User Forms

Session Objectives:

• Create a form-based user interface
• Enhance a form's user interface for keyboard input
• Initialise a combo box control with data
• Implement a form-based spin button control
• Utilise an Excel function within Visual Basic code
• Paste form results into a spreadsheet
• Understand modal and modeless forms

User Forms are a little understood feature of Excel since they were introduced in Excel 97 rendering the older dialogue forms obsolete. Few people are even aware that Excel's user forms feature even exists! In this session we re-model our mortgage calculation utility as an Excel form.

Session 9 - Creating an Executive Information System

Session Objectives:

• Define an ODBC data source
• Use an ODBC data source to query a database
• Create a graphical user interface
• Create a custom menu bar
• Hide and show toolbars
• Protect an application
• Add a pacifier page to an Excel application

This session brings together all of the skills learned in sessions 1-8 and adds a few new tricks and twists to created a full-blown professional and robust Excel application.

The application begins by querying a database to retrieve bang up-to-date management information and displays a "pacifier" screen while the query is executing.

The executive is then presented with a screen with two icons. They offer different views into the data and display a Pivot Chart in each case to graphically represent sales by product and category.

The skills learned in this session will put you in fine shape to develop and design Excel applications of all types.

Summary

The Smart Method's Excel VBA course is quite remarkable. The ambitious remit: to teach both Visual Basic and the Excel Object model in just eight hours, would be unrealistic and unachievable using other less advanced teaching methods.

Our Excel VBA course is available throughout the UK, Europe and the World.

If you have any other questions about this course feel free to Email or Telephone at any time.