|
 Visual Basic for Applications is the integrated programming language used in Excel 2003. In this course we take an in-depth look at this language, and how it is applied to developing programs & automating operations in Excel 2003.
Duration:
3 Days
Audience:
This course had been designed for the non-technical user who needs to design & create automated worksheets in 2003.
Prerequisites:
Participants must have a good understanding of Excel spreadsheets in particular working with ranges.
Course Content:
Introduction to VBA
• Why use VBA if I can record macros in Excel?
• Recoding a Simple macro
• Reviewing the code
• Familiarisation with the VBA environment
• Running Code from the VB Editor window
• Getting help on code
• Stepping through a procedure
• Using a Break point
• Communicating with the User
VBA Terminology
• Modules and procedures
• Components of your code
• Objects, Collections, Properties and methods
• Using the Excel Object model
Data Types, Variables and Operators
• Working with variables and constants
• Using Data types
• Working with operators and expressions
• Implicit and Explicit Declarations
• Variable Scope - Procedural, Modular or Public
• Passing variables by Value
• Passing variables by Reference
• Using Arrays
Workbooks and Worksheets
• Workbook objects and methods
• Creating a new workbook
• Adding and Renaming Sheets
• Saving and Closing a Workbook
Functions
• Mathematical functions
• Date and time functions
• String functions
• Using Excel functions in VBA code
User Defined Function Procedures
• Creating User Defined functions
• Using user-defined functions in a worksheet
• Declaring Multiple arguments in a function
• Creating a Function Library
Loops
• Do Until and Do While loops
• Looping at least once
• For Next Loop
• For Each Loops with collections
Decision Structures in VBA
• Using IF to make decisions
• Testing for multiple conditions
• Establishing Flow control
• Branching
• Call other procedures
Working with Names
• Adding Names
• Deleting Names
• Creating Hidden Names
• Checking for the Existence of a Name
Manipulating data
• Working with the Ranges and Selections
• Using the Cells Property to Select a Range
• Using the Offset Property to Refer to a Range
• Using the Columns and Rows Properties
• Using the Union Method to Join Multiple Ranges
• Using the IsEmpty Function
• Using the CurrentRegion Property
• Using the Areas Collection
File management techniques in VBA
• Open and Save files
• Copy, move and delete files
• Changing folders
• Using Excel Open and SaveAs dialog boxes
Custom Forms / Dialog boxes
• Creating a user form
• Labels, text boxes, combo boxes and list boxes
• Setting properties for the form and controls
• Assigning data to combo boxes and list boxes
• Option buttons, Groups, check boxes and buttons
• Creating the event code for controls
• Initialising the form
• Closing the form
• Using RefEdit to allow user interaction
Toolbars and menus
• Adding Toolbars to your application
• Showing and Hiding the toolbar
• Creating a custom menu
• Adding macros to menu commands
• Removing a menu
Managing Information with VBA
• Linking Excel to an Access database
• Adding a Record to the Database from Excel
• Retrieving Records from the Database
• Updating an Existing Record
|
Software Courses 
(07) 3210 0002
info@CTStraining.com.au
