Overview
The purpose of the course is to gain an understanding of how Excel VBA can save you time through automation of repetitive Excel tasks. Day one is a core skills day, delivered with tutor-led mini exercises, designed to build up programming confidence. Here we spend a good amount of time on language structure and syntax. Day two applies the concepts to a real-life business problem, where delegates are given freedom to design a solution with tutor guidance.
Key Learning Outcomes:
- Record, write, edit, test and run VBA macros with confidence
- Automate repetitive Excel business tasks and streamline workflow
- Write and apply User Defined Functions (UDFs)
- Implement event procedures
Course Content
- Recording Macros
- Recording and Running Macros
- Recording a Macro
- Running a Macro
- Adding a Macro/Procedure to the Quick Access Toolbar
- The Personal Macro Workbook
- Saving the Personal Macros
- Editing a Personal Macro
- Assigning Personal Macros to the Toolbar
- Introducing Visual Basic for Applications
- Editing Macros in Visual Basic Editor
- Understanding the Development Environment
- Protect/Lock Excel VBA Code
- Using Help
- Understanding and Creating Modules
- Defining Procedures
- Naming Procedures
- Creating a Sub-Procedure
- Working Using the Code Editor
- Defining Control-Of-Flow structures
- Using Boolean Expressions
- Using the IfEnd If Decision Structures
- Using the Select CaseEnd Select Structure
- Using the DoLoop Structure
- Using The ForNext Structure
- Using the For EachNext Structure
- Guidelines for Use Of Control-Of-Flow Structures
- Understanding Errors
- Using Debugging Tools
- Identifying the Value of Expressions
- Setting Breakpoints
- How to Step Through Code
- Working with Break Mode during Run Mode
- Using the Immediate Window
- Defining Objects
- Examining the Excel Object Hierarchy
- Defining Collections
- Referencing Objects in a Collection
- Using the Object Browser
- Working with Properties
- The With Statement
- Working With Methods
- Event Procedures
- Defining Expressions and Statements
- Expressions
- Statements
- How to Declare Variables
- Naming Variables
- Assigning Values To Variables
- Declaring Variables Explicitly
- Determining Data Types
- Programming with Variable Scope
- Harnessing Intrinsic Functions
- Defining Constants and Using Intrinsic Constants
- Using Intrinsic Constants
- Creating a Function Procedure
- Calling a UDF
- Using a function within an Excel Workbook
- Adding Message Boxes
- Return Values
- Notes Error! Bookmark not defined
- Using Input Boxes
- How to Declare and Use Object Variables
- Defining VBA’s Error Trapping Options
- Capturing Errors with the On Error Statement
- Determining the Err Object
- Coding an Error-Handling Routine
- Using Inline Error Handling
- Defining UserForms
- Utilising the Toolbox
- Using UserForm Properties, Events And Methods
- Properties
- Events
- Understanding Controls
- Naming Conventions
- Setting Control Properties in the Properties Window
- Using the Label Control
- Using the Text Box Control
- Using the Command Button Control
- Using the Combo Box Control
- Using the Frame Control
- Using Option Button Controls
- What is a Range?
- Range Property of the Application
- Cells Property
- The SpecialCells Method
- Naming Ranges
- Working with Collections
- Creating charts from worksheet data
- Key Properties and methods of the chart object
- Creating Charts from Arrays
- Understanding PivotTables
- Creating A PivotTable
- Procedure
- Using the PivotTable Wizard Method
- Using PivotFields
- What is an Array
- Array Sizes
- One Dimensional Arrays
- Arrays with Multiple Dimensions
- A word about index numbers
- Ubound and Lbound
- Saving arrays in names
- Procedure Arguments
- Passing Arguments
- Optional Arguments
- Default Values
- Passing arguments by value and reference
- VBA Password Protection
- About Macro Security
- Macro security settings and their effects
- Change Macro Security Settings
No reviews for this course