EX13VBA – Excel 2013 VBA

Enquire/Book this course

  • This field is for validation purposes and should be left unchanged.
Print this page
  • Code: EX13VBA
  • Duration: 2 Days
  • Price per delegate: £525.00 +VAT

Trained over 60000 delegates

Course delivered by industry expert instructors

Highly competitive pricing

Course Description

This course is designed for first time users of Excel Visual Basic, who are familiar with the Windows environment and have used Excel to advanced level.  VBA Programming is designed to guide the programmer in writing procedures using the VBA language.  The procedures you will create in this course are standard using Excel VBA to enable developers to build user defined functions, automate processes.

Target Student:

This course is intended for advanced Microsoft Excel professionals that need to automate Excel spread sheet tasks using Visual Basic for Applications (VBA).

Prerequisites:

Knowledge of the basics of Excel, including how to create, edit, format, and print worksheets that include charts and sorted and filtered data. Students are recommended to first take the following courses or have equivalent knowledge:

  • Microsoft® Office Excel® 2013: Levels 1 - 4

Delivery Method:

Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.

Performance-Based Objectives:

Upon successful completion of this course, students will be able to:

  • Develop a macro.
  • Format worksheets using macros.
  • Create an interactive worksheet.
  • Work with multiple worksheets.
  • Perform calculations.

Course Content

Macros

What is a Macro?
Absolute and Relative Reference
Personal Macro Workbook
Security Issues
Do’s and Don’ts when recording a Macro
Recording Macros
Running Macros
Editing a Macro
Creating  a Button
Creating a Tab
Assign a Shortcut Key
Making Macros Do Complex Tasks
Saving a Macro Enabled Workbook

The Visual Basic Editor

The Visual Basic Interface
The Code Window
Saving and Running a Procedure
Printing a Procedure
Create Event and Macro Procedures
Coding the Open Event Procedure

Working with Object Model

Using the Object Model
Using the Immediate Window

Working with Object Variables

Using Variables
Using Object Variables
Reserving a procedure-level variable
Naming Variables
Use the Set Statement
Entering a Value & Formula into a Range Object
Previewing and Printing a Worksheet Object

Working with String Variables

Reserving a procedure-level string variable
The Assignment Statement
The InputBox Function
Concatenating Strings
The Val Function
The Option Explicit Statement

Working with Date Variables

Reserving a procedure-level date variable
Using an Assignment Statement
Converting Strings to Dates
The OffsetProperty

Working with Numeric Variables

Reserving a procedure-level numeric variable
Using the Assignment Statement
Assigning a numeric expression to a numeric variable

Using the Selection Structure

Using the If..Then..Else Statement
Nesting the If..Then Else Statement
Ucase functions
Logical Operators

Using the MsgBox Function

The Select Case Statement
The MsgBox Function
Values returned by the MsgBox function

Working with Custom Dialog Boxes

Creating Custom Dialog Boxes
Setting the Tab Order /Keyboard access to a control
Assigning accelerator keys
Using Default and Cancel Properties
Adding a Form
Naming a form
The Toolbox
Using the Toolbox to add a control to the Form
Control Properties

Error Trapping

Intercepting and handling run-time errors
The On Error statement and the error-handling routine
The Resume Statement