Excel Macro's

This course is currently not scheduled on the open calendar, but can be organized on request.

Number of days

1 day(s)

Audience

This course is aimed at experienced Excel users who wish to automate repetitive tasks in an Excel file.

Prerequisites

A thorough knowledge of Excel is required, especially with regard to the tasks that you want to automate.

Objectives

Gain insight in the Macro Recorder as well as basic competences in the Visual Basic Editor with regard to Excel.

Methods

Classroom training with attention to individual guidance and possibility to ask questions. The participant can immediately put the features of the program into practice with various exercises. Each participant is provided with his own workstation.

Description

In Excel, you can automate tasks through the integrated programming language VBA. However, it is possible to generate this code via a Macro Recorder. This tool allows non-programmers to automate repetitive tasks.

In deze module wordt het opnemen en afspelen van macro’s uitgelegd. Daarnaast worden ook enkele basisvaardigheden van de VBA-editor en van de taal VBA besproken, zodat minimale wijzigingen aan de code kunnen gebeuren.

Contents

  • Introduction
  • Preparation macros
  • Create macros via the Macro Recorder
  • Execute macros
    • Via de Ribbon
    • Via hotkeys
    • Via a button in the Quick Access Toolbar
    • Via a command button in a worksheet
    • Link macros to a graphical object
  • Managing recorded macros
    • Modify options
    • Delete
    • Modify
  • Discussion of the Visual Basic Editor
  • Modification and writing of a code in a recorded macro
    • Readability
    • Variables and constants
    • Expressions (or calculations)
    • Interaction with the user
    • Properties of objects
    • Methods of objects (actions)
  • Some interesting program structures
    • IF THEN
    • SELECT CASE
    • FOR NEXT
    • DO LOOP
  • Very concise discussion of objects, collections, properties and methods
    • Application
    • Workbooks
    • Workbook
    • Worksheets
    • Worksheet
    • Range
    • Cells
  • Copy and relocation of macros