↑ Return to Microsoft Excel

VBA for Excel

Level 1

Duration: 3 days

While Excel is a very powerful and complete tool, you’ll sometimes encounter situations where the program is missing some feature you really need. Using VBA will allow you to solve a variety of problems as well as greatly improving the efficiency of your workflow particularly when faced with repetitive tasks. In this course we will get under the hood of the power of Macros and explore how to unleash the power of Excel to customize any task you may face.

 

Basic Macro Concepts

  • Macros and Sub Procedures
  • Limitations of Recorded Macros

Recording Macros

  • Relative and Absolute Referencing
  • Storing Macros
  • Editing a Macro
  • Debugginga Macro
  • Step Into, Step Over, Step Out
  • Run To Cursor
  • Ways to Run a Macro; Adding to Toolbars, Creating Buttons, Etc.

Visual Basic

  • Defining Projects, Modules, Sub Procedures, and Events
  • The Visual Basic Editor
  • Views and Icons
  • Adding Comments
  • Checking Syntax Errors

Properties

  • Common Properties
  • Using the With Command
  • Properties That Are Objects

Methods

  • Common Methods
  • Events

The Object Browser

  • Object Libraries
  • Classes and Members

Constants

  • Variable Types
  • Text, Dates, Numbers and Variant
  • Declaring Several Variables

Variable Tips

  • Public Vs Private Declarations
  • Variable Scope Vs Visibility
  • Forcing Variable Declaration

User Interaction

  • Input Boxes
  • Message Boxes
  • Building Messages
  • Special Characters To Use When Concatenating
  • Message Box For Output
  • Status Bar Messages

Control of Flow

  • If […] Then Statements
  • If […] Then […] Elseif
  • Running Other Procedures From Within Procedures
  • If Statements with Message Boxes
  • For […] Next Loops
  • Do While […] Loops

Event Trapping

  • Auto_Open And Auto_Close
  • On Error Go To
  • Resume
  • Application Event Properties
  • Other Objects’ Event Properties

Working with Files

  • Extracting File Information–Name, Path, etc.
  • Using VBA to Process Text Files