Microsoft Excel – VBA for Aplications

Obsah školení

At the conclusion of this course you should be able to:

Automatization in MS Excel

• recording and coding of macros

Introduction to macro-recording tool

• conception
• limits of the tool - what is impossible to record

Sharing of macros

• Personal macro workbook
• Startup folder of MS Excel

Examples explaining the possibilities of recorder

• how to prepare general macro (ie independent on number of rows of table)
• automated formatting of tables
• automated processing of data
• automated generation of formulas
• automated data conversions
• absolute and relative references

How to start execution of recorded macro

• Macro manager
• Keyboard shortcut
• Button in the ribbon
• Button in Worksheet

Introduction to VBA world

• Enviroment of VBA editor
• where are macros stored, how to find problematic commands and how to fix the problems (stepping into macro line by line)

Excel object model

• Basic characteristics of objects
• Properties and methods
• Object Browser

Program Flow Control

• With
• If … Then
• For … Next

Object Range

• Basic work with the Range object
• Working with the currently selected area
• Inserting values
• Inserting formulas

Variables and procedures

• Declaring variables
• Data Types

Communication with user

• Message box
• Input box

Catching errors

• Run-time error
• Err object
• Inline error handling

Debugging

• Types of errors
• Break mode & Program stepping

Object Application

• Application object and MS Office
• Selected properties and methods of Excel.Application object

Termín

17. - 18.5. 2018