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.
