We are proud of our reputation
for delivering the highest quality work

EXCEL WITH VBA


Developer tools in Microsoft Office give you the functionality to create custom applications in Excel. These tools require Visual Basic for Applications (VBA). The developer can create custom spreadsheets, workbooks, controls, and even toolbar add-ins for users.

Visual Basic is a specific Microsoft language, so it integrates into any Microsoft productivity tool. This course focuses on Excel customizations and applications. To create these customizations, you first need to learn macros and what they can do for your projects. We then go into modules and custom coded macros. Visual Basic is an object-oriented language, so we show you how to create custom classes that plug into your Excel modules.


Excel With Vba Training Syllabus


Getting Excel ready

  • Enabling the developer toolbar
  • Understanding file types supported by macros
  • Excel Workbook (.xls) – Macros supported
  • Excel Workbook (.xlsx) – Not supported
  • Excel Macro-Enabled Workbook (.xlsm) – Macros supported
  • Excel Binary Workbook (.xlsb) – Macros supported
  • Configure Excel to save as .xlsm by defaul
  • Macro security
  • Setting trusted locations
  • Enabling Macros by clicking button on toolbar
  • Disable notifications for other locations

  • Using the macro recorder

  • Give macro a name
  • Add a shortcut key
  • Store macro in workbook vs. separate file
  • Adding a description

  • Running a macro

  • Adding a macro to the quick access toolbar
  • Button form control
  • Clipart
  • Shapes and Word Art
  • SmartArt Graphics
  • Visual Basic Editor (VBE)
  • How to access (Alt + F11)
  • Layout of the window
  • Project explorer
  • Button form control
  • MSE Objects
  • How to rearrange the display window
  • Debugging VBA code
  • Add breakpoint
  • Step into, over, continue

  • Problems with the recorder

  • Named ranges
  • Range references
  • Relative ranges
  • Cells, offset, resize, columns, rows, union, intersect, regions
  • Referencing tables

  • Looping / Flow Control

  • For / Next loops
  • Do loops
  • While loops
  • For / Each loops

  • Selection Statements

  • If / Then / Else
  • Select / Case

  • A1 vs. R1C1 references

  • Toggling style references
  • Using Excel formulas
  • Using Arrays


  • Excel Names

  • Global vs. local variables
  • Adding / Hiding / Deleting Names
  • Checking Name existence
  • Comments
  • Types of Names

  • Event Programming

  • Event levels
  • Using Events
  • Workbook / Worksheet / Chart events
  • Application-Level events

  • Arrays

  • Declaring single-dimension and multi-dimension arrays
  • Propagating an array
  • Retrieving data from an array
  • Reasons and examples of why to use arrays
  • Dynamic arrays
  • Passing arrays
  • Reference
  • Argument

  • Classes, Records, and Collections

  • Class Modules
  • Trapping Events
  • Custom Objects
  • Using collections
  • User-Defined Types
  • Custom Properties

  • User Forms

  • Interaction methods
  • Creating / Calling / Hiding a Userform
  • Programming a Userform / Controls
  • Verifying Data Entry
  • Filenames

  • Data Mining with Advanced Filter

  • Replacing Loops
  • Extracting lists of values
  • Criteria Ranges

  • Pivot Tables

  • Introduction and understanding Pivot Tables and versions
  • Examples
  • Filtering Datasets
  • Using the Data Model

  • Charts

  • Creating and customizing charts
  • Combo charts
  • Advanced charts
  • Exporting charts as graphics
  • Pivot charts

  • Conditional Formatting

  • Data Visualization
  • Data Bars, Color Scales, Icon Sets
  • Sparklines

  • Reading from and writing to the Web, Text file processing, Automating Word functions, Handling errors, Customizing the ribbon, Creating Add-Ins