MS Excel - Advanced VBA Programming (MSEVB2)

Microsoft, Programming

This course is for users who want to advance programming in MS Excel with Visual Basic for Applications (VBA). It builds on basic VBA knowledge and covers the IDE, macro recording, debugging, variables and arrays, modular design and automation patterns for reliable code.

After the course you will control Excel's object model, create user forms, automate file and folder tasks using File System Object (FSO), work with the Windows registry, build database interfaces, produce add-ins and automate other Office apps from Excel.

Location, current course term

Contact us

Custom

Customized Training (date, location, content, duration)

The course:

Hide detail

• Introduction
1. Development environment
2. Recording and editing macros
3. Using variables, static and dynamic arrays
4. Creating procedures and functions
5. Required, optional parameters and parameter arrays
6. Standard VBA functions
7. Branching, conditional statements and loops

• Excel objects
1. References, properties and methods of cells and ranges
2. Working with worksheets and workbooks, their properties and methods
3. Properties and methods of the Application object and collections
4. Merging recorded macros into a final project

• Excel events
1. Using worksheet events
2. Workbook events
3. Application object events

• Using controls, their properties and events
1. Controls on worksheets
2. User forms with controls

• Dialog boxes
1. Working with Excel dialog boxes
2. Creating custom dialog boxes

• Working with files
1. Standard file handling (CSV, TXT, INI, ...)
2. File and folder management with File System Object (FSO)

• Customizing the Excel environment
1. Adding commands to the ribbon
2. Creating context menus
3. Building custom ribbons using XML

• Power Query
1. Creating connections through data transformation
2. Applying Power Query results to new sheets

Assumed knowledge:

Basic VBA programming skills equivalent to a VBA I course.

Recommended previous course:

MS Excel — VBA Programming and Data Transformation Options (MSEVB1)

Schedule:

3 days (9:00 AM - 5:00 PM )

Course price:

476.00 € ( 575.96 € incl. 21% VAT)

Language: