VBA in MS Excel - Beginners Level. Basics of computer programming

    Czas trwania
    Godzina rozpoczęcia
    Stopień trudności

      VBA in MS Excel – Beginners Level. Basics of computer programming

      Remote and stationary training options

      About training

      The training is designed for people who want to learn how to automate basic spreadsheet work and gain the basic knowledge necessary to further develop their VBA programming skills.

      View the training programme

      What will you gain from the training?

      Training objectives

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

      • Learn the basics principles of the craft and art of programming
      • Acquire the ability to read and understand the code 
      • Understand the basic constructions used in programming
      • Learn the basic types of VBA language errors and how to avoid them
      • Obtain a set of ready-to-use procedures to apply in daily work


      After this training course, the participant will automate:

      • data processing and formatting in spreadsheets,
      • checking the correctness of data and data calculations/processing (such as user errors and MS Excel errors),
      • data corrections (such as the most common user errors).
      Is this training for you?

      Profile of participants

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

      • work on large amount of data in spreadsheets,
      • perform numerous repetitive operations (e.g., copy data or correct errors)
      • create repetitive reports and calculations based on changing data

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


      Required knowledge: very good practical knowledge of MS Excel (participants should use intensively MS Excel in daily work).



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


      Development path
      • training course “VBA in MS Excel – Intermediate Level. Worksheets, workbooks, files”
      • further integrated VBA training courses (includes 4 training courses in total) 

      Podobne tematy