Targeted Audience:
This course is aimed at individuals with a basic grounding in Excel VBA and an advanced knowledge of Microsoft Excel to develop skills with introducing more sophisticated automation into their workflows.
Course Objective:
- Variable types
- Using object variables to represent worksheets and workbooks
- Using count-based (For-Next) and conditional based (Do-Until, Do-While) loops
- If-Then-Else-End If and Select Case statements
- With blocks
- Using Range and Cells objects
- Benefits
- Upon completion of the course delegates will have a good understanding of the major components of VBA.
- Delegates will expand their reach by being able to use VBA to communicate with other Office applications, such as Word.
- In addition, the course also covers working with data from database applications such as Access, and manipulating and presenting the data.
Course Outline:
Unit 1: The Excel Object Model
- Exploring the Range object in detail
- The versatile Current Region object
- Working with collections of Workbooks and Worksheets
- Manipulating Charts through VBA
- Manipulating PivotTables through VBA
Unit 2: Arrays:
- Efficient variable storage with arrays
- Array optimization
- Dynamic arrays
- The Array function
Unit 3: Triggers and Events:
- Running macros automatically
- Executing macros on a timer
- Associating macros with other Workbook events
Unit 4: Advanced Parameters:
- Passing parameters by reference and by value
Unit 5: Working with Text Files:
- Importing text files
- Exporting text files
- Using the FileStream object
Unit 6: Linking with Office:
- Connecting to other Office applications
- Working with other Office applications
Unit 7: Linking to data sources using ADO:
- Understanding ActiveX Data Objects
- Using Excel to communicate with other data sources
- Understanding the connection string
- Adding, reading, modifying and deleting data
Unit 8: Add-Ins:
- How macro security works
- Password protecting your code
- Distributing macros via an Add-In
Barcelona (Spain)
20 - 24 May 2024
5500 Euro
Paris (France)
10 - 14 Jun 2024
5500 Euro
Barcelona (Spain)
10 - 14 Jun 2024
5500 Euro
Online
07 - 11 Jul 2024
2500 Euro
Vienna (Austria)
15 - 19 Jul 2024
4900 Euro
London (UK)
22 - 26 Jul 2024
5800 Euro
Lisbon (Portugal)
05 - 09 Aug 2024
4900 Euro
Cairo (Egypt)
18 - 22 Aug 2024
3500 Euro
Online
25 - 29 Aug 2024
2500 Euro
Amsterdam (Netherlands)
26 - 30 Aug 2024
5500 Euro
Istanbul (Turkey)
01 - 05 Sep 2024
5000 Euro
London (UK)
02 - 06 Sep 2024
5800 Euro
Dubai (UAE)
08 - 12 Sep 2024
4500 Euro
Casablanca (Morocco)
09 - 13 Sep 2024
3000 Euro
Brussels (Belgium)
09 - 13 Sep 2024
5200 Euro
Boston (USA)
23 - 27 Sep 2024
5500 Euro
Dubai (UAE)
29 Sep - 03 Oct 2024
4500 Euro
Munich (Germany)
30 Sep - 04 Oct 2024
4900 Euro
Brussels (Belgium)
30 Sep - 04 Oct 2024
5200 Euro
Istanbul (Turkey)
06 - 10 Oct 2024
5000 Euro