VBA

Learn to automate repetitive Excel tasks with macros and Visual Basic Applications. Learn to debug programs and extend macros with variables and For Next loops. Learn to use Excel functions programatically and create User Defined Functions (UDFs). Learn to work with object variables and array variables within For Each and Do While loops. Courses are hands-on and include practice exercises. Course includes a 240 page manual and access to videos of class online.

Frequently Asked Questions

• What time does class start?
  Weekday classes start at 9am
  Weekend classes start at 10am
• Do I need to bring a computer?
  We provide a Windows computer.
• Do I receive a certificate?
  Yes, upon completion you will receive an attractive gold foil certificate.
• Are there any prerequisites?
  Ability to use a mouse and a keyboard. Knowledge and experience with advanced Excel training topics.
• Where are you located?
  31 West 34th Street. Near Penn Station, NJ Transit, and the N, Q, R, W, B, D, F, 1, 2, 3, A, C, E trains.

About The Instructor

Glenn Harris is a Microsoft Certified Technical Trainer and Office Master Instructor with 20 years of classroom experience. He has published many training manuals on Microsoft products. Excel Class is the only class that offers programmed self-assessment labs and online access to videos of Glenn teaching class (to himself).

VBA Outline

Download a PDF

• Lesson 1: What is VBA
  • ProgrammingTerminology
• Lesson 2: Macros
  • Record
  • Naming Rules
• Lesson 3: Macros Break – Debugging
  • Introduction to the VB Editor
    • Getting Set Up to Code
  • Introduction to Object Oriented Programming
  • The Dot Operator
  • Debug Print
  • Object Browser
  • Debugging
    • Reset
• Lesson 4: Variables
  • Implicit vs. Explicit Declaration
  • Variable Naming Rules
  • Local vs. Global Scope
  • Public vs. Private Scope
  • Constants
  • Variable Substitution
  • Variable Concatenation
  • Object Variables
  • Array Variables
• Lesson 5: VBA to Navigate
  • Ranges
    • Select vs. Activate
    • Value
    • Offset
    • Autofill
    • Find the Last Row
    • Name
  • Cells
    • ActiveCell
  • Sheets
    • Navigate
    • Add
    • Name
    • Hide
  • Copy and Paste
  • Workbooks
    • Open One Workbook
    • Open Many Workbooks
    • New Workbooks
• Lesson 6: VBA Program Control Structures
  • Anatomy of a Program
    • Call Statements
  • Pseudocode as an Outline
  • Decision Control
    • If Then Else
    • Select Case
  • Flow Control
    • For Next
    • For Each
    • Do While
    • Do Until
    • With End With
  • Loops:
    • Loop With Counters
    • Loop With Step
    • Loop Backwards
    • Loop Through A Scenario
    • Loop Through Rows
    • Loops Within Loops
    • Loop and Make Decisions With If
    • Loop to Parse
    • Loop to Concatenate
• Lesson 7: Error Handling
  • Run Time Errors – Level 1
    • On Error
    • Ignore Errors
  • Run Time Errors – Level 2
    • Create an External Text File
• Lesson 8: Speed Up VBA Code
  • Improve Performance of VBA Code
• Lesson 9: Functions
  • Intrinsic Functions
    • MsgBox
    • InputBox
    • FileDialog
    • Now
    • Date
    • Text Functions
    • Logical Functions
    • VBA Only Functions
  • Non Intrinsic Functions
    • Write Excel Functions in Cells
    • Call Excel Functions in RAM
    • Write User Defined Functions
      • Save As Add-In
    • Functions in Loops
• Lesson 10: Workbook Events
  • Double Click Event
  • The Intersection Operator
• Lesson 11:
  • Object Variables
  • Array Variables
  • Do Loops and For Each Loops With Object and Array Variables
• Lesson 12: Indefinite Loops
  • Emergency Exit
• Lesson 13: Putting It All Together
• Lesson 14: Excel Resources
• Appendices:
  • VBA Passwords and Digital Signatures
  • VB Editor Keyboard Shortcuts
  • Key VBA Commands
  • Save As An AddIn
  • Windows Task Scheduler