Visual Basic for Applications (VBA) for Analysts Training Course

Primary tabs

Course Language

This course is delivered in Polish or English.

Course Code

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 Outline Course 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


Guaranteed to run even with a single delegate!
Public Classroom Public Classroom
Participants from multiple organisations. Topics usually cannot be customised
From 1687PLN
(77)
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 1698PLN
Request quote
Private Remote Private Remote
The instructor and the participants are in two different physical locations and communicate via the Internet
From 4390PLN
Request quote
SelfStudy SelfStudy
Self-study courses allow you to learn at your own pace on your own time. There is no live instructor involved. The participants use recorded video, quizzes and reading at their own convenience.
Price not set yet
Register Interest

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 1687PLN 1698PLN 4390PLN
2 1494PLN 1399PLN 2745PLN
3 1429PLN 1299PLN 2197PLN
4 1397PLN 1250PLN 1923PLN
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]
Visual Basic for Applications (VBA) for Analysts Poznan, Garbary Mon, 2016-07-04 09:00 1912PLN / 1278PLN
Debian Administration Poznan, Garbary Mon, 2016-07-04 09:00 3157PLN / 2083PLN
Agile Project Management with Scrum Szczecin Wed, 2016-07-06 09:00 1746PLN / 1361PLN
A Practical Guide to Successful Pricing Strategies Poznan, Garbary Wed, 2016-07-06 09:00 1427PLN / 1093PLN
SQL language in MSSQL Poznan, Garbary Thu, 2016-07-07 09:00 1568PLN / 1142PLN
Excel and VBA Programming for Audit and Finance Professionals Warszawa, ul. Złota 3/11 Mon, 2016-07-11 09:00 1913PLN / 1441PLN
Introduction to Selenium Wrocław, ul.Ludwika Rydygiera 2a/22 Thu, 2016-07-14 09:00 768PLN / 539PLN
Forecasting with R Poznan, Garbary Thu, 2016-07-14 09:00 2188PLN / 1527PLN
Machine Learning Fundamentals with R Warszawa, ul. Złota 3/11 Mon, 2016-07-18 09:00 2523PLN / 1828PLN
Administracja bazą danych Microsoft SQL Server 2012 Toruń, ul. Żeglarska 10/14 Mon, 2016-07-18 09:00 2509PLN / 1653PLN
Apache Tomcat Administration Kraków Mon, 2016-07-18 09:00 1713PLN / 1438PLN
Effective working with spreadsheet in Excel Gdańsk Tue, 2016-07-19 09:00 768PLN / 652PLN
ITIL®: Intermediate Lifecycle Stream: Service Strategy (SS) Kraków Tue, 2016-07-19 09:00 4338PLN / 3116PLN
Microsoft Access - download the data Poznan, Garbary Wed, 2016-07-20 09:00 1117PLN / 856PLN
Debian Administration Olsztyn, ul. Kajki 3/1 Mon, 2016-07-25 09:00 3157PLN / 2167PLN
Python Programming Warszawa, ul. Złota 3/11 Mon, 2016-08-01 09:00 5790PLN / 3753PLN
Data Analysis with Oracle 11g - workshop Warszawa, ul. Złota 3/11 Mon, 2016-08-01 09:00 4350PLN / 3012PLN
Web Application Development in PHP Warszawa, ul. Złota 3/11 Wed, 2016-08-03 09:00 2688PLN / 2022PLN
Zapewnienie jakości oprogramowania – przegląd metodyk Warszawa, ul. Złota 3/11 Tue, 2016-08-09 09:00 2735PLN / 1863PLN
Oracle 11g - Programming with PL / SQL II Wrocław, ul.Ludwika Rydygiera 2a/22 Wed, 2016-08-10 09:00 2363PLN / 1785PLN
Stress management Warszawa, ul. Złota 3/11 Thu, 2016-08-18 09:00 2112PLN / 1212PLN
Java Spring Kraków Mon, 2016-08-29 09:00 7039PLN / 5245PLN
Java Spring Szczecin Mon, 2016-09-05 09:00 7039PLN / 5044PLN
Programming in WPF 4.5 Warszawa, ul. Złota 3/11 Mon, 2016-09-05 09:00 2359PLN / 1355PLN
Building Web Apps using the MEAN stack Szczecin Mon, 2016-09-12 09:00 4788PLN / 3124PLN
Java Spring Gdańsk Mon, 2016-09-12 09:00 7039PLN / 5153PLN
Java Spring Poznan, Garbary Mon, 2016-09-12 09:00 7039PLN / 4961PLN
Java Spring Wrocław, ul.Ludwika Rydygiera 2a/22 Mon, 2016-09-19 09:00 7039PLN / 4961PLN
Java Spring Warszawa, ul. Złota 3/11 Mon, 2016-09-19 09:00 7039PLN / 4961PLN
BPMN 2.0 for Business Analysts Wrocław, ul.Ludwika Rydygiera 2a/22 Tue, 2016-09-27 09:00 3110PLN / 2337PLN
Visual Basic for Applications (VBA) in Excel - Advanced Białystok, ul. Malmeda 1 Mon, 2016-11-14 09:00 1689PLN / 1413PLN

Upcoming Courses

VenueCourse DateCourse Price [Remote/Classroom]
Poznan, GarbaryMon, 2016-07-04 09:001912PLN / 1278PLN
KrakówMon, 2016-07-18 09:001923PLN / 1462PLN
LublinWed, 2016-07-20 09:001923PLN / 1421PLN
GdańskWed, 2016-07-20 09:001923PLN / 1393PLN
Zielona Góra, ul. Reja 6Wed, 2016-07-20 09:001923PLN / 1321PLN

Some of our clients