After this training course, the participant will automate:

• data processing and formatting in spreadsheets,
• checking the correctness of data and data calculations/processing (such as user errors and MS Excel errors),
• data corrections (such as the most common user errors).

Profile of participants

This training course is intended for individuals who, among other things:

• work on large amount of data in spreadsheets,
• perform numerous repetitive operations (e.g., copy data or correct errors)
• create repetitive reports and calculations based on changing data

 This course is specifically designed for analysts, accountants, warehouse staff, and employees involved in production support and organization.

Prerequisites

Required knowledge: very good practical knowledge of MS Excel (participants should use intensively MS Excel in daily work).

• 3 days, 24 training hours

1.   Start of the training course – organizational matters
2. Macros – task automation in worksheets
   2.1 Registering and running macros
   2.2.   Buttons for running macros in a worksheet
3. VBA code management
   3.1.  VBA editor window structure
   3.2.   Archiving and transferring VBA code – using VBA modules
   3.3.   Navigation in VBA code
4. Adding macros to MS Excel interface  [optionally]
   4.1.   Toolbars
5. Editing VBA code
   5.1.   Modifying VBA code
   5.2.   Changing the basic settings of VBA editor
   5.3.   Colors in VBA editor
6. Introduction to programming
   6.1.   Procedures
   6.2.   Variables – declaring variables correctly and preventing non-declaration errors
   6.3.   VBA data types
   6.4.   Operators
   6.5.   Exercises
7. Program testing
   7.1.   Stepwise startup
   7.2.   Quick preview of processing results
   7.3.   Sensors – tracking variable values
8. Text processing
   8.1.   Joining text strings (concatenation)
   8.2.   Text string processing functions
9. Data processing control
   9.1.   Conditional statement and building conditions correctly
   9.2.   “For” loop with a counter + workshops
   9.3.   Loop that processes collections of objects (“For Each”) + workshops
   9.4.   Loop with exit condition (“Do ... Loop”) + workshops
   9.5.   Stop/exit statement (“Exit”)
10. VBA in spreadsheet
    10.1.    Objects in spreadsheets
    10.2.    Properties and methods
    10.3.    Object hierarchy in MS Excel
    10.4.    Basic object syntax
    10.5.    Classes and collections
11. Operations on cells and cell ranges
    11.1.   Operations on an active cell
    11.2.   Navigating to active cell
    11.3.    References to cells and cell ranges
12. Returning user’s data
    12.1.    How to use the “InputBox” function
    12.2.    Converting data types
13. End of the training course (test + discussion of the results, training evaluation)

Training methods

Lecture + workshops (amount proportional to lecture) + workshops check (individual) + workshops discussion.

• training course “VBA in MS Excel – Intermediate Level. Worksheets, workbooks, files”
• further integrated VBA training courses (includes 4 training courses in total)