Excel VBA (2 day course)

Summary

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

 

Venue details:  
Belfast, 8-10 Amelia Street, Belfast
Start date & time:  
21 October 2019 09:30
End date & time:  
22 October 2019 16:30
Price: €546.58  (€519.25 Member price)
By registering for this course you have accepted the terms and conditions
Training ticket cost:  
20.00
CPD hours:  
13.00
Speaker details
First nameLast name
Mullan Training

Description

Product type:  
CPD course
Category:  
Information technology

Who should attend?

Prerequisites:

  • Navigate, edit and manage Excel workbooks
  • Format worksheets and workbooks
  • Understand the difference between absolute and relative references
  • Understand the basics of Excel formulas and functions, e.g. Sum, text functions
  • Understand the concepts of a Macro and its capabilities

Course overview 

Introduction

  • What is a Macro
  • Recording a Macro
  • Relative References
  • Running A Macro
  • Running A Macro From The Macros Dialog Box
  • Editing A Macro With Visual Basic

Assigning Macros to Buttons and Objects

  • Assigning a Macro to a Button on a Sheet
  • Assigning a Macro to a Drawn Object on a Sheet
  • Assigning a Macro to a Button on a Toolbar
  • Creating A Short-Cut Key To Run A Macro

Visual Basic Editor

  • Objects
  • Methods
  • Properties
  • Programming Tools
  • The Menu Bar

Editing Macros

  • The Visual Basic Editor
  • The Project Explorer
  • The Code Window
  • Procedures
  • Comments
  • Printing a Visual Basic Module

The Range Object

  • The Cells Property
  • The Range Property
  • Flexible Ranges
  • Range Contents

Workbooks and Sheets

  • Activating a Workbook
  • Activating a Worksheet
  • ThisWorkBook

Running Code

  • Run Mode And Design Mode
  • Running Code From The Development Environment

The Project Explorer

  • Using The Project Explorer

The Properties Window

  • Changing A Property

Using Constants

  • Excel Constants
  • Variable Constants

Objects, Properties and Methods

  • Controlling Objects with their Properties
  • Performing Actions with Methods
  • Performing Multiple Actions on an Object

Excel Objects

  • Objects, Properties And Methods
  • Getting & Setting Properties

Variables

  • Using Variables In Routines

Using Visual Basic Functions

  • Inputbox Function
  • Msgbox Function
  • Using A Set Statement

Building Formulae Control Structures

  • If...Then Decision Structures
  • Logical Operators
  • For...Loops
  • Do Loops

Event Handling

  • Vba Editing & Debugging
  • Auto Macros
  • Error Handling

Controls, Dialogue

  • Built-In Dialogue Boxes
  • Predefined Dialogue Boxes
  • Custom Forms
  • Worksheet Forms
  • User-Defined Forms
  • Adding Form Controls
  • Form Control Properties
  • Control and Dialogue Box Events
  • Displaying and Dismissing a User Form
  • Handling Form Controls

Control Structures

  • Conditional Control Structures
  • Looping Control Structures
  • Non-Structured Statements
  • Subprocedures

Key learning outcomes

  • Record and edit a Macro
  • Use different techniques in order to run any macros available such as keyboard commands, the use of the Quick Access Toolbars or Buttons
  • Use VBA and examine the Visual Basic Editor; change the properties of an object; add a module to a project; write the code for a procedure and then run it; and use the Object Browser to search procedures
  • Manipulate data by declaring variables of different data types; combine data by using expressions; use functions to accept input and display output; and declare variables and procedures with the appropriate scope
  • Use decision structures to create procedures that make decisions; and use loop structures to perform repetitive tasks
  • Create an error handling routine in case things go wrong with VBA code