Microsoft Excel VBA |  |  |
Two Day Course
Pre Requisites
A basic understanding of Excel is required. No previous knowledge of VBA or of any other programming language is required.
Objectives
This training course is designed to go beyond the macro recorder to enable you to write Excel macros using the Visual Basic for Applications language. The course is an excellent introduction for anyone who requires an understanding of VBA and how it is implemented in Excel.
Versions Available
97, 2000, XP, 2003
Further Learning
Course Content
- The Basics of VBA
- Data storage in variables
- Subroutine Calls and passing values
- Handling text values
- Control Structures:
- Decision making:
- If-Then-Else
- Case Statements
- Looping: Conditional Loops, Counter Loops and Collection Loops
- Understanding the Excel Object Model
- Review of theory: Objects, Methods and Properties
- Collections and Containers
- Square brackets
- Object references- Cells, Sheets and Workbooks
- With…End With
- Recording and Editing
- Recording a macro
- Relative vs Absolute recordings
- Personal macro workbook
- Editing and optimising recorded code
- Removing Selection statements
- Further VBA
- The role of Option Explicit
- Variable Declaration
- Data Types
- Variable Scope and Lifetime
- Public and Private
- When to use Set
- Use of Constants
- Naming Conventions
- Common Tasks in Excel Macros
- Copying and Printing
- Measuring areas and lists
- Locating data on a worksheet
- Manipulating cells
- Select Special
- Using the VB Editor
- Code Window:
- Running and Stepping code
- Breakpoints and Break mode
- Complete Word
- Commenting/ Uncommenting
- Object Browser
- Events
- The role of event driven procedures
- Using the event code shells
- OnTime and OnKey Methods
- Functions
- Using VBA functions
- The Format function
- How to call Excel functions in your code
- How to create a Function procedure
- Functions vs Subs
- Creating a custom function for Excel
- Creating an Add-In
- User Interface
- Message Box statement
- Message Box function
- Input Box function
- Input Box method
- Excel’s Status Bar and Caption
- Calling Excel’s built-in Dialogs
- Review of Excel’s user interface features
- Creating a Simple User Form
- More VBA
- Using Arrays to store sets of data