Course Outline
Excel Object Model
- Protecting sheets via VBA
- Workbook objects and Workbooks collection
- Worksheet object and Worksheets collection
- Validating sheets
- Practical methods for the Range object
- Copy, paste, and paste special operations
- CurrentRegion property
- Find and replace functions
- Sorting ranges
- Charts (Chart object)
Events
- Application-level events
Arrays
- Dynamic arrays
- Table arrays and Variants
- Optimising arrays and memory usage
- Multi-dimensional arrays
Object-Oriented Programming
- Classes and Objects
- Creating classes
- Instantiating and destroying objects
- Defining methods
- Defining properties
- Data validation using properties
- Default properties and methods
- Error handling within class modules
Creating and managing collections
- Creating a collection
- Adding and removing items
- Referencing components (utilising keys and indices)
Advanced VBA structures and functions
- Passing parameters by value and by reference (ByRef and ByVal)
- Procedures with a variable number of parameters
- Optional parameters and defaults
- Procedures with an unknown number of parameters (ParamArray)
- Enumeration for streamlined parameter passing
- User-defined types
- Handling Null, Nothing, empty string "", Empty, and 0
- Type conversion
File Operations
- Opening and closing text files
- Reading and writing text and binary data
- Processing records in CSV files
- Efficient processing of text files
Utilising VBA functions in other applications
Extras
- Developing custom add-ins
- Creating toolbars for add-ins
- Installing and protecting custom add-ins
Utilising external libraries
Connecting to external databases (ODBC, OLEDB)
Testimonials (7)
I like the hands on training and seeing us solve for issues on the spot.
Jon Matrille - LocumTenens.com
Course - Visual Basic for Applications (VBA) in Excel - Advanced
I really enjoy the training. Huge and practical! knowledge of the trainer combined with his skill to conduct the training made the training time very efficient. The trainer recognized the level of participant's experience in VBA and provided exercises relevant to that experience which made the training very useful.
Barbara Peek - UBS Business Solutions Poland Sp. z o.o.
Course - Visual Basic for Applications (VBA) in Excel - Advanced
I was benefit from the trainer knowledge, explanation and tips.
Kornel Tymcio - UBS Business Solutions Poland Sp. z o.o.
Course - Visual Basic for Applications (VBA) in Excel - Advanced
I liked the trainer, nice guy with great attitude.
Lukasz Kanior - UBS Business Solutions Poland Sp. z o.o.
Course - Visual Basic for Applications (VBA) in Excel - Advanced
I generally enjoyed the knowledge and sense of humor.
Lukasz Rozga - UBS Business Solutions Poland Sp. z o.o.
Course - Visual Basic for Applications (VBA) in Excel - Advanced
I mostly was benefit from the fitted training to people needs.
Robert Solek - UBS Business Solutions Poland Sp. z o.o.
Course - Visual Basic for Applications (VBA) in Excel - Advanced
The whole topic is interesting - everything was OK.
