Understand the relationship between
macros and VBA
Record a macro with absolute
references
Understand macro security
Implement macro security
Record a macro with relative
references
Use shape controls to run macros
Run a macro from a button control
This session pushes Macros to the max
without writing a single line of
VBA. Users are often unaware
of how powerful recorded macros can be if they are implemented
in the right way. We use this session to explore the use
of recorded macros to add some sophisticated features to Excel
2007 workbooks.
Here are some of the workbooks you'll be
working with:
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.
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.
