Training Courses

Training Courses
Programming with VBA in Microsoft Excel

Code: VBA
Type: IT InstructorLed Courses
Category: Visual Basic For Applications

VBA (Visual Basic for Applications) is an Object Oriented Language. This programming language can be used to automate tasks by developing procedures and functions. It is useful for programmers and for people who wish build procedures (macros) in order to automate tasks and increase their own productivity and the productivity of the people of their departments.



Duration: 4 days, 28 hours
Location: EDITC & MMC Conference Center, 16 Imvrou Street, 1055 Nicosia
Language: Greek
Attendance: 6-28
 

Topics

Unit 1: What  Is VBA?
 
What Is VBA?
What Can You Do with VBA?

  • Inserting a bunch of text
  • Automating a task  you perform frequently
  • Automating repetitive operations
  • Creating a custom command
  • Creating a custom button
  • Developing new worksheet functions
  • Creating custom add-ins for Excel
  • Creating complete, macro-driven applications
  • Advantages and  Disadvantages of VBA
  • VBA in a Nutshell

An Excursion into Versions

Unit 2: Jumping Right In  

  • Taking the  First Steps
  • Recording the  Macro
  • Testing the  Macro
  • Examining the  Macro  
  • Modifying  the  Macro
  • Saving Workbooks That  Contain Macros
  • Understanding Macro  Security

More about the  NameAndTime Macro

Unit 3: Working in the Visual Basic Editor  

What Is the  Visual Basic Editor?

  • Activating the  VBE
  • Understanding VBE components

Working  with the  Project Window

  • Adding  a new VBA module
  • Removing a VBA module
  • Exporting and  importing objects

Working  with a Code Window

  • Minimizing and  maximizing windows
  • Creating a module
  • Getting  VBA code into a module
  • Entering code directly
  • Using the  macro  recorder
  • Copying  VBA code

Customizing the  VBA Environment

  • Using the  Editor  tab
  • Using the  Editor  Format tab
  • Using the  General tab
  • Using the  Docking tab

Unit 4: Introducing the Excel Object Model

Excel Is an Object?
Climbing  the  Object Hierarchy
Wrapping Your Mind around Collections
Referring to Objects

  • Navigating through the  hierarchy
  • Simplifying object references

Diving into Object Properties and  Methods

  • Object properties
  • Object methods
  • Object events

Finding  Out More

  • Using VBA’s Help system
  • Using the  Object Browser

Automatically listing  properties and  methods

Unit 5: VBA Sub and Function Procedures

Subs versus Functions

  • Looking at Sub procedures
  • Looking at Function procedures
  • Naming Subs and  Functions

Executing Sub procedures

Executing the  Sub procedure directly

  • Executing the  procedure from the  Macro  dialog  box
  • Executing a macro by using  a shortcut key
  • Executing the  procedure from a button or shape
  • Executing the  procedure from another procedure
  • Executing Function procedures
    • Calling the  function from a Sub procedure

Calling a function from a worksheet formula

Unit 6: Using the Excel Macro Recorder

  • Is It Live, or Is It VBA?
  • Recording Basics
  • Preparing to Record
  • Relative or Absolute?
  • Recording in absolute mode
  • Recording in relative mode
  • What Gets Recorded?
  • Recording Options
  • Macro  name
  • Shortcut key
  • Store  Macro  In
  • Description
  • Is This Thing Efficient?

Unit 7: Essential VBA Language  Elements

Using Comments in Your VBA Code

Using Variables, Constants, and  Data Types

  • Understanding variables
  • What are VBA’s data types?
  • Declaring and  scoping variables .
  • Working  with constants
  • Pre-made constants
  • Working  with strings
  • Working  with dates

Using Assignment Statements

  • Assignment statement examples
  • About  that equal sign
  • Smooth operators

Working  with Arrays  

  • Declaring arrays
  • Multidimensional arrays
  • Dynamic  arrays

Using Labels

Unit 8: Working with Range Objects

A Quick Review

Other Ways to Refer to a Range

  • The Cells property
  • The Offset property

Some Useful Range Object Properties

  • The Value property
  • The Text property
  • The Count  property
  • The Column  and  Row properties
  • The Address property
  • The HasFormula property
  • The Font property
  • The Interior property
  • The Formula property
  • The NumberFormat property

Some Useful Range Object Methods

  • The Select  method
  • The Copy and  Paste methods
  • The Clear method

The Delete method

