Contact
Comarch Training

VBA in MS Excel - Intermediate Level

Worksheets, workbooks, files
    Duration
    Start hour
    Level

      Training in VBA

      Comprehensive 3-day Training in VBA

      For Whom?

      The training is designed for people who primarily want to expand their knowledge and skills gained in basic training and they need to automate the work associated with data processing in multiple sheets and / or workbooks.

      You might spend a lot of time in Microsoft Excel, doing your day-to-day job. You know time in Excel moves differently than the time outside Excel. Performing tasks in spreadsheets is time- and energy-consuming when you don’t know any shortcuts. Thankfully, there is a solution to that, and it is called VBA (Visual Basics for Application). Even more luckily, Comarch Training Center offers Excel VBA Online Training on various levels – for beginners and more advanced users. See details about how to get full control over Excel with a 3-day course.

      Read more > VBA Training – Choose A Course to Automate Your Work In MS Excel

      For Whom?

      Training objectives

      In this course, you will learn about important elements of the Visual Basic programming language making your macro code efficient and professional. At the same time you will find out new parts of the Excel object model to do operations on workbooks and worksheets.

      Skills
      During the course you will gain the following skills:
      • Building your own functions’ library
      • Creating macros that automatically run at given Excel event
      • Handling macro errors
      • Using Arrays to accelerate macros
      • Operating on the Excel and text files
      Audience profile
      If you already have some experience writing macros using the main tools of the Visual Basic programming language.
      Requirements
      The training is intended for people who work in Microsoft Excel on a daily basis and already have some experience writing macros using main tools of the Visual Basic programming language.
      Duration
      3 days,  8 teaching hours each.
      Training delivery method
      During the training, 80% of the time is carried out in the form of a practical lecture, in which the instructor presents ready-to-use examples and solutions that the participant can then use in their own macros. The remaining 20% of the training time is allocated to the independent work of the participants. The exercises are prepared in such a way as to consolidate the presented material and encourage creative activity.
      Theoretical and practical knowledge training course VBA
      First steps to define your own function
      • The syntax of the function's definition
      • Difference in between functions and procedures
      • Building Excel formulas with the user defined function
      • Calling functions in the macro code
      • Functions with optional arguments
      • Share your functions by Excel add-in
      Playing with worksheets
      • The Worksheet and the Worksheets objects
      • Creating new sheets and renaming
      • Protecting and unprotecting sheets with a password
      • Making sheets hidden
      • Checking if the given sheet exists
      Some operations on workbooks
      • The Workbook and Workbooks object
      • Create, Save and Close a new workbook
      • Opening existing workbook
      • Use dialog window to ask the macro user pointing the file to open
      • Get the list of files in the given folder
      Basics of events and event's handling
      • What is the event
      • Handling Worksheet's events
      • Handling Workbook's events
      The macro errors. How to cope with them?
      • Types of errors and their causes
      • How to handle errors to tell the macro user what happened
      I/O formatted file operations
      • Reading a text file content and loading it to Excel sheet
      • Write sheet's data to a text file
      • Store information about macro errors in the log file
      What are arrays and why to use them?
      • Array basics
      • One and Two-dimensional arrays
      • Static and dynamic arrays
      • Using dynamic array to define multiple choice filter (with Autofilter)
        And other useful stuff
      • Tips to make the macro running faster
      • Macro run time calculations
      • Setting Excel to run the macro "silent" mode
      Development path
      If you have attended the basic and intermediate trainings of Visual Basic for Applications in Microsoft Excel or you already have experience writing macros for the Excel application, check if you will not find the higher level courses useful (Advanced and Expert).

      Similar trainings

      Consultant:

      Kacper Wiech
      Kacper Wiech

      kacper.wiech@comarch.com

      +48 12 687 78 11

      +48 726 460 695