Visual Basic for Applications (VBA) for Analysts Training Course

Primary tabs

Client Testimonials

duża ilość ćwiczeń

Małgorzata Jasniecka - IZBA SKARBOWA W OPOLU

Możliwości przećwiczenia całej wiedzy zdobytej na szkoleniu w dodatkowych zadaniach.

Wioleta Kubczak - IZBA SKARBOWA W OPOLU

Course 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 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 1533PLN
(83)
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 Private Remote
The instructor and the participants are in two different physical locations and communicate via the Internet
From 2800PLN
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 Remote
1 1533PLN 2800PLN
2 1317PLN 1950PLN
3 1244PLN 1667PLN
4 1208PLN 1525PLN
Cannot find a suitable date? Choose Your Course Date >>
Too expensive? Suggest your price

Related Categories


Upcoming Courses

VenueCourse DateCourse Price [Remote / Classroom]
Białystok, ul. Malmeda 1Mon, 2017-02-06 09:002800PLN / 1983PLN
Toruń, ul. Żeglarska 10/14Mon, 2017-02-06 09:002800PLN / 1833PLN
Olsztyn, ul. Kajki 3/1Mon, 2017-02-06 09:002800PLN / 1683PLN
Warszawa, ul. Złota 3/11Mon, 2017-02-06 09:002800PLN / 1533PLN
Częstochowa, ul. Wały Dwernickiego 117/121Mon, 2017-02-06 09:002800PLN / 1983PLN

Course Discounts

Course Venue Course Date Course Price [Remote / Classroom]
Angular JavaScript Poznan, Garbary 100/63 Mon, 2017-01-23 09:00 7425PLN / 3275PLN
Container management with Docker Zielona Góra, ul. Reja 6 Mon, 2017-01-23 09:00 8910PLN / 3720PLN
Comprehensive Git Kraków, ul. Rzemieślnicza 1 Mon, 2017-01-23 09:00 5841PLN / 3147PLN
Building Web Apps using the MEAN stack Poznan, Garbary 100/63 Mon, 2017-01-30 09:00 14652PLN / 5440PLN
Adobe Photoshop Elements Katowice ul. Opolska 22 Mon, 2017-01-30 09:00 1881PLN / 1327PLN
Psychologiczne aspekty zarządzania zespołem IT – psychologia zespołu Scrum agile Toruń, ul. Żeglarska 10/14 Mon, 2017-02-06 09:00 5742PLN / 2340PLN
Programming in C++ Warszawa, ul. Złota 3/11 Mon, 2017-02-06 09:00 5445PLN / 2815PLN
Visual Basic for Applications (VBA) in Excel (intermediate level) Warszawa, ul. Złota 3/11 Mon, 2017-02-06 09:00 2376PLN / 1192PLN
C#.Net Olsztyn, ul. Kajki 3/1 Mon, 2017-02-06 09:00 25047PLN / 8840PLN
Java Performance Wroclaw, ul.Ludwika Rydygiera 2a/22 Mon, 2017-02-06 09:00 9801PLN / 3000PLN
Creating and managing Web sites Olsztyn, ul. Kajki 3/1 Mon, 2017-02-06 09:00 5841PLN / 2548PLN
Adobe Photoshop Gdynia, ul. Ejsmonda 2 Mon, 2017-02-06 09:00 1881PLN / 1452PLN
Marketing Analytics using R Gdańsk, ul. Powstańców Warszawskich 45 Wed, 2017-02-08 09:00 11880PLN / 5010PLN
Team Building and Management Szczecin, ul. Małopolska 23 Tue, 2017-02-14 09:00 5346PLN / 1569PLN
Microsoft Access - download the data Poznan, Garbary 100/63 Thu, 2017-02-16 09:00 2475PLN / 1225PLN
OCUP2 UML 2.5 Certification - Foundation Exam Preparation Warszawa, ul. Złota 3/11 Mon, 2017-02-20 09:00 7000PLN / 2933PLN
Programming in ASP.NET MVC 5 Gdynia, ul. Ejsmonda 2 Mon, 2017-02-20 09:00 5841PLN / 2673PLN
Working with spreadsheet in Microsoft Excel Szczecin, ul. Małopolska 23 Tue, 2017-02-28 09:00 1485PLN / 995PLN
Introduction to CSS3 Poznan, Garbary 100/63 Wed, 2017-03-22 09:00 1881PLN / 952PLN

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