ComboProjects

Understanding matters

Learning Excel Macros

busy_beesLearning Macros in Microsoft Excel

This 3 day course is designed for users with a solid background in Excel who want to learn how to create new solutions to their day-to-day challenges. Many institutions offer a two day course on Excel macros (even one day!) but that just teases their appetite. Optionally this three day course can be given in two separate steps, two consecutive days in the first week and the last day in the following week.

Objectives

At the end of this workshop the participants will have seen how to create their own custom functions and procedures (macros). They will be able to create a library of custom tools to be used in any workbooks. They will have seen how to create a simple form to use as an interface to their application.

Day 1

  • Record an expense report macro and optimize it.
  • Add new functionality to the report: Request employee name, add date report in regenerated.
  • Creating and renaming modules
  • Creating Sub procedures without the macro recorder
  • About Function procedure.
  • Working with Worksheets using the SHEETS object.
  • How to use Function in Excel.
  • Using DIM statement to create variables and data type.
  • Conditional statements – IF-THEN-ELSE and SELECT-END SELECT.

Day 2

  • Introduction to loops.
  • The DO WHILE – LOOP statement.
  • The OFFSET method.
  • The FOR – NEXT Statement.
  • The FOR EACH – NEXT statement.
  • VBA functions
  • Excel objects
  • Discussion of various important topics: Personal macro workbook, Option Explicit statement, Indentation.
  • Object variables. Practice with various exercises using all that was learned up to this point.

Day 3

  • Brief review of the more conceptual topics discussed in day 1 & 2.
  • Learning about arrays.
  • How to loop through an array.
  • The Append2Database project. This tutorial based project is a complete fictitious solution for importing a text file and modifying it so that it is database compatible. Then it is appended at the bottom of a History database. In real life this this would take 40 minutes to do by hand. The macro completes the task in 2.5 seconds. Duration of project: 2 hours.
  • Creating a Userform. In this module we create a form allowing the user to unhide columns in a worksheet in a way that is very tedious with the user interface.

 Note

In this workshop all participants will be given material allowing them to pursue their development in Excel VBA. It is recommended to bring a memory stick to make a personal copy all the files.