Visual Basic Application (VBA) for Excel Training

Visual Basic Application (VBA) for Excel Training

ln this Visual Basic (VBA) for Excel Training, the trainer will introduces object-oriented programming and show how to automate routine tasks and provide custom functionality to enhance Excel performance and efficiency. This course introduces the Visual Basic for Applications programming language, covers creating subroutines and functions to hold code.

Topics include:

  • Working in the Visual Basic Editor
  • Adding code to a macro
  • Creating, exporting, and deleting code modules
  • Declaring and using variables
  • Managing variable scope
  • Defining arrays
  • Managing workbooks and worksheets with VBA
  • Repeating tasks with loops
  • Debugging VBA code
  • Cutting, copying, and pasting cell data
  • Running and triggering event procedures

Module 1 Introduce Visual Basic for Applications (VBA)

Introducing object oriented programming
Examining Excel object model
Working in the Visual Basic Editor
Setting VBA project properties
Creating, exporting, and deleting code modules
Creating a subroutine or function
Controlling procedure scope and visibility
Adding comments to the code
Running a VBA routine
Adding code to a recorded macro
Module 2: Defining Variables, Constants. and Calculations

Introducing Excel VBA data types
Declaring variables and requiring declarating before use
Managing variable scope
Defining static variables and constants
Creating a calculation using mathematical operators
Defining arrays
Defining and using object variables
Streamlining code reference
Module 3: Adding Logic to VBA Code

Repeating a task using a For Next loop
Stepping through all items of a collection Using the For Each loop
Repeating a tasking using the Do loop
Performing a task when conditions are met using the If – Then
Selecting which item to take using the Case statement
Module 4 Debugging Your VBA Code

Managing errors using On Error statements
Stepping through a subroutine or function
Setting breakpoints in the code
Verifying output using the immediate window
Watching a value in a routine
Module 5 Managing Workbook Elements and Data in VBA

Writing a value to a cell
Cutting, copying and pasting cell elements
Finding values in cells
Referring to cells using the Offset function
Concatenating text strings
Returning part of a string
Managing worksheets with VBA
Managing workbooks with VBA
Module 6 Adding Advanced Elements to Your Workbook

Turning off screen updating within a macro
Using worksheet function in a macro
Acquiring values using an input box or message box
Calling a subroutine from another subroutine
Module 7 Using Excel Events in Your VBA Code

Running a procedure when you open, close or save a workbook
Running a procedure when a cell range changes
Triggering a procedure using a specific key sequence