Workshop Duration
2 days
Overview
This course is designed to give proficiency in the Visual Basic Editor (VBE), predominantly making use of Excel objects, basic VBA programming, e vent handling, control structures, and debugging tools
Prerequisites
- Navigate, edit and manage Excel workbooks
- Format worksheets and workbooks
- Understand the difference between absolute and relative references
- Understand the basics of Excel formulas and functions, e.g. Sum, text functions
- Understand the concepts of a Macro and its capabilities
Who will the course benefit?
In this course, you will extend your knowledge into some of the more specialized and advanced capabilities of Excel by automating some common tasks, applying advanced analysis techniques to more complex data sets, collaborating on worksheets with others, and sharing Excel data with other applications
Course Content
Introduction
- What is a Macro
- Recording a Macro
- Relative References
- Running A Macro
- Running A Macro From The Macros Dialog Box
- Editing A Macro With Visual Basic
Assigning Macros to buttons and objects
- Assigning a Macro to a Button on a Sheet
- Assigning a Macro to a Drawn Object on a Sheet
- Assigning a Macro to a Button on a Toolbar
- Creating A Short-Cut Key To Run A Macro
Visual Basic Editor
- Objects
- Methods
- Properties
- Programming Tools
- The Menu Bar
Editing Macros
- The Visual Basic Editor
- The Project Explorer
- The Code Window
- Procedures
- Inserting Comments
- Printing a Visual Basic Module
The Range Object
- The Cells Property
- The Range Property
- Flexible Ranges
- Range Contents
Building Formulae Control Structures
- If…Then Decision Structures
- Logical Operators
- For… Loops
- Do Loops
Event Handling
- Vba Editing & Debugging
- Auto Macros
- Error Handling
Controls, Dialogue
- Built-In Dialogue Boxes
- Predefined Dialogue Boxes
- Custom Forms and Worksheet Forms
- User-Defined Forms
- Adding Form Controls
- Form Control Properties
- Control and Dialogue Box Events
- Displaying and Dismissing a User Form
- Handling Form Controls
Control Structures
- Conditional Control Structures
- Looping Control Structures
- Non-Structured Statements
- Subprocedures
Workbooks and Sheets
- Activating a Workbook
- Activating a Worksheet
- ThisWorkBook, Running Code
- Run Mode And Design Mode
- Running Code From The Development Environment
The Project Explorer
- Using The Project Explorer
The Properties Window
- Changing A Property
Using Constants
- Excel Constants and Variable Constants
Objects Properties and Methods
- Controlling Objects with their Properties
- Performing Actions with Methods
- Performing Multiple Actions on an Object
Excel Objects
- Objects, Properties and Methods
- Getting & Setting Properties
Variables
- Using Variables In Routines
Using Basic Functions
- Inputbox Function
- Msgbox Function
- Using A Set Statement
Skills gained
- Record and edit a Macro recorded within Excel/Word
- Use different techniques in order to run any macros available such as keyboard commands, the use of the Quick Access Toolbars or Buttons
- Use VBA and examine the Visual Basic Editor; change the properties of an object; add a module to a project; write the code for a procedure and then run it; and use the Object Browser to search procedures
- Manipulate data by declaring variables of different data types; combine data by using expressions; use functions to accept input and display output; and declare variables and procedures with the appropriate scope
- Use decision structures to create procedures that make decisions; and use loop structures to perform repetitive tasks
- Create an error handling routine in case things go wrong with VBA code
If you would like to book onto a course or find out more information please fill out our contact form or call us on 01454 203 355.
