Course Code: excelvbai
Duration: 14 hours
Prerequisites:
  • Good Excel user knowledge.

Audience

  • Data Analysts
Overview:

It is an introduction to procedural programming in VBA. Training allows you to gain a strong foundation for further learning and VBA environment.

This instructor-led, live training (online or onsite) is aimed at data analysts who wish to use VBA in Excel for automating tasks.

By the end of this training, participants will be able to:

  • Record and edit macros as required.
  • Write procedures using data from the sheet.
  • Create your own functions.
  • Handle an event (opening worksheet cell update etc.) by means of the handler.
  • Create a form.

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:

Macros

  • Recording and Modifying Macros
  • Assigning Macros to Toolbar, Buttons, Shortcut key

Visual Basic for Applications Environment

  • Visual Basic Editor and its options
  • Component Management
  • Shortcuts
  • Environment Optimization

Introduction to Procedural Programming

  • Procedures: Functions and Sub-routines
  • Data Types

Control Flow

  • If ...Then....Elseif....Else....Endif
  • Case
  • Do...Loop , While
  • For... Next
  • Exit
  • Arrays and Loops
  • Strings

Visual Basic

  • VBA and Sheet Data Exchange (Cells, Range Methods)
  • Getting and Printing Data from Users (InputBox, MsgBox)
  • Variable Declaration
  • Scope and LifeTime of Variables and Objects
  • Operators and Their Priorities
  • Modules Options
  • Creating Worksheet Functions
  • Objects, Classes, Methods and Properties
  • Locking Project for Viewing

Debugging

  • Run Time, Design Time, and Break Mode
  • Brakes and Watches
  • Local Window
  • Immediate Window
  • Call Stack

Error Handling

  • Errors Types
  • On Error GoTo
  • On Error Resume Next

Excel Model Object

  • Application Object
  • Workbook Object and Workbooks Collection
  • Worksheet Object and Worksheets Collection
  • ThisWorkbook, ActiveWorkbook, ActiveCell, ....
  • Selection Object
  • Range Collection
  • Cells Collection
  • Using Status Bar
  • Screen Updating and Optimizing
  • Timer and Time Measuring

Events Handling in VBA

VBA Forms

  • Creating and Using Forms
  • Invoking Forms in VBA Code
  • Using VBA Forms in Worksheet
Sites Published:

Qatar - Excel VBA Introduction

Egypt - Excel VBA Introduction

Saudi Arabia - Excel VBA Introduction

Canada - Excel VBA Introduction

澳門 - Excel VBA Introduction

USA - Excel VBA Introduction

Österreich - Excel VBA Introduction

Schweiz - Excel VBA Introduction

Czech Republic - Excel VBA Introduction

Denmark - Excel VBA Introduction

Estonia - Excel VBA Introduction

Finland - Excel VBA Introduction

Luxembourg - Excel VBA Introduction

Latvia - Excel VBA Introduction

Lithuania - Excel VBA Introduction

Norway - Excel VBA Introduction

România - Excel VBA Introduction

Türkiye - Excel VBA Introduction

Malta - Excel VBA Introduction

Malaysia - Excel VBA Introduction

New Zealand - Excel VBA Introduction

Thailand - Excel VBA Introduction

Vietnam - Excel VBA Introduction

Argentina - Excel VBA Introduction

Chile - Excel VBA Introduction

Costa Rica - Excel VBA Introduction

Ecuador - Excel VBA Introduction

Guatemala - Excel VBA Introduction

Colombia - Excel VBA Introduction

México - Excel VBA Introduction

Panama - Excel VBA Introduction

Peru - Excel VBA Introduction

Uruguay - Excel VBA Introduction

Venezuela - Excel VBA Introduction

Bolivia - Excel VBA Introduction

Indonesia - Excel VBA Introduction

Kazakhstan - Excel VBA Introduction

Moldova - Excel VBA Introduction

Morocco - Excel VBA Introduction

Tunisia - Excel VBA Introduction

Kuwait - Excel VBA Introduction

Oman - Excel VBA Introduction

Slovakia - Excel VBA Introduction

Kenya - Excel VBA Introduction

Nigeria - Excel VBA Introduction

Botswana - Excel VBA Introduction

Slovenia - Excel VBA Introduction

Croatia - Excel VBA Introduction

Serbia - Excel VBA Introduction

Bhutan - Excel VBA Introduction

Nepal - Excel VBA Introduction

Uzbekistan - Excel VBA Introduction