This course is designed for users of Microsoft Excel wanting to extend their existing skills in using the product. It is aimed at those who have no prior knowledge of Visual Basic for Applications and who want to be able to automate tasks and create custom user-interfaces within Excel.
The course is based upon the subject areas listed below and each chapter includes tutor-led demonstrations followed by hands-on practical exercises for delegates. Please note that the subject areas covered will depend on the ability of the delegates attending and time available.
For those who have a reasonable working knowledge of Excel and who wish to automate tasks and extend the functionality of Excel further.
By actively participating in this course, you will learn about the following:
Understand and create macros.
View and modify recorded macros.
Understand Visual Basic procedures.
Understand and use objects.
Understand variables.
Understand decision making and looping.
Use controls and dialog boxes.
Create automatic procedures.
To ensure your success, we recommend that you have a reasonable working knowledge of Excel or have attended the courses detailed below:
Software Requirements for Virtual Classroom
Delegates will need to have Microsoft Excel 2013, 2016, 2019 or 365 (desktop version) installed on their PC to participate in Virtual Classroom courses. Delegates may notice differences in their software if using a MAC.
Introduction to Macros
The Capabilities of a Macro
Recording a Macro
Where a Macro is Stored
Stopping the Recorder
Running a Macro
Absolute and Relative Referencing
Adding a Macro to a Menu
Adding a Macro to a Button on a Tool Bar
Adding a Macro to a Button on a Worksheet
Viewing & Modifying Recorded Macros
Displaying a Macro
Reading the Visual Basic Code
Getting Help in Visual Basic
Printing a Visual Basic Module
Visual Basic Procedures
What is a Procedure?
Procedure Arguments
Calling Procedures
Visual Basic Modules
Calling Procedures from other Modules
Calling Procedures from another Workbook
Visual Basic Objects
Introduction to Objects
Using Objects
Properties in Procedures
Performing Actions with Methods
Collections of Objects
Objects as Containers
Referencing Cells
The Object Browser
Using the Object Browser
Learning Objects' Properties and Methods
Pasting Code into your Module
Declaring Variables
Why identify Variable and Arguments in Advance
Option Explicit helping with Errors
Data Types
Data Types within Arguments
Assigning Objects to Variables
Operators, Constants & Scope
Operators, Constants, Scope of Variables and Constants
Decision Making & Looping
Decision Making
If....Then Statements
Select Case Statements
Looping
Do....Loop
For....Next Loops
Nested Control Structures
Exiting from a Control Structure
Testing & Correcting Code
The Tools that can Help
Debugging Tools on the Visual Basic Toolbar
Using the Immediate Window in the Debug Window
Using Watch in the Debug Window
Watch Types in the Watch Window
Stepping Through your Code
Controls & Dialog Boxes
Using Simple Pre-defined Dialog Boxes
Getting Information from the User
Displaying Information with the MsgBox Function
Making Customised User Forms
Setting Control Properties
Assigning Code to Controls and User Forms
Displaying a User Form
Creating Automatic Procedures
Creating Automatic Procedures
Setting Automatic Macros for Single Sheets