Intermediate Excel Macros and VBA Programming

To explore further the object techniques available in the VBA programming language, focussing on best practices and efficiency of coding.

Course Outline
Intermediate Excel Macros and VBA Programming Programming 1 Day

Who is it for?

This course is designed for those who have a basic grasp of VBA and would like to enhance their existing skills. At the end of the course, you will be able to manipulate ranges more efficiently, create a user defined function and be able to create a custom data entry screen utilising a UserForm.


Delegates should have attended the Introduction to VBA course or have the equivalent knowledge and experience of programming in VBA.

Coding Techniques

    Review of Basic Coding Techniques

  • Recording macros
  • Control structures and their use
  • Declaring and using variables
  • Objects, properties and methods
  • Debugging Techniques
More on Objects
  • The Excel Object Model
  • What is a collection?
  • The FOR EACH loop
  • Using an object as a variable
The Range Object
  • What is a range object?
  • Different types of range object
  • Useful properties
  • Useful methods
Controlling other applications
  • Referencing other applications
  • Instantiating objects
  • Writing inter-application code
  • Connecting to other Office applications
  • Overview of Word object model
  • Overview of Outlook object model
User Defined Functions
  • Define a user defined function (UDF)
  • Define the arguments to the function
  • Passing by reference or by value
  • (ByRef or ByVal)
  • Optional and default parameters
  • Use the function in an Excel workbook
Share Functions

    Methods for sharing the Function

  • Using the personal macro workbook
  • Creating an Add-In
  • Loading the Add-In
  • Create a custom data entry screen using a Userform
  • Adding controls to a Userform
  • Controlling the Userform

Tags: Microsoft Excel

About the Trainers

Qualifications - European Computing Driving License (ECDL) Microsoft Office Specialists (MOS) Microsoft Office User Specialist (MOUS), Microsoft Certified Applications Specialist (MCAS), 2 of our trainers have taken the Institute of Learning Management Train the Trainer Master Class Award. Every trainer has a minimum of 5 years training experience.


NC Member logo

Latest News

Windows 8 and 10

Windows 8 courses now available.

New courses for Microsoft Windows 10 will be available soon – be one of the first to see it with us.

MS Office Training

Microsoft Office weekend training and Microsoft Office 2013 upgrade training now available.


"I have used NC Training Services for a number of years now to provide a range of training for staff at 2 different companies. I have always been very impressed by the flexibility of the company and the consistency of the training. The feedback from all staff who have attended the courses has been extremely positive and has definitely suited the needs of the people. Due to the ability to tailor make a course, I have been able to provide clear objectives (which can vary depending on the company/people being trained) and this is something I had not been able to do before and it certainly proved extremely beneficial for the people attending the course. I would certainly recommend NC Training Services."

Caroline Holt, Customer Service Manager

"After taking part in the intermediate and advanced excel courses I soon booked onto the word training. Nicola was both helpful and technical. The courses were just the right length and I would strongly recommend."

James Harris, Critchleys Chartered Accountants