VBA w MS Excel – Advanced Level Databases in Excel spreadsheet

    Czas trwania
    Godzina rozpoczęcia
    Stopień trudności

      VBA w MS Excel – Advanced Level Databases in Excel spreadsheet


      Remote and stationary training options

      About training

      The training is designed for people who have basic knowledge and skills in using VBA language in working with spreadsheets, and who work with databases in spreadsheets on a daily basis and need to automate data exchange processes (also with databases created in dedicated technologies - MS Access, Oracle, MySQL, etc.).

      View the training programme

      1. What will you gain from the training?
      2. Training objectives

        This training course is to empower participants’ expertise while doing their job, thus:

        • Understand how to use array variables (used, inter alia, to process quickly millions of records)
        • Understand how dialog boxes (user forms) are built and work
        • Prepare an automated template enhancing its usability with databases
        • 4Develop a set of ready-to-use procedures – useful tools to apply in daily work

        Skills 

        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.
      3. Is this training for you?
      4. 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).
      5. Duration
      6. Duration:

        • 3 days, 24 training hours
      7. Detailed training program
      8. 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

      9. Development path
      What will you gain from the training?

      Training objectives

      This training course is to empower participants’ expertise while doing their job, thus:

      • Understand how to use array variables (used, inter alia, to process quickly millions of records)
      • Understand how dialog boxes (user forms) are built and work
      • Prepare an automated template enhancing its usability with databases
      • 4Develop a set of ready-to-use procedures – useful tools to apply in daily work

      Skills 

      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.
      Is this training for you?

      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

      Duration:

      • 3 days, 24 training hours
      Detailed training program

      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

      Development path

      Podobne tematy

      Masz pytania? Skontaktuj się z nami!

      31-864 Kraków

      ul. Prof. Michała Życzkowskiego 33