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.
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
- Recording macros
- Control structures and their use
- Declaring and using variables
- Objects, properties and methods
- Debugging Techniques
Review of Basic Coding 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
- Using the personal macro workbook
- Creating an Add-In
- Loading the Add-In
Methods for sharing the Function
- Create a custom data entry screen using a Userform
- Adding controls to a Userform
- Controlling the Userform