Course overview

Duration: 2 days (13 hours)

This course is aimed at experienced VBA users or those who have attended our 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

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.

View our full catalogue of Leadership, Personal Development, Systems, Office Applications and Project Management courses

Contact us

Do you have questions, suggestions, or feedback? We value your input and are here to assist you. Please don't hesitate to get in touch with us via email or phone:

• Email: [email protected]
• Phone: +44 (0)20 8152 6551

Our dedicated team is ready to respond to your enquiries and provide the support you need. Feel free to reach out, and we'll do our best to assist you.

Thank you for visiting our website, and we look forward to hearing from you!