After this training course, the participant will be able to do the following in an automated way:

• get and upload data from/to any databases (such as MS Access, MySQL, Oracle) using CSV files,
• work with user forms (dialog boxes),
• create and maintain databases with a changing number of records,
• manage very large amounts of data.

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 intended, among others, for analysts, accountants, warehouse staff, and employees in production support and organization.

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

• 3 days, 24 training hours

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.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.5.  Two dimensional arrays
   5.6.  Dynamic arrays
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
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.3.  Adding text fields
   8.4.  Adding check boxes and option fields (option button)
   8.5.  Adding a list of values
   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 methods:

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

If you have attended the basic and intermediate Visual Basic for Applications trainings in Microsoft Excel or already have experience writing macros for Excel, check whether the advanced courses (Expert level) would be useful to you.

• VBA in MS Excel – Advanced Level - Data analysis

For detailed information, please reach out to us at training@comarch.com.