Excel Macros from A to Z using VBA
Course Description
This course refreshes the skills of Advanced users of Excel on the basics of Macros, then builds on the knowledge of participants to enable them to create, vet and edit Macros effectively and accurately using VBA.
Duration: 1 days
Prerequisites
All participants should have a strong working knowledge of Excel.
Overview
What is VBA
What can be done with VBA
Other products containing VBA
Storing VBA files
Using VBA
How to get use VBA
Using the VBA Editor
Using the Integrated Development Environment
Toolbars
Menus
VBA Windows
VBA Syntax
Using Code Modules
Naming Modules
How to write code
Editor Options
Code Options
Excel Macros
Creating a macro in Visual Basic
Recording a macro,
Examining the recorded VBA
Editing the recorded VBA
The Excel Object Model
Structure of an Excel Application
Visual Basic Help
Excel Object Browser
Types of Excel Objects
Visual Basic Subroutines and Functions
Using Windows message boxes
Using Windows input boxes
Creating VBA subroutines
Creating VBA functions
Visual Basic Data Types and Variables
What is a variable?
Scope of a variable
Size of a variable
What is a DataType?
String DataTypes
Numeric DataTypes
Date DataTypes
VBA Built-In Functions
String manipulation functions
Numeric manipulation functions
Date manipulation functions
Statistical functions
Controlling Excel Objects from VBA
using Excel workbooks in VBA
using Excel worksheets in VBA
using Excel ranges in VBA
finding lastrow in range object
VBA Arrays
dimensioning arrays
filling arrays
displaying arrays
redefining arrays
multi-demensional arrays
Control of Flow in VBA
using If statements
using Case Select statements
using For loops
using ForEach loops
using While loops
interacting through collections
Excel methods, properties and events
What are Methods?
What are Properties?
What are Events?
Opening Workbooks with VBA
Responding to Excel events, Open,Close
Debugging with VBA
How to debug in VBA?
using Immediate window
using the Watch window
using Breakpoints
using Single step
monitoring variables
UserForms in Excel
What is a UserForm?
Creating Userforms in Excel
Using the Toolbox
using Textboxes and Labels
Using Comboboxes and Listboxes
using CommandButtons
Using Option and Checkbox controls
Practical Excel Forms lab
Build a project to collect user data
Design and Build Booking UserForm
Write VBA code to control the UserForm
Save the user data in Excel
Userform opens when Excel loads
Connecting to other Applications
VBA in other applications
using Word via Excel VBA
using Access via Excel VBA