VBA in MS Excel – Intermediate Level Worksheets, workbooks, files

    Czas trwania
    Godzina rozpoczęcia
    Stopień trudności

      VBA in MS Excel – Intermediate Level Worksheets, workbooks, files


      Remote and stationary training options

      About training

      The training is designed for people who primarily want to extend the knowledge and skills acquired in the basic training and who need to automate the work of processing data that is contained or placed in multiple sheets and/or notebooks.

      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:

        • Automate repetitive tasks – such as manipulation of worksheets and files (creation/generation and deletion) and worksheet formatting
        • Increase proficiency in creating and implementing efficient algorithms
        • Develop a set of ready-to-use procedures – useful tools to apply in daily work

        Skills 

        After this training course, the participant will automate:

        • preparation and formatting of worksheets with repetitive calculation structures (operations on formulas and worksheets, creating of custom functions, among other things), 
        • download and saving of data in multiple worksheets and/or workbooks,
        • segregation and preparation of data/calculation sets for various stakeholders (operations on worksheets and workbooks, among other things).
      3. Is this training for you?
      4. 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).
      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.      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

      9. Development path
        • training course “VBA in MS Excel – Advanced Level. Databases in Excel spreadsheet”.
        • training course “VBA in MS Excel – Advanced Level. Data analysis”
      What will you gain from the training?

      Training objectives

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

      • Automate repetitive tasks – such as manipulation of worksheets and files (creation/generation and deletion) and worksheet formatting
      • Increase proficiency in creating and implementing efficient algorithms
      • Develop a set of ready-to-use procedures – useful tools to apply in daily work

      Skills 

      After this training course, the participant will automate:

      • preparation and formatting of worksheets with repetitive calculation structures (operations on formulas and worksheets, creating of custom functions, among other things), 
      • download and saving of data in multiple worksheets and/or workbooks,
      • segregation and preparation of data/calculation sets for various stakeholders (operations on worksheets and workbooks, among other things).
      Is this training for you?

      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

      Duration:

      • 3 days, 24 training hours
      Detailed training program

      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

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

      Podobne tematy

      Masz pytania? Skontaktuj się z nami!

      31-864 Kraków

      ul. Prof. Michała Życzkowskiego 33