Menu

Tel: 01423 534630    Email: info@tts-uk.com

Microsoft Excel VBA Advanced

Designed for Excel 365, 2019, 2016, 2013, 2010

Who is this course for?

This course is aimed at individuals with a basic grounding in Excel VBA and an advanced knowledge of Microsoft Excel, who wish to develop their skills with introduce more sophisticated automation into their workflows.

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.

Prerequisites

Attendance of our Microsoft Excel VBA Intermediate course, or equivalent knowledge.

Including the understanding of the following:

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


  • Course content

    The Excel Object Model

    Exploring the Range object in detail
    The versatile CurrentRegion object
    Working with collections of Workbooks and Worksheets
    Manipulating Charts through VBA
    Manipulating PivotTables through VBA

    Arrays

    Efficient variable storage with arrays
    Array optimisation
    Dynamic arrays
    The Array function

    Triggers and Events

    Running macros automatically
    Executing macros on a timer
    Associating macros with other Workbook events

    Advanced Parameters

    Passing parameters by reference and by value

    Working with Text Files

    Importing text files
    Exporting text files
    Using the FileStream object

    Linking with Office

    Connecting to other Office applications
    Working with other Office applications

    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

    Add-Ins

    How macro security works
    Password protecting your code
    Distributing macros via an Add-In

    Appendix: Classes and Objects

    Working with Class Modules

Duration: 2 days
Level: Advanced
Price: £595 + VAT

Payment logos


Book Now Enquire now

Share Course:

Register to attend

    All fields with * are mandatory.