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

If you already have some experience writing macros using the main tools of the Visual Basic programming language.

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.

3 days,  8 teaching hours each.

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.

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

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