Microsoft® Excel VBA Level I: Macros, Constants and Variables

Microsoft® Excel VBA Level I: Macros, Constants and Variables is divided in two parts.  Part 1 will demonstrate to the user how to effectively coordinate macros along with exposing them to the basic foundations of Visual Basic programming. The macro recorder will be used to create the base code that will be edited using coherent VB fundamentals.

Objective: Upon completion of this course the user will be able to create a blueprint for developing a macro project by applying the principles outlined. They will also discover various ways to execute macro programs such as using keystrokes and toolbar icons.

• Exploring Macros
  1. Macro security
  2. Construction
  3. Naming
• Using the Macro Recorder
• Exploring Visual Basic code
  1. The VB Editor
  2. Editor options
  3. Modularized programming
• Nesting procedure code
• Macro execution
  1. Keyboard shortcuts
  2. Quick Access Toolbar button
  3. Auto_Open and Auto_Close
• Macro distribution
  1. Saving as a .bas file
• Exporting and importing

Microsoft® Excel VBA: Variables and Constants will delve deeper into the core components of the Visual Basic language. The internal framework of code structure will be discussed, including objects, properties, and methods. The user will also be exposed to variable and constant declaration as well as the scope and lifetime of those variables. This course will also cover blanket methods for trapping program errors.

Objective: Upon completion of this course the user will effectively construct code blocks where explicit variables will be used to control user input values as well as define and use constant values in VBA procedures. Basic error-trapping techniques will also be explored.

• Structuring VBA code
  1. Objects, Properties, and Methods
     1. The Offset Property
     2. Using Command buttons

• Declaring Variables
  1. Data Types
  2. The DIM statement
  3. Scope and Lifetime
  4. Public and Private Modules
• Declaring Constants
• The InputBox function
  1. Returning variable values to a spreadsheet
• Error Trapping
  1. The On Error statement
  2. Creating an error subroutine

IMPORTANT INFORMATION ABOUT PARTICIPATION:  As part of our sponsorship with NASBA, we have agreed to provide at least three (3) instances of engagement per CPE hour (50 minutes).  These may come in the form of open-ended questions, hand-raising, polling, and other techniques.  You MUST answer or respond to all three opportunities to engage in order to receive credit for that CPE hour.   Knowledge Source Inc. retains these responses after all CPE classes for audit purposes.

Knowledge Source Inc. is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.