Profile of participants

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

• work with multiple worksheets and workbooks at the same time,
• create multiple worksheets and/or workbooks with repetitive data/calculation structures,
• prepare similar or identical data sets and/or reports in spreadsheets for many different stakeholders.

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

Preparation

Required knowledge:

• 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” (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.      User-defined functions

2.1.   Using functions

2.2.   Using functions in MS Excel formulas

2.3.   Using functions in VBA code

2.4.   Creating custom functions

2.4.1.     How to create custom function

2.4.2.     How to use custom function in spreadsheet

2.4.3.     How to use custom function in sub procedure

2.4.4.     Workshops

2.5.   Creating add-ins – function library

2.5.1.     How to create add-in using custom functions

2.5.2.     How to activate user’s add-in

2.5.3.     How to uninstall user’s add-in

3.      Formulas – calculation structure in worksheet

3.1.   Inserting of formulas into a spreadsheet

3.1.1.     How to copy an entry to another cell

3.1.2.     How to copy formula to another cell

3.1.3.     How to save an absolute addressing formula in a cell

3.1.4.     How to save formula with spreadsheet function in a cell

4.      Styles

4.1.   Applying styles and cell style types

4.2.   Operations on cell styles

4.2.1.     How to create a named style

4.2.2.     How to specify data type

4.2.3.     How to set text alignment

4.2.4.     How to enable/disable text wrapping

4.2.5.     How to set font, font size and style

4.2.6.     How to set a cell background color

4.2.7.     How to set the security level of cell contents

4.2.8.     How to remove style

4.2.9.     Workshops

5.      Operations on worksheets

5.1.   References to worksheets

5.1.1.     How to check the number of worksheets in a workbook

5.1.2.     How to refer to worksheet by number

5.1.3.     How to check an active worksheet number

5.1.4.     How to check an active worksheet name

5.1.5.     How to refer to worksheet by name

5.1.6.     How to rename worksheet

5.1.7.     How to check if worksheet is hidden (hiding/unhiding of worksheet)

5.1.8.     How to enable/disable worksheet protection (with password)

5.1.9.     Workshops

5.2.   Creating and deleting worksheets

5.2.1.     How to add (create a new) worksheet

5.2.2.     How to add worksheet of specific type

5.2.3.     How to add worksheet before another indicated worksheet

5.2.4.     How to add worksheet at the end of workbook

5.2.5.     How to add a named worksheet 

5.2.6.     How to delete selected worksheet

5.2.7.     Workshops

6.      Operations on workbooks

6.1.   Operations on an active workbook

6.1.1.     How to get an active workbook name

6.1.2.     How to get workbook path

6.1.3.     How to get (previously saved) workbook path and name 

6.1.4.     How to create a new workbook

6.1.5.     How to save workbook under a different name

6.1.6.     How to check the current (default) directory

6.1.7.     How to change the current directory

6.1.8.     How to change directory

6.1.9.     How to save workbook in the specified directory

6.1.10. How to save workbook (in its default location)

6.1.11. How to close workbook

6.2.   Operations on selected workbook

6.2.1.     How to open workbook with a given name

6.2.2.     How to close selected workbook

6.2.3.     How to close workbook and save changes

6.2.4.     How to activate selected workbook

6.2.5.     How to display the content of selected directory

6.2.6.     How to delete file from disk

6.2.7.     Workshops

7.      Worksheet and workbook events

7.1.   Storage location of worksheet and workbook event procedures 

7.2.   Syntax of event procedure control manual

7.3.   Worksheet events

7.3.1.     How to program a worksheet event

7.3.2.     Frequently used worksheet events

7.4.   Workbook events

7.4.1.     How to program a workbook event

7.4.2.     Frequently used workbook events

7.4.3.     Workshops

8.      Error detection and handling

8.1.   Run-time error catching and handling

8.1.1.     How to handle run-time error in VBA compiler

8.1.2.     How to handle run-time error – proceedings algorithm

8.2.   Run-time error catching possibilities in code

8.2.1.     How to handle run-time error (in the entire program)

8.2.2.     How to handle local run-time error

8.3.   Elements of run-time error handling

9.      Useful mechanisms and structures – selection

9.1.   Timing (“Timer” function)

9.2.   Procedure optimization using “ScreenUpdating” function

9.3.   Code protection against preview and modification

9.4.   Using With ... End With statement to increase the clarity and speed of procedures

9.5.   Using indicators for single objects

9.6.   Running dialog boxes of an application

9.7.   Running another application

10.  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

• training course “VBA in MS Excel – Advanced Level. Databases in Excel spreadsheet”.
• training course “VBA in MS Excel – Advanced Level. Data analysis”