MS Excel – Automation of reporting and data analysis using VBA

Can you record a macro, know the basic concepts such as variable, object or loop, or maybe you have completed the VBA01 course (it's all there)? If so, this is the course for you. There will be no basis here, we will go a step further. You will learn more about Excel objects such as a workbook, sheet, or the MS Excel application itself You will learn the principles of professional code development You will learn the rules of reacting to errors in the code as well as unforeseen situations. You will learn about the different ways to run macros as well as how to create a form. You will be able to create automation applications.

• At the end of the course, no sheet, workbook or cell automation will be a big problem for you.
• You will be able to create forms, macros that run automatically in response to an event (e.g. changing the content of a cell).
• You will learn how to retrieve data from other files.
• You will learn how to use Excel to start another application (eg Outllook).
• You will know the rules of creating VBA code.
• The knowledge gained during the course will allow you to create automation applications yourself.

A good knowledge of MS Excel environment, completing EX_VBA_01 course or equivalent knowledge.

Training method:

• • Lecture + workshops

English

1. Introduction
   • Quick revision of configuring working environment of Microsoft Excel developer
   • Good practice of programming – structure of the application
2. Quick revision of issues related to declaring variables (forcing declaration)
   • Advantages and disadvantages of modular and global declarations
   • The range of procedures and functions
   • Benefits of parameter transfer
   • Advantages of constants
   • Most important conditional constructions
   • Most important types of loops
3. Tracking the execution (deleting errors)
   • Direct instruction window
   • Detector window
   • Interruption points
   • Tabs and other ways of navigation in bigger applications’ code
   • Troubleshooting
4. Tables
   • one-dimensional
   • multi-dimensional
   • dynamic.
5. Manipulating Microsoft Excel objects from VBA level
   • Microsoft Excel object library
   • different ways to learn the objects
   • examples of using Microsoft Excel most important object:
6. Event procedures
   • of folder and worksheet
   • of a form
   • of application.
7. Forms
8. Creating your own applications
   • automatic data import, completion
   • creating the application automating mail sending
   • managing data, worksheets, folders from VBA level