Visual Basic for Applications (VBA) in Excel - Advanced Training Course

Primary tabs

Course Language

This course is delivered in English.

Course Code

vbaexceladv

Duration Duration

21 hours (usually 3 days including breaks)

Requirements Requirements

It is assumed that course participants are already familiar with Excel spreadsheet and know how to program in VBA or any procedural language (Pascal, C, Basic, etc.). It is recommended to complete the course Visual Basic for Applications - Introduction to Programming<

Overview Overview

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

After the course you can:

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

Course Outline Course Outline

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 classroom

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)


Guaranteed to run even with a single delegate!
Public Classroom Public Classroom
Participants from multiple organisations. Topics usually cannot be customised
From $6140
(117)
Private Classroom Private Classroom
Participants are from one organisation only. No external participants are allowed. Usually customised to a specific group, course topics are agreed between the client and the trainer.
From $6140
Request quote
Private Remote Private Remote
The instructor and the participants are in two different physical locations and communicate via the Internet
From $3440
Request quote

The more delegates, the greater the savings per delegate. Table reflects price per delegate and is used for illustration purposes only, actual prices may differ.

Number of Delegates Public Classroom Private Classroom Private Remote
1 $6140 $6140 $3440
2 $3510 $3435 $2085
3 $2633 $2533 $1633
4 $2195 $2083 $1408
Cannot find a suitable date? Choose Your Course Date >>
Too expensive? Suggest your price

Related Categories


Course Discounts

Course Venue Course Date Course Price [Remote/Classroom]
CP100A: Google Cloud Platform Fundamentals ON, Toronto - University & Dundas Fri, Jul 29 2016, 9:30 am $1208 / N/A
CP306A: Google Container Engine and Kubernetes Remote Course Fri, Aug 26 2016, 9:30 am $1220 / N/A
Forecasting with R Remote Course Tue, Aug 30 2016, 9:30 am $2450 / N/A
SQL Fundamentals Remote Course Fri, Sep 16 2016, 9:30 am $750 / N/A

Upcoming Courses

VenueCourse DateCourse Price [Remote/Classroom]
AB, Edmonton - First Edmonton PlaceMon, Aug 15 2016, 9:30 am$3440 / $6740
ON, Toronto - University & DundasMon, Aug 15 2016, 9:30 am$3440 / $6741
ON, Ottawa - Fairmont Chateau LaurierTue, Aug 16 2016, 9:30 am$3440 / $6740
BC, Burnaby Wed, Aug 17 2016, 9:30 am$3440 / $6290
NB, Saint JohnMon, Aug 22 2016, 9:30 am$3440 / $7190

Some of our clients