COURSE INFORMATION
The aim of the course is to enable you to understand Macros using VBA terminology within the Visual Basic Editor. You will be able to explore and understand the VBA language to help manipulate data by declaring variables and procedures of correct scope. You will learn to use decision structures to perform and loop structures to perform tasks. You will look at how to ensure that future editing, amending and updating can be carried out with a minimum of effort.
ONVIB
- Recording Macros
- Working with the Visual Basic Editor
- Developing Procedures
- Objects
- Declaring Variables
- Managing the Flow of Control
- Debugging
- Message Boxes and Input Boxes
- Creating Custom Forms
- Creating User Defined Functions
- Error Handling
Recording Macros
- Recording and running macros
- Recording Absolute and Relative selection
- Saving a macro-enabled workbook
- Assigning a macro to the QAT
- Creating a Macro Button
- Using the Personal Macro Workbook
Working with the Visual Basic Editor
- Introducing Visual Basic for Applications
- Navigating the Visual Basic Editor
- Using AutoLists
Developing Procedures
- Understanding and creating modules
- Defining procedures
- Calling subroutines
Objects
- The Excel Document Object Model
- Objects and Collections
- The Object Browser
- Methods and Properties
- The With statement
Declaring Variables
- How to declare variables
- Defining the Scope of variables
- Determining Data Types
- Defining constants
Managing the Flow of Control
- Defining control structures
- Decision making structures:
- If...End If structure
- Looping structures:
- Conditional loops: the Do...Loop structure
- Counter loops: the For...Next structure
- Collection loops: the For Each...Next structure
- Select Case...End Select structure
Debugging
- Stepping through code
- Setting Breakpoints
- Identifying the current value of expressions
- Using the Immediate Window
Message Boxes and Input Boxes
- Creating a Message box
- Message box return values
- The Input Box function
- Excel's Input Box method
- Handling input box return values
Creating Custom Forms
- Worksheet Form creation
- Adding form controls
- Setting the properties of controls
Creating User Defined Functions
- Calling Excel worksheet functions
- Creating a function procedure
- Calling a User Defined Function
Error Handling
- Defining error handling
- VBA's error trapping options
- Capturing errors with On Error statements
- The Error object
- A good knowledge of the advanced features in Excel is very beneficial.
- This is a programming course and spans all versions of Excel. A good knowledge of Excel features, formula and functions is recommended.
- £345
- Two day trainer led or virtual
