Visual Basic for Applications (VBA) in Excel - Advanced ( vbaexceladv | 14 hours )

The course reveals the secrets of effective writing advanced applications in VBA application MsExcel.

This instructor-led, live training (online or onsite) is aimed at data analysts who wish to use advanced VBA in Excel for automating tasks.

By the end of this training, participants will be able to:

• Write an advanced object-oriented application
• Convert text files
• Use of external data sources
• Use external libraries

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Excel Object Model

• The protection sheet from VBA
• Object Workbook, Workbooks collection
• Worksheet Object, Collection Worksheets
• Validation sheets
• Practical methods of the Range object
• Copy, paste, paste special
• Property CurrentRegion
• Find, replace
• Sorting ranges
• Charts (Object Chart)

Events

• Application-level events

Boards

• Dynamic arrays
• Table arrays Variant
• Optimizing arrays and memory
• Multi-dimensional arrays

Object-Oriented Programming

• Classes and Objects
• Creating classes
• Creating and Destroying Objects
• Create methods
• Create property
• Validation data using property
• The default properties and methods
• Error handling in the class module

Create and manage collections

• Create a collection
• Adding and removing items
• References to the components (using a key and an index)

Advanced structures and functions VBA

• Passing parameters by value and reference (ByRef and ByVal)
• Procedures with a variable number of parameters
• Optional Parameters and Defaults
• Procedures of unknown number of parameters (ParamArray)
• Enumeration, convenient parameter passing
• Type the user (User-defined Type)
• Service Null, Nothing, empty string "", Empty, 0
• Type conversion (Conversion)

File Operations

• Opening and closing text files
• Reading and writing text and binary data
• Processing of records in the CSV file
• Efficient processing of text files

Use VBA functions in other applications

Extras

• Create your own add-ons
• Create a toolbar for addition
• Installing your own add-ons and their protection

Using external libraries

Connecting to external databases (ODBC, OLEDB)