VBA in Excel - Part 2

Course overview

Duration: 2 days (13 hours)

This course is aimed at experienced VBA users or those who have attended our VBA in Excel Part 1 course and have started to use the language.

Part 2 expands on the huge array of commands and elements of the data model that can be used and looks at building more complex VBA models

Objectives 

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

• Use comparison operators and system functions
• Use Arrays
• Work with ranges
• Work with the worksheets and workbooks collections and objects
• Use application objects
• Use the FileSystemObject
• Create function procedures
• Import and save text files
• Connect to other applications

Content

Comparison operators  

• Using Comparison operators and functions
• The LIKE function
• Wildcards
• Using SELECT Case

System functions  

• The VAL function
• Case functions
• Trim functions
• Text functions
• Date functions

Excel worksheet functions  

• Using Excel functions in VBA

Arrays  

• Creating arrays
• Using arrays
• Ubound and Lbound
• Single and multi dimensional arrays
• Static vs dynamic arrays

Working with ranges  

• Creating range objects
• Passing data between range objects and arrays
• Using Transpose

The Excel object model  

• The object browser

Working with collections  

• Collections
• Creating object variables
• Setting object variables
• The For Each . . . Next Loo

Sheet collections  

• The worksheets collection
• The worksheet object
• The sheets collection
• Using object
• Grouping worksheets
• Using Typename

The Workbook collection  

• The workbooks collection
• Setting workbook variables

Application objects  

• Excel default information
• Display/alerts
• Screen updating
• Status bar
• On Time
• Using Wait

Systems dialog boxes  

• Showing dialog boxes
• Using dialog boxes

Working with files  

• Searching for files and folders
• The DIR function
• The FileSystemObject

Function procedures  

• Creating functions
• Adding arguments
• Calling functions

Working with text files  

• Searching for files and folders
• The DIR function
• The FileSystemObject
• Using the FILE and FOLDER collections

Sharing data with other applications  

• Early vs Late Binding
• Setting references to other applications
• Creating application variables
• Setting application variables

Still have questions about this course?

Click Enquire Now and our team will share full details on content, dates, and pricing — and help you decide if it’s the right fit for you or your organisation.

Public Training Programme

Explore our current selection of public courses, available as convenient and cost-effective virtual sessions.

Engage with expert instructors and a diverse peer group in an interactive, online learning environment.

Ideal for those valuing flexibility and affordability, our public courses are ideal where you only have small numbers requiring training and offer training from the comfort of your home or office.

Full Training Catalogue

We deliver a wide range of training across Leadership, Personal Development, Project Management, and IT applications. Many organisations choose us because they can source both technical and people-focused training from one trusted provider.

Courses can be tailored to your organisation’s needs, making it easier to upskill teams consistently across different areas. Whether you want to build management capability, strengthen project delivery, or boost everyday software skills, you’ll find relevant options in our catalogue.