Advance Excel – With VBA - Duration 1 Month
Chapter 1- Introduction to excel
Why
What
Who
How
Anticipated User Skill Requirements
Copyright Acknowledgments
Creating this Guide
Chapter 2 – Introduction to VBA
WHAT IS VBA?
VBA: An Event Driven Language
VBA: An Object-Based Language
THE EXCEL VBA IDE
Getting to the VBA IDE
To Be Explicit or Not
TYPES OF CODE MODULES
General Purpose Code Modules
Workbook Code Modules
Workbook Events
Worksheet Code Modules
Worksheet Events
The „Target‟ and „Cancel‟ Objects
Class and User Form Modules
Class Modules
User Forms and their Modules
Chapter 3- Procedures & Functions
PROCEDURES: FUNCTION AND SUB
Functions
Subs
Procedures: Public or Private
Chapter 4- Data & Its Types
CONSTANTS, VARIABLES AND TYPING
Data Types
Our First Procedure
Reserved Words
Comments and Remarks
Chapter 5- Error Handling
Error Handling: A Beginning
Constant and Variable Declarations Revisited
Procedure Level Scope
Module Level Scope
Public Scope
When to Use Constants and/or Variables
Chapter 6- GOOD PROGRAMMING PRACTICES
What is Good Code
Good Programming Practice
Good Programming Practices
Chapter 7- LOOPING STRUCTURES
For … Next Loops
For Each Loops
Do... Loops
Do Loops Control Summary
DECISION MAKERS
If...Then
If...Then...Else
If...Then...ElseIf...Else
Select Case
DATA SOURCES
Data from Worksheets: Intro
Data from External Sources
User Provided Data
Input Using Input Box$()
Using MsgBox$ as User Input
User Form as a Data Source
Data from Worksheets: A Study
Project 1: Copy between Workbooks
Data from Text Files: A Study
Project 2: Importing Data from a Text file
Chapter 8 - PROGRAMMING WITH EXCEL OBJECTS
Advantages of Using Object References
Performance Improvements Using Object References
The Excel Object Model as a Reference
Chapter 9 - CODE SNIPPETS AND EXAMPLES
Sorting A Range
Find the Last Used Cell in a Column
Identify the Last Used Row
Identify the Next Available Row
Find the First Empty Cell in a Column
Get the Address Instead of the Row
Find the Last Used Cell in a Row
Consolidating Data in a Workbook
Chapter 10 ADDITIONAL EXCEL VBA RESOURCES
EXCEL MVP WEBSITES
Excel tips
Chart Tutorials
General
Formatting
Formulas
Charts & Graphics
Printing
Developer Tips by Category
General VBA
Command Bars & Menus
User Forms
VBA Functions
Advance Excel- With Marcos & Pivot Table – 15 days
Course Description
Functions
AVERAGE
COUNTIF
IF
MEDIAN
MAX
MIN
PMT
SUM
SUMIF
Relative & Absolute Cell References
Naming Cells
Conditional Formatting
Exercise: Scientific Instrument Readings Analysis
Formatting & Appearance
Numbers
Alignment
Fonts
Borders
Background Colors
Clearing Cell Formatting
Adding Comments to Cells
Graphics & WordArt
Advanced Analysis Tools
Goal Seek
Scenario Manager
Automating Excel with Macros
Creating & Recording a New Macro
Running a Macro
Suspending & Resuming a Macro
Deleting a Macro
Exercise: Buying a House
Regression Analysis with Trend lines
Exercise: Finding Unknown Alligator Weights
Exercise: Billing Example
Workshop Evaluation