Key Information
Duration: 1 Day days
Course Learning Skills: VBA 2013
Additional Material: Download Course Slides Link
Brief
We offer three one day Excel VBA courses to cover the three levels of Basic, Intermediate and Advanced. You can, if you wish, do all three over a three day period or combine any two over a two day period.
Also, you can mix and match modules from the three levels if you would prefer to tailor a course more suited to your needs.
Attendees
This course is for experienced users of Excel with basic VBA knowledge now looking to control a wider and more powerful set of features within their spreadsheets.
Prerequisites
Ideally each delegate should already be proficient at creating and maintaining spreadsheets, be comfortable with most of Excel’s advanced features and be familiar with the basic features of VBA (e.g. procedures, functions, objects, properties and methods). Those who have not done VBA previously or would like a refresher are welcome to combine our basic course with this one over a two day period.
Benefits
Delegates will gain a broader understanding of Excel VBA programming and be able to do each of the following:
- Create and maintain screen forms for application control
- Use VBA to maintain and manipulate charts
- Use VBA to maintain and manipulate pivot tables
Recap of VBA concepts
- Overview of the Excel object and collections hierarchy
- Overview of procedures versus functions
- Overview of variables and their scope
- Overview of control and decision structures
Working with basic objects
- Overview of useful Application object properties
- Use of events attached to Workbook and Worksheet objects
- Overview of useful Range object methods and properties
- Use of VBA to sort sheets
- Use of the Comment object
- Error handling
User forms
- Maintenance and display of a user form
- Basic controls – Label, Text-Box, Command-Button
- List items – List-Box, Combo-Box
- Mouse-controlled items – Check-Box, Option-Button(Radio Button), Toggle-Button
- Display items – Frame, Tab-Strip, Multi-Page, Scroll-Bar, Spin-Button, Image
- Use of form events, procedures and functions
Using VBA to maintain Charts
- Creation of a chart as a sheet or as an object within a sheet
- Application of formats to different chart areas and items
- Maintenance of data labels and values
- Maintenance of key attributes
Using VBA to maintain pivot tables
- Creation of a pivot table and definition of its source data
- Manipulation of column and row headings
- Manipulation of cell values
- Application of grouping and filtering
- Application of formats to different sections of a table