Visual Basic for Applications (VBA) for Analysts Training Course

Course Code

3164

Duration

21 hours (usually 3 days including breaks)

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

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

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

Testimonials

★★★★★
★★★★★

Related Categories

Course Discounts

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

is growing fast!

We are looking to expand our presence in Israel!

As a Business Development Manager you will:

  • expand business in Israel
  • recruit local talent (sales, agents, trainers, consultants)
  • recruit local trainers and consultants

We offer:

  • Artificial Intelligence and Big Data systems to support your local operation
  • high-tech automation
  • continuously upgraded course catalogue and content
  • good fun in international team

If you are interested in running a high-tech, high-quality training and consulting business.

Apply now!