Unit 9: Using VBA and Functions

What Is a Function?

Using Built-In VBA Functions

VBA function examples

VBA functions that do more than return a value

Discovering VBA functions

Using Worksheet Functions in VBA

  • Worksheet function examples
  • Entering worksheet functions
  • More about using  worksheet functions

Using Custom Functions

Unit10: Controlling Program Flow and Making Decisions

Going with the  Flow, Dude
The GoTo Statement
Decisions, Decisions

  • The If-Then structure
  • The Select  Case structure

Knocking  Your Code for a Loop

  • For-Next loops
  • Do-While loop
  • Do-Until loop

Using For Each-Next  Loops  With Collections

Unit 11: Automatic Procedures and Events

Preparing for the  Big Event

  • Are events useful?
  • Programming event-handler procedures

Where  Does the  VBA Code Go?
Writing  an Event-Handler Procedure
Introductory  Examples

  • The Open  event for a workbook
  • The BeforeClose event for a workbook
  • The BeforeSave event for a workbook

Examples of Activation Events

  • Activate and  deactivate events in a sheet
  • Activate and  deactivate events in a workbook
  • Workbook activation events

Other Worksheet-Related Events

  • The BeforeDoubleClick event
  • The BeforeRightClick event
  • The Change event

Events Not Associated with Objects

  • The OnTime  event

Keypress events

Unit  12: Error-Handling Techniques

Types of Errors
An Erroneous Example

  • The macro’s not  quite perfect
  • The macro is still not  perfect
  • Is the  macro perfect yet?
  • Giving up on perfection

Handling Errors Another Way

  • Revisiting the  EnterSquareRoot procedure
  • About  the  On Error  statement

Handling Errors: The Details

  • Resuming after  an error
  • Error  handling in a nutshell
  • Knowing when  to ignore errors
  • Identifying specific errors

An Intentional Error  

Unit  13: Bug Extermination Techniques

Species of Bugs
Identifying Bugs
Debugging Techniques

  • Examining your  code
  • Using the  MsgBox function
  • Inserting Debug.Print statements
  • Using the  VBA debugger

About  the  Debugger

  • Setting  breakpoints in your  code
  • Using the  Watch window
  • Using the  Locals  window

Bug Reduction Tips

Unit 14: VBA Programming Examples

Working  with Ranges  

  • Copying  a range
  • Copying  a variable-sized range
  • Selecting to the  end  of a row or column
  • Selecting a row or column
  • Moving a range
  • Looping through a range efficiently
  • Looping through a range efficiently (Part II)
  • Prompting for a cell value
  • Determining the  selection type
  • Identifying a multiple selection

Changing Excel Settings

  • Changing Boolean settings
  • Changing non-Boolean settings

Working  with Charts

  • AddChart versus AddChart2
  • Modifying  the  chart type  
  • Looping through the  ChartObjects collection
  • Modifying  chart  properties
  • Applying  chart formatting

VBA Speed Tips

  • Turning off screen updating
  • Turning off automatic calculation
  • Eliminating those pesky alert messages
  • Simplifying object references
  • Declaring variable types

Using the  With-End With structure

Unit  15: Simple Dialog Boxes  

UserForm Alternatives
The MsgBox Function

  • Displaying a simple message box
  • Getting  a response from a message box
  • Customizing message boxes

The InputBox Function

  • InputBox syntax
  • An InputBox example
  • Another type  of InputBox

The GetOpenFilename Method

  • The syntax for the  GetOpenFilename method
  • A GetOpenFilename example

The GetSaveAsFilename Method
Getting  a Folder  Name
Displaying Excel’s Built-in Dialog Boxes



Who Should Attend

This course is intended for:

  • Programmers/Developers
  • Managers in small / medium enterprises
  • People who work with Excel intensively in positions where automation of Excel tasks  is imperative such as Accountants, Actuaries, Financial Controllers, Project Managers, Scientific personnel that wishes to build macros to automate routines


Objectives

Upon completion the participants will be able to

  • Use an Object Oriented Language
  • Program in Visual Basic for Applications
  • Create Procedures and Functions
  • Use Decision Based Logic
  • Use Loops
  • Create Forms


Prerequisites
Participants should have a very good knowledge of Excel.



Find Training Courses
Name
Type
v
Category
v
 

© EDUCATION & INFORMATION TECHNOLOGY CENTRE (EDITC). All Rights Reserved. Developed by CMP POLYMEDIA LTD