Introduction to VBA for Excel Macros and Visual Basic for Applications

Outline

Audience

This 2 day course is designed to give proficiency in the Visual Basic Editor (VBE), predominantly making use of Excel objects, basic VBA programming, event handling, control structures and debugging tools.

Aims and Objectives

On completion of this course, delegates will be able to:

  • Record and edit a macro
  • Trigger a macro during design mode
  • User control - keyboard commands, quick access toolbars or buttons, shapes and images on the worksheet
  • Use VBA and examine the Visual Basic Editor
  • Design dialog boxes and simple user forms with controls
  • Drop down boxes, command buttons, text boxes and labels
  • Use decision structures to create procedures that make decisions - use loop structures to perform repetitive tasks
  • Create an error handling routine in case things go wrong with VBA code



Content

Introduction to Macros and VBA

  • Review of Naming Cells and Ranges
  • Review of methods for navigating Excel and creating selections
  • Over view of the Developer Tab
  • Purpose of Macros
  • Where to save Macros
  • Using the Recorder to Record a macro
  • Recording a Macro using Relative vs Absolute References
  • Personal macro workbook
  • Introducing Visual Basic for Applications
  • Running a Macro
  • Saving and Opening Files with Macros
  • Editing a Macro in the Visual Basic Editor
  • Understanding the Development Environment
  • Tips for General Typing in VBA
  • Edition and optimising recorded code
  • Removing Selection statements
  • With...End With
  • Running macros: Assigning to Quick Access Toolbar, Shapes, Pictures and Keyboard shortcuts


The VB Environment

  • The Project Window
  • Module Sheet
  • Properties Window
  • Navigating between Excel and the VB Editor
  • Immediate and Watch Window
  • Menus and Toolbars
  • Setting the VB Options


Procedures

  • Program design
  • Structure of a sub routine
  • Creating a Sub routine
  • Key Words
  • Navigate a Module
  • Edit a Module
  • Commenting / Uncommenting
  • Running and Stepping code
  • Exiting Procedures
  • Calling Procedures, run sub routines together
  • Working with Module sheets - renaming, exporting, removing, inserting


Objects, Properties, Methods and Events

  • The VBA Language
  • Understanding Objects Properties, Methods and Events
  • Navigating the Object Hierarchy
  • Understanding Collections
  • Using the Object Browser
  • Working with Properties
  • Working with Methods
  • Creating an Event Procedure


Variables and Expressions

  • The role of Option Explicit
  • Variable Declaration
  • Data Types
  • Variable Scope and Lifetime
  • Public and Private
  • Understanding Constants
  • Use of VBA Constants
  • Naming Conventions


User Defined Functions

  • Creating a Function
  • Understanding Arguments


Object Variables

  • Overview of Object Variables
  • Declaring Object Variables
  • Using the SET statement


Program Flow and Control

  • Decision Structures - Branching
  • IF, Nested IF
    • If-Then-Else
  • AND OR NOT
  • SELECT CASE


Looping Structures

  • Do Loops
  • For Next Loops
  • For Each Next Loops
  • Counter Loops and Collection Loops
  • Conditional Loops


Interactive Macros

  • Using a Message Box
  • Use a Conditional Message Box
  • Use the Inputbox
  • Controlling and Answer to a Message Box


Debugging and Handling Errors

  • Understanding Errors
  • Using Debugging Tools
  • Setting Breakpoints and Using Break Mode
  • Stepping Through Code
  • Trapping Errors with the One Error Statement


Forms and Controls

  • Understanding UserForms
  • Using the Toolbox
  • Designing Forms
  • Working with a Form's Properties, Methods and Events
  • Working with Form and Control, Properties, Methods and Event
  • Working with Form Controls
  • Setting the Tab Order
  • Populating a Control
  • Adding Code to Controls












Show the next months in

Location Dates Time Price exc. VAT   Status
Contact Graham on 01243 216278 or email courses@growtrain.co.uk to book this course
Share This