FB Twitter Linkedin Instagram Excel with VBA - Itronix Solutions

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

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

Using the macro recorder

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

Running a macro

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

Problems with the recorder

  • a.    Unnecessary code added
  • b.    Difficult to troubleshoot
  • c.     Uses absolute references to cells

Introduction to Object-Oriented Programming (OOP)

Using Message Boxes

Ranges and Selections

  • a.    Named ranges
  • b.    Range references
  • c.     Relative ranges
  • d.    Cells, offset, resize, columns, rows, union, intersect, regions
  • e.    Referencing tables

Looping / Flow Control

  • a.    For / Next loops
  • b.    Do loops
  • c.     While loops
  • d.    For / Each loops

Selection Statements

  • a.    If / Then / Else
  • b.    Select / Case

A1 vs. R1C1 references

  • a.    Toggling style references
  • b.    Using Excel formulas
  • c.     Using Arrays

 Excel Names

  • a.    Global vs. local variables
  • b.    Adding / Hiding / Deleting Names
  • c.     Checking Name existence
  • d.    Comments
  • e.    Types of Names

Event Programming

  • a.    Event levels
  • b.    Using Events
  • c.     Workbook / Worksheet / Chart events
  • d.    Application-Level events


  • a.    Declaring single-dimension and multi-dimension arrays
  • b.    Propagating an array
  • c.     Retrieving data from an array
  • d.    Reasons and examples of why to use arrays
  • e.    Dynamic arrays
  • f.      Passing arrays
  • i.     Reference
  • ii.     Argument

Classes, Records, and Collections

  • a.    Class Modules
  • b.    Trapping Events
  • c.     Custom Objects
  • d.    Using collections
  • e.    User-Defined Types
  • f.      Custom Properties

User Forms

  • a.    Interaction methods
  • b.    Creating / Calling / Hiding a Userform
  • c.     Programming a Userform / Controls
  • d.    Verifying Data Entry
  • e.    Filenames

Data Mining with Advanced Filter

  • a.    Replacing Loops
  • b.    Extracting lists of values
  • c.     Criteria Ranges

Pivot Tables

  • a.    Introduction and understanding Pivot Tables and versions
  • b.    Examples
  • c.     Filtering Datasets
  • d.    Using the Data Model

19. Charts

  • a.    Creating and customizing charts
  • b.    Combo charts
  • c.     Advanced charts
  • d.    Exporting charts as graphics
  • e.    Pivot charts

Conditional Formatting

  • a.    Data Visualization
  • b.    Data Bars, Color Scales, Icon Sets
  • 21. Sparklines

Reading from and writing to the Web

Text file processing

Automating Word functions

Handling errors

Customizing the ribbon

Creating Add-Ins