VBA w MS Excel – Advanced Level Data analysis

    Czas trwania
    Godzina rozpoczęcia
    Stopień trudności

      VBA w MS Excel – Advanced Level Data analysis


      Remote and stationary training options

      About training

      The training is designed for people who have basic knowledge and skills in using the VBA language to work with spreadsheets, and who are primarily concerned with the collection of data using a variety of surveys/questionnaires (paper or electronic) and/or the graphical presentation of survey results/calculations.

      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 the mechanism supporting data analysis (such as autofilter, pivot tables and date/time functions)
        • Understand chart designs created in spreadsheets
        • Enhance skills in creating reliable and effective algorithms 
        • Develop 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:

        • analyze and model data using pivot tables,
        • analyze data based on date/time, 
        • visualize the status based on the data collected in a worksheet (such as, “warning status” and “emergency status” of inventory, company resource loads, etc.),
        • analyze databases created in spreadsheets,
        • present calculation results using diagrams.
      3. Is this training for you?
      4. 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)
      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.      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

      9. Development path
        • VBA language in MS Excel
      What will you gain from the training?

      Training objectives

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

      • Understand the mechanism supporting data analysis (such as autofilter, pivot tables and date/time functions)
      • Understand chart designs created in spreadsheets
      • Enhance skills in creating reliable and effective algorithms 
      • Develop 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:

      • analyze and model data using pivot tables,
      • analyze data based on date/time, 
      • visualize the status based on the data collected in a worksheet (such as, “warning status” and “emergency status” of inventory, company resource loads, etc.),
      • analyze databases created in spreadsheets,
      • present calculation results using diagrams.
      Is this training for you?

      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

      Duration:

      • 3 days, 24 training hours 
      Detailed training program

      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

      Development path
      • VBA language in MS Excel

      Podobne tematy

      Masz pytania? Skontaktuj się z nami!

      31-864 Kraków

      ul. Prof. Michała Życzkowskiego 33