Visual Basic for Applications (VBA) for Analysts Training Course

Primary tabs

Course CodeCourse Code

3164

Duration Duration

21 hours (usually 3 days including breaks)

Requirements Requirements

Ability to work with a spreadsheet, basic knowledge (references, ranges, sheets, ...). No knowledge is required to create macros, SQL, or write code in VBA.

Overview Overview

The course is designed for users of Excel for creating analyzes and reports.

Course OutlineCourse Outline

Macros

  • Recording and editing macros
  • Where to store macros.
  • Assigning macros to forms, toolbars, keyboard shortcuts

VBA Environment

  • Visual Basic Editor and its options
  • Keyboard Shortcuts
  • Optimizing the environment

Introduction to procedural programming

  • Procedures: Function, Sub
  • The data types
  • The conditional statement If...Then....Elseif....Else....End If
  • Instruction Case
  • Loop while, until
  • Loop for ... next
  • Instructions break the loop(exit)

Strings

  • Combining strings (concatenation)
  • Conversion to other types - implicit and explicit
  • Features processing strings

Visual Basic

  • Download and upload data to a spreadsheet (Cells, Range)
  • Download and upload data to the user (InputBox, MsgBox)
  • The declaration of variables
  • The extent and lifetime of variables
  • Operators and their priorities
  • Options modules
  • Create your own functions and use them in a sheet
  • Objects, classes, methods and properties
  • Securing code
  • Security code tampering and preview

Debugging

  • Processing step
  • Locals window
  • Immediate window
  • Traps - Watches
  • Call Stack

Error handling

  • Types of errors and ways to avoid
  • Capturing and handling run-time errors
  • Structures: On Error Resume Next, On Error GoTo label, On Error GoTo 0

Excel Object Model

  • The Application object
  • Workbook object and a collection of Workbooks
  • Worksheet Object and Collection Worksheets
  • Objects ThisWorkbook, ActiveWorkbook, ActiveCell ....
  • Object Selection
  • Collection Range
  • Object Cells
  • Display data on the statusbar
  • Optimization using ScreenUpdating
  • The time measurement by the method Timer

The use of external data sources

  • Using ADO library
  • References to external data sources
  • ADO objects:
    • Connection
    • Command
    • Recordset
  • Connection string
  • Create connections to different databases: Microsoft Access, Oracle, MySQL

Reporting

  • Introduction to the SQL language The basic structure of SQL (SELECT, UPDATE, INSERT INTO, DELETE) Calling a Microsoft Access query from Excel Forms to support the use of databases

TestimonialsTestimonials

the practical examples.

Marika Agius - Malta Financial Services Authority

STEP BY STEP EVEN HANDS ON WITH REALISTIC DATA EXAMPLES

CARL MIFSUD - Malta Financial Services Authority

Hands-on practice and exercises

Godwin Spiteri - Malta Financial Services Authority

Kamil has an excellent way of explaining things. He speaks clearly and will take the time to explain things individually when necessary.

Theresa Fenech - Malta Financial Services Authority

The trainer was very well prepared and encouraged interaction with the group. The exercises were very interesting and enabled us to utilise the knowledge on the subject in a practical context.

Tomasz Kolbuszewski - Malta Financial Services Authority

Trainer was calm and very patient to explain in a way that everyone understands, even those with basic knowledge of excel.

Sera Farrugia - Malta Financial Services Authority

its applicability to work related issues

Ranier Buhagiar - Malta Financial Services Authority

Training customised to our company's needs; individual attention; private classroom

Rosanne Tanti - Malta Financial Services Authority

I liked the fact that we were a small group and therefore the trainer was able to offer individual attention to each trainee.

Claire Pace - Malta Financial Services Authority

Bookings, Prices and EnquiriesBookings, Prices and Enquiries

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.
Private Remote
From 1690EUR
Private Remote
The instructor and the participants are in two different physical locations and communicate via the Internet. More Information

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 Private Remote
1 1690EUR
2 1145EUR
3 963EUR
4 873EUR
Public Classroom
From 2340EUR
(182)
Public Classroom
Participants from multiple organisations. Topics usually cannot be customised

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
1 2340EUR
2 1495EUR
3 1213EUR
4 1073EUR
Cannot find a suitable date? Choose Your Course Date >>
Too expensive? Suggest your price

Related Categories

Related Courses


Upcoming Courses

VenueCourse DateCourse Price [Remote / Classroom]
BaselMon, 2017-11-06 09:301690EUR / 2340EUR
ZürichMon, 2017-11-06 09:301690EUR / 2340EUR
BernTue, 2017-11-07 09:301690EUR / 2340EUR

Course Discounts

Course Venue Course Date Course Price [Remote / Classroom]
MongoDB for Developers Zürich Mon, 2017-11-06 09:30 1782EUR / 2282EUR
Statistics Level 1 Bern Wed, 2017-11-15 09:30 1881EUR / 2381EUR
Neural Network in R Zürich Tue, 2017-11-21 09:30 1872EUR / 2372EUR
Semantic Web Overview Zürich Wed, 2017-11-29 09:30 972EUR / 1322EUR
Drools Rules Administration Bern Wed, 2018-02-28 09:30 2961EUR / 3611EUR

Course Discounts Newsletter

We respect the privacy of your email address. We will not pass on or sell your address to others.
You can always change your preferences or unsubscribe completely.

Some of our clients