Visual Basic for Applications (VBA) in Excel Part 2

The course aims to take your skills in VBA in Excel to the next level.

Applications covered include:

• Learn programming constructs in more depth ;
• Use Excel range objects to dynamically find ranges for reports;
• Use the Excel object model beyond workbooks, worksheets and ranges;
• Create simple applications using multiple subroutines and functions;
• Automate processes such as the creation of charts and pivot tables;
• Use VBA libraries in a simple way to interact with Access, Outlook, PowerPoint and Word.

Software version: Office 2019

The course will expand on your existing knowledge of VBA by introducing a wider range of objects to help you build more complex and robust VBA procedures and functions.

Who is it for?

This course is ideal if you have some basic knowledge of VBA and good knowledge of Excel.

Timetable

This practical short course takes the form of a series of weekly classes that can be taken on weekday evenings or Saturday daytime.

City Short Courses follow the academic year, delivering courses over three terms. These include:

• Autumn - October
• Spring - January
• Summer - April

Benefits

• Delivered by an industry professional
• Taught in small groups
• Awarded a City, University of London certificate

• Review: the Excel API: especially Workbooks, Worksheets and Range objects; Object Browser
• Review generic programming taught in part 1: data variables, If statements, For Each loops and Object variables; introducing Select Case;
• Comprehensive review of Range objects; dynamically finding end of ranges; copying data between sheets and files;  a look at Comments (called Notes in later Excel)
• Debugging errors: step into; step over; step out; Debug.Print and Debug.Assert; breakpoints; Watch windows; Immediate and Locals windows;
• Validating input, type conversion functions;
• Looping structures: review of for-each; while loops; for-next loops; nested loops; performance;
• Automating built-in Excel functionality: AutoFilter, Go To, WorksheetFunction; how this can avoid loops; improving Macro Recorder code;
• Arrays: static and dynamic arrays; variant arrays;
• Interacting with the user: MsgBox constants; Application.InputBox; GetOpenFilename, GetSaveAsFilename;
• Workbooks and filesystem: opening, saving, Dir statement, FSO object;
• Workbook and Worksheet events;
• Complex applications: structuring programs using separate macros for separate tasks; variable scope; using a consistent programming style;
• Manipulating charts and pivot tables; automating the creation of multiple charts and pivot tables;
• Error-handling: On Error Resume Next; On Error GoTo; an Error-handling template
• External libraries: introduction to automating Office applications: copying data between applications; basics of sending Outlook emails; FileSystemObject (FS0) and dictionaries; early and late binding;
• Microsoft documentation and review of useful VBA resources.

By the end of the course, you will be able to

• Gain a more in-depth knowledge of programming techniques and data structures;
• Understand the Excel Object Model in-depth;
• Have a comprehensive understanding of Workbook, Worksheet and Range objects;
• Dynamically find ranges - especially target ranges for copying data;
• Automate common Excel tasks such as filtering data, creating/manipulating charts and pivot tables;
• Avoid code that causes slow performance;
• Combine subroutines and functions to create a simple application;
• Interact with the file system;
• Understand how to use library references.

You will be awarded an official City, University of London certificate if you attend over 70 per cent of the classes. The course is not formally accredited.

Assessment

There is no formal assessment as part of this short course.

Visual Basic for Applications (VBA) in Excel Part 2 follows on from Visual Basic for Applications (VBA) in Excel Part 1, and delegates should have successfully completed this or have equivalent knowledge.

English requirements

Applicants must have fluent written and spoken English.