Course Code

vbaexceladv
 

     Duration

14 Hours
 
 

     Requirements

  • An understanding of VBA
  • Procedural programming experience

Audience

  • Data Analysts
 

     Overview

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.
 

     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 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)

 

     Feedback (13)

Content and the way it was provided.

Marcin Samborski


I generally enjoyed the instructors knowledge.

US Bank


I was benefit from the examples from different area of VBA programming.

UBS Business Solutions Poland Sp. z o.o.


The whole topic is interesting - everything was OK.

Bartosz Wierzewski - UBS B


I mostly was benefit from the fitted training to people needs.

Robert Solek - UBS Business Solutions Poland Sp. z o.o.


I generally enjoyed the knowledge and sense of humor.

Łukasz Rózga - UBS Business Solutions Poland Sp. z o.o.


I liked the trainer, nice guy with great attitude.

Lukasz Kanior - UBS Business Solutions Poland Sp. z o.o.


I was benefit from the trainer knowledge, explanation and tips.

Kornel Tymcio - UBS Business Solutions Poland Sp. z o.o.


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.


I like the hands on training and seeing us solve for issues on the spot.

Jon Matrille - LocumTenens.com


Trainer's help in solving problems

Tomasz Czornak


Doing live exercises in Excel, and trainer knowledge.

Perceval Pradelle


well structured.



The course could be tailored to suit your needs and objectives. It can also be delivered on your premises if preferred.


  
  
  


  

Online Price per participant 6000 AED

  

Classroom Price per participant 6000 AED

Starts

 

Ends

 

  Workday courses take place between 9:30 and 16:30

Location


  Show venue details


Number of Participants






Related Courses

Total Courses 4


Access VBA

  21 hours

VBA For Access & Excel

  21 hours

Excel Advanced with VBA

  21 hours

Visual Basic for Applications (VBA) in Excel - Introduction to programming

  21 hours

Visual Basic for Applications (VBA) in Excel (intermediate level)

  14 hours

Visual Basic for Applications (VBA) for Analysts

  21 hours

Excel and VBA Programming for Audit and Finance Professionals

  28 hours

 

Analysing Financial Data in Excel

  14 hours

 

Excel For Statistical Data Analysis

  14 hours

Data and Analytics - from the ground up

  42 hours

Working with spreadsheet in Microsoft Excel

  14 hours



Discounted Public Courses



This site in other countries/regions