Profile of participants

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

• process in spreadsheets the data retrieved from databases and database systems (such as MS Access, Oracle or SAP),
• prepare database statements in spreadsheets,
• optimize the (manual and automatic) processing of large amounts of data,
• collect and compile the results of various researches and surveys.

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

Preparation

Required knowledge:

• very good practical knowledge of MS Excel, 
• 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.     Databases created in spreadsheets

2.1.  Database usage

2.2.  Basic information on databases

2.3.  Database usage automation

2.4.  Restrictions on the use of direct connection to an external database

3.     Data transfer between databases – using CSV files (text files with sequential access)

3.1.  CSV file format

3.2.  General algorithm for processing CSV files

3.3.  Other possibilities of using CSV file support mechanism

3.4.  “Input” mode – uploading data from a file

3.5.  “Output” mode – writing data to a file

3.6.  “Append” mode – adding data to the end of file

3.7.  Workshops

4.     Names of cells and cell ranges

4.1.  Name usage and advantages of using names

4.2.  Rules for creating and using names

4.3.  Operations on names

4.3.1.    How to count names in a workbook

4.3.2.    How to assign name to a cell (with absolute address or in RC notation)

4.3.3.    How to assign name to a cell range (with absolute address or in RC notation)

4.3.4.    How to check name location

4.3.5.    How to check name index in a collection

4.3.6.    How to delete name

4.3.7.    How to determine the number of the last row in a database 

4.3.8.    How to determine the number of the last column in a database 

4.4.  VBA language traps in using cell/cell range names

4.5.  Workshops

5.     Array variables (arrays)

5.1.  Advantages of using array variables

5.2.  Array variable types

5.3.  Rules for building and using array variables

5.4.  Declaring and processing arrays

5.4.1.    How to count array elements from 1

5.4.2.    How to declare an array variable

5.4.3.    How to assign value to selected array element

5.4.4.    How to fill in an array with data

5.4.5.    How to use predefined array bounds in processing (“LBound” and “Ubound” functions)

5.4.6.    How to copy array content to worksheet

5.4.7.    Workshops

5.5.  Two dimensional arrays

5.5.1.    How to declare and use two-dimensional array

5.5.2.    How to use “LBound” and “UBound” functions in a two-dimensional array

5.5.3.    Workshops

5.6.  Dynamic arrays

5.6.1.    How to use dynamic array variable

5.6.2.    Workshops

6.     Custom data types (User-defined types) – for records/data structures

6.1.  Advantages of using structured data types

6.2.  Creating structured data types

6.3.  Using structured data types

6.3.1.    How to use structured data type

6.3.2.    How to use structured data type together with array variable

6.3.3.    Workshops

7.     Communication with user – “MsgBox” function

7.1.  Displaying messages to user

7.2.  Program control

7.3.  Determining the reach of variables (for entire module and for entire project)

8.     Dialog boxes (VBA user forms)

8.1.  Basic rules for creating clear and functional dialog boxes

8.2.  Creating dialog boxes

8.2.1.    How to create and run a dialog box

8.2.2.    How to add a control (such as an exit window button) to a dialog box

8.2.3.    How to assign the [Enter] key to the “OK” button

8.2.4.    How to add a hot key to a button

8.2.5.    How to assign the [Esc] key to the “Cancel” button

8.2.6.    How to program a control from a dialog box

8.3.  Adding text fields

8.3.1.    How to add text field with a label

8.3.2.    How to pass a value from a text field to a variable

8.3.3.    How to program user form initialization (loading)

8.3.4.    How to set access order to components with the [Tab] key

8.4.  Adding check boxes and option fields (option button)

8.5.  Adding a list of values

8.5.1.    How to add a list of values (combo box)

8.5.2.    How to add values to a list

8.6.  Workshops

8.7.  Exporting and importing user forms

9.     Preparing a database template and relevant programming

9.1.  Database workbook-template content

9.2.  Workshops

9.3.  Presentation of sample database template

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. Data analysis”