Developing Macros for Microsoft Excel Course Calendar (1 days)

Call for upcoming dates or on-site delivery.
1 Includes airfare from anywhere in the continental United States and some parts of Canada as well as accommodation and shuttle services. 2 Include course and course materials only. 3 Requires a broadband internet connection and microphone. Note that Virtual LIVE courses are led by an instructor, and as such start and end in local time (based on the course location), this my differ from your time zone. For government and education pricing, please contact us.

Recommended Pre-requisites

Prior to attending this excel training course, students should attend our Advanced Microsoft Excel training, or have equivalent experience.

Course Outline/Agenda

This Excel training course covers the following topics:

• Understanding the fundamentals of Visual Basic for Applications (VBA) programming
• Recoding, Storing, and executing excel macros using the macro recorder
  • Creating macro toolbars
  • Adding macros to toolbars and menus
  • Assigning macros to form controls
  • Creating macro events tied to form controls
• Understand the VB Editor and how to use it.
  • The project explorer window
  • The Properties Window
  • The programming window
• Examining and understanding Excel macro code
  • Absolute versus relative references
  • Understanding the pitfalls of macro recoder generated code
• Examining recorded macro code
  • Basics to review code
  • Using debugging tools to review code
  • Watching objects
  • Conditional breakpoints
• Correcting recorded macros
  • Recording issues
  • Removing extraneous selects
  • Locating the last range in a row
  • Using variables to avoid hard-coding values
  • Performing copy and paste operations in a single step
  • Using With to perform multiple actions on a range
  • Speeding up macros
• Working with ranges
  • Referring to ranges
  • Ranges in other sheets
  • Relative ranges
  • Using cells to select a range
  • Using resize to change a range size
  • Other range functions & properties
• Control statements and structures
  • The IF statement
  • The SELECT CASE statement
  • Using For .. Next loops
  • The Do loop
• Creating user defined functions
• Case studies/Function examples
  • Summing cells based on format
  • Extracting numbers from text strings
  • Determining the day of week from the date
  • Separating delimited strings
  • Returning the maximum value in a range

Class Size

On-Site Delivery

This course is available for on-site delivery for groups of 3 or more students.

OTG offers discounted training for on-site courses of more than 3 students - call us at 877-258-8987 (or submit the form on the upper right side of this page) for more information.

Instructional Materials

• OTG Course Guide

View course guide table of contents Requires Adobe Acrobat Reader

Enroll in this course