|
Module
#1 - Visual Basic For
Applications 2007 Course
Full Version
(a
$297 value)
|
The course
is composed of several videos organized in 3 DVDs. In the first edition
more
than 40 lessons are included. Each lesson is composed of one of more
videos.
The videos are created by experts developing applications using Visual
Basic
for Application (VBA) for MS Excel or MS Access for small, middle and
large
companies as consultants and as managers and coach for internal
processes.
These experts have created complex tools to create the reporting
structure and
manage the business processes. An extended version will include also
real examples
to be used directly in working and office processes.
In Details:
Lesson
1
What
Object oriented programming is
How to
get results with VBA
Macro
and Spreadsheets
Lesson
2
The
power of Excel with VBA
Personal
limits and barriers before using VBA
VBA
toolbar
Macro
Security
Recording,
Saving and Running Macros
VBE –
The VBA editor
The
VBA code
Protection
of the source code
Lesson
3
Understanding
the code
Remove
the concept “VBA is HARD!”
VBE to
analyze recorded macros
The
Debugging tool
Objects,
Methods, Properties
Code cleaning
and optimization
Objects
Type
of objects
Classes
Dates
and Times
Number,
Characters, Strings
Lesson
4
Declare
functions
Functions
and Subroutines
Constants
and Variables
Options:
Explicit, Implicit
Private
and Public variables
Dimension
variables (Dim, Redim, Redim Preserve)
Lesson
5
Objects
Hierarchy
of objects
Worksheets
as objects
Workbooks
as objects
Cells
and Ranges as objects
Properties
and Events
Lesson
6
The
Range Object
Range
properties and selection tips
Refer
to Ranges in other sheets
Relationships
between ranges
Select
ranges using the Cells property
The
Offset property
Size
and resize ranges
Columns
and Rows to refer to Ranges
Join
multiple ranges
IsEmpy
function
CurrentRegion
property
Areas
Collection properties
Format
ranges – create an automatic routine
Number
formatting
Excel
constants
Lesson
7
The
Msgbox objects
Msgbox
properties
The
Inpubox
Predefined
Dialogs
Standard
Excel Dialods
The
Application object
VBA
constants
Lesson
8
User-defined
functions
Custom
Excel functions
Lesson
9
For..Next
Loops
Do
Loops
While..Wend
loops
Do
While Loops
Do
Until Loops
With
instruction
For
Each – The VBA loop
Lesson
10
Operators
The
MOD instruction
The
logical instructions
The IF
instruction
IF,
ELSE, ELSEIF…
The
Select Case instruction
The
Type definition
Lesson
11
Formulas
R1C1
style formulas
Display
R1C1 style formulas in Excel
Formulas
in Excel
The
R1C1 reference style
Conditional
formatting of R1C1 formulas
Array
formulas
The
SUM formula
Lesson
12
Names
Global
and Local Names
Add
and deleting Names
Types
of Names
Hide
Names
Checking
Names
Lesson
13
Events
Levels
of Events
Workbook
and Worksheet Events
Chart
sheet Events
Application-Level
Events
The
target variable in worksheet
The
Open and Close Events
The
Change and SelectionChange Events
Lesson
14
User
Forms
User
Interaction Methods
Create
a Userform
View
and Hide USerforms
Userform
controls and objects
The
Multipage Control
Lesson
15
Charts
Embedded
Charts and ChartSheets
How to
create a chart using VBA
Object
variables
The
Chart structure
The
Chart Types
Interactive
Charts
Save
Charts as images
Customize
Charts
Optimize
the layout: change the position of labels in a chart
Lesson
16
Page
Setup
Setup
the Header
Setup
the Footer
Margins
Setup
the PrintArea
Lesson
17
The
String functions
The MID
function
The
UCASE and LCASE functions
The
Instr function
User
Defined functions to manipulate strings
Lesson
18
The
Time functions
The
Date functions
Set
Working and holiday days
Set
weekly calendars
Compare
dates
Lesson
19
Filtering
Data
Advance
filtering in Excel using VBA
Critaria
ranges for advanced filtering
Filter
in Place
The
importance of the xlFilterCopy constant in filtering
Autofiltering
Lesson
20
Pivot
Tables
Creating
a Pivot Table in Excel using VBA
Summarizing
data fields with the Grouping functionality
Advanced
Pivot Table techniques
Pivot
Table functions
Reporting
Lesson
21
Group
function
Creating
Subtotals
Protect
Worksheets and Workbooks
Create
Tables
Lesson
22
Using
Add-ins
Standard
Add-ins
Converting
an Excel Workbook to an Add-in
Install
Add-ins
Hidden
Workbooks as Add-ins
ActiveX
automation
Dynamic
Linking Libraries
Lesson
23
Web
services
Importing
data from the Web
Streaming
Data
Using
Application
Reading
and Writing to the Web
Lesson
24
What
is XML?
Universal
File Format
XML to
export Excel to HTML
Lesson
25
Use
Excel to update Word files
Early
and Late Binding
Create
and Refer Objects
Objects
in Word
Lesson
26
Arrays
Declare,
Fill and Empty Arrays
Passing
an Array
Lesson
27
Files
File
System Object
Drivers
and folders
File
types
Excel
special methods
Lesson
28
Text
files
Importing
and writing from and to text files
Dir()
function
Shell
function
Kill
Function
Open
and Close functions
Lesson
29
Databases
and VBA
Connecting
to Access
ADO
versus DAO
ADO
tools
SQL
rules and VBA
Operations
on records
Functions
on records via ADO
ADO
utilities
Postgres
and VBA
DAO
and Access
Lesson
30
Classes
Inserting
a Class Module
Creating
a custom object
The
Get and Let properties
Records
Collections
User
defined types
Drawing
objects
Lesson
31
Advanced
Userform techniques
View
Code/View Form
Userform
Toolbar and controls
More
Userform controls
Userform
properties
Userform
events
Adding
controls at Runtime
Tab
Order
Formatting
Userforms
Templates
and smart forms
Control
of Userform in an Excel Worksheet
Lesson
32
Connection
to Outlook
Mailing
using VBA
Mailing
properties
Lesson
33
Advanced
Programming Interfaces (API)
Registry
and VBA
API
declaration
Some
examples
Lesson
34
Errors
Error
parameters
Error
functions
Generating
error events
Error
handling
Lesson
35
Creating
and using custom menus
Creating
and using custom toolbars
Other
ways to run macros
Lesson
36
Ribbons
and Excel 2007
Adding
Ribbons
Customize
Ribbons
Lesson
37
VBA as
the tool to extend Excel
Combining
and separating Workbooks
Cell
comments
VBA
advanced techniques
Lesson
38
Miscellaneous
Sendkeys
function
The
Shell instruction
DoEvents
and Events
Enable
Events
Screen
update
Shortcuts
to move through routines
Lesson
39
Special
properties and events of ranges
List
of constants
Special
functions (Cstr, Split,…)
Some
cool examples of applications are
included as videos and source code.
ear
friend,
