Profile of participants

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

• analyze large amount of data collected in spreadsheets,
• are in charge of stock level control and logistics (using spreadsheets),
• prepare extensive lists and reports based on pivot tables.

It is indented, inter alia, for analysts, accountants, warehousemen, production support/organization employees.

Prerequisites

• very good practical knowledge of MS Excel (participants should use intensively MS Excel in daily work),
• taken training course “VBA in MS Excel – Beginners Level. Basics of computer programming” and “VBA in MS Excel – Intermediate Level. Worksheets, workbooks, files” (or knowledge and background of the training material)

Duration:

• 3 days, 24 training hours 

Detailed training program

1.      Start of the training course – organizational matters

2.      Macros – task automation in worksheets

2.1.   Registering and running macros

2.1.1.     How to run macro-operated card

2.1.2.     How to set security level

2.1.3.     How to prepare environment for macros

2.1.4.     How to register macro

2.1.5.     How to run macro

2.1.6.     How to save worksheet with macros

2.1.7.     Workshops 

2.2.   Buttons for running macros in a worksheet

2.2.1.     How to add a run macro button to worksheet

2.2.2.     Workshops

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.      [optionally] Adding macros to MS Excel interface

4.1.   Toolbars

4.1.1.     How to activate worksheet with individual macros

4.1.2.     How to add a run macro button to the quick access toolbar

4.1.3.     How to modify the ribbon – add a sheet with user’s macros

4.1.4.     Workshops 

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.1.1.     How to join text strings

8.1.2.     How to break a line of text in code

8.1.3.     How to break an output line of text (e.g., in a message window)

8.1.4.     How to add special characters (e.g., quotation marks) to the output text

8.1.5.     Workshops

8.2.   Text string processing functions

8.2.1.     How to check the length of a supplied text string (“Len” function)

8.2.2.     How to return the start of a supplied text string (“Left” function)

8.2.3.     How to return the end of a supplied text string (“Right” function)

8.2.4.     How to return the middle of a supplied text string ("Mid" function)

8.2.5.     How to remove duplicate spaces at the start and end of a text string (“Trim” function)

8.2.6.     How to convert text string to upper-case (“Ucase” function)

8.2.7.     How to convert text string to lower-case (“Lcase” function)

8.2.8.     How to find a substring in a string (“InStr” function)

8.2.9.     How to check if expression is a number ("IsNumeric" function) + VBA function errors

8.2.10.  Workshops 

9.      Data processing control

9.1.   Conditional statement and building conditions correctly

9.1.1.     How to execute only the true condition with one statement only (one-line syntax)

9.1.2.     How to execute only the true condition with multiple statements

9.1.3.     How to use complex condition

9.1.4.     How to execute both the true and false conditions

9.1.5.     How to check multiple criteria sequentially

9.1.6.     Workshops 

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.1.1. How to return cell value

11.1.2. How to save cell value

11.1.3. How to return the value displayed in a cell

11.1.4. How to format cell with a style

11.1.5. How to clear cell

11.1.6. How to return cell address

11.1.7. How to return column/line number for an active cell

11.2.   Navigating to active cell

11.2.1. How to move to another cell

11.2.2. How to prevent the user from leaving a worksheet

11.2.3. How to return the content from the cell below

11.2.4. Workshops 

11.3.    References to cells and cell ranges

11.3.1. How to select cell with a given address

11.3.2. How to select cell range with a given address

11.3.3. How to select discontinuous cell range

11.3.4. How to select a named column (e.g., A)

11.3.5. How to select a numbered column

11.3.6. How to check whether a column is hidden and how to hide/unhide a column or line

11.3.7. Hot to fit column width to content

11.3.8. How to select a numbered line

11.3.9. How to fit line height to content

11.3.10. How to set standard line height

11.3.11. How to count the number of columns/lines in a cell range

11.3.12.  How to return cell address with specified coordinates (line number and column number)

11.3.13.   Workshops

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 mathods

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

DOWNLOAD FILE

• VBA language in MS Excel