Beginning, Intermediate,
Advanced Excel for Business (Modules 1-8)
|
Module: Introduction
Key Module Topics:
- Copyright and support information
- Mouse terminology
- Digital manual and example organization
- Where to find the Excel examples
Module 1:
Excel Basics for Business Professionals
Key Module Topics:
- Introduction to Microsoft Excel, VBA, DLL’s and business graphical user
interfaces
- Excel program interface review including how to use Excel on-line help
- How to open and close workbooks
- How to save workbooks
- How to use toolbars and the Ribbon
- How to arrange workbook windows
- How to create a new workbook
Module 2:
Excel and the Business Professional
Key Module Topics:
- Discusses how Excel can be used in the business process in the areas of
analysis, information integration, VBA program development, dashboard
development, rapid analysis, team integration and data processing.
Module 3:
Business Analysis Methods on the Worksheet
Key Module Topics:
- Worksheet/cell command and control fundamentals
- Business formula construction and editing techniques on the worksheet
- How to name cells and ranges and use them in business formulas
- How to design business input and output ranges on the worksheet
- How to use Logical, Lookup, Reference and Information functions to
create "smart" business worksheets
- How to create array formulas that summarize data
- How to use the conditional formatting and data validation features in
Excel to create interactive business applications on the worksheet
- How to track business data using the conditional formatting feature
- How to protect worksheets and workbooks
- How to create business forms on the worksheet
- How to format worksheets (i.e. color cells, change the font...etc)
- How to use Excel's drawing tools and import pictures
- How to design data and management task tracking applications in Excel
using the conditional formatting feature
- How to analyze sort and filter data using Excel's Sort, Auto Filter and
Advanced Filter tools
- How to create an outline a worksheet
- How to create Pivot Tables on the worksheet
- How to import text files into Excel
- How to construct ActiveX and Form controls on the worksheet
- How to create graphics that display worksheet cell values
- How to use OLE and DDE to link with other applications in Excel
- How to build worksheet outlines
Module 4:
Creating Business Charts and Reports in Excel
Key Module Topics:
- Business chart construction using Chart Wizard
- Business chart editing and formatting techniques
- Curve fitting in Excel
- Data visualization in Excel
- Business report generation strategies in Excel
Module 5:
Accessing Data Bases from Microsoft Excel Using MS Query
Key Module Topics:
- Overview of relational databases
- Overview of ODBC and MS Query
- How to use MS Query to access a database
- How return data to the Excel worksheet from MS Query
- How to dynamically link data on a worksheet to a database
Module
6: Problem Optimization in Excel Using
Solver
Key Module Topics:
- Overview of problem optimization
- How to layout an optimization problem in Excel
- Command and control worksheet overview
- How to drive multiple workbooks using Solver
- How to use Solver in Microsoft Excel
Module 7: Linking Teams Together in Excel
Key Module Topics:
- Overview of external
references
- How to link Excel workbooks
- How to manage Excel workbook links
- How to
distribute linked Excel workbooks
- How to share Excel workbooks
Module 8:
Advanced Excel Formulas
Key Module Topics:
- How to extract start and end points of data
- How use the IF function
- How to summarize data using advanced logic
- How to parse text
- How to determine specific items from lists
*Modules 9-11 Reserved for Future Manual Expansion
Microsoft Excel Beginning VBA
for Business (Modules 12-15)
|
Module 12:
VBA Editor and Module Basics
Key Module Topics:
- Overview of how VBA can help in business
tasks
- VBA editor overview including VBA project,
module and procedure overview
- Controlling and typing in VBA modules
- Referencing and integrating VBA projects
- Using the VBA debugger to debug your code
Module 13:
VBA Language Review
Key Module Topics:
- VBA variables, constants and arrays
(declaring, scope and data typing)
- VBA operators (logical, arithmetic and
comparison)
- Control structures (loops and logic
decisions)
- Overview of Excel objects, properties and
methods
- How to identify and command Excel objects
using VBA
- How to use Excel/VBA on-line help
- How to use the VBA macro recorder to record
Excel business tasks in VBA
- How to use the Object Browser
- How to error proof your VBA code
- How to command Workbook, Worksheet and Range
objects using VBA
- Automated chart construction
- Data processing techniques
- Dashboards for Excel
Module 14:
VBA Function Procedure Design
Key Module Topics:
- Benefits of using Function procedures and
User Defined Worksheet Functions (UDF's) for business tasks
- Function procedure and UDF differences
- How to declare Function procedures and UDF’s
in VBA
- How to create arguments for Function
procedures and UDF’s
- How to call UDF’s from a worksheet formula
- Calling Function procedures from other VBA
procedures
- Function procedure and UDF procedure
interface design
- How to pass worksheet ranges as arguments to
UDF's
- Returning scalar and array values from UDF’s
to worksheet formulas
- Using VBA and Excel Worksheet functions in
your VBA procedures
Module 15:
VBA Sub Procedure Design
Key Module Topics:
- Benefits of using Sub procedures for
business tasks
- Declaring Sub procedures in VBA
- Creating arguments for Sub procedures
- Calling Sub procedures from other VBA
procedures
- Executing Sub procedures via the Macro
dialog box
- Executing Sub procedures via shortcut keys
- Executing Sub procedures via graphics
- Executing Sub procedures via events
- Sub procedure interface design
- Sub procedure design tips
- Property procedure overview
Microsoft Excel Advanced VBA
for Business (Modules 16-18)
|
Module 16:
Graphical User Interface and Tool Packaging Design
Key Module Topics:
- Developing business GUI's and applications
in Excel
- Creating graphics that display business data
on the worksheet
- Assigning VBA Sub procedures to worksheet
based graphics
- Using ActiveX and Form controls to create
GUI’s on the worksheet
- Creating event procedures for controls
placed on a worksheet
- Business wizard overview
- Using userforms to create business wizards
- Creating userforms in Excel
- Creating controls and graphics on userforms
- Commanding userforms and controls with VBA
- Business wizard design tips
- Creating toolbars and menus to control your
Excel tools
- Controlling Excel’s toolbar and menu system
with VBA
- How to create and package Excel tools
Module 17:
Commanding Databases and Applications Using VBA
Key Module Topics:
- OLE automation overview
- How to command other applications from Excel
using VBA
- How to use CreateObject to control other
applications
- Using DDE with VBA
- How to use DAO to communicate with Microsoft
Access
- How to use ADO to communicate with Databases
- How to use ADO to communicate with Microsoft
Excel
- How to use ADO to communicate with Text
Files
- How to use the FileSystemObject to
communicate with Windows file system
- How to open multiple workbook files using
the FileSystemObject
- How to control Window's Shell
- How to control Microsoft Word
- How to control Microsoft PowerPoint
Module 18:
VBA /ActiveX DLL Programming
Key Module Topics:
- Object and class review
- Practical uses of objects
- Object method and properties overview
- VB project overview for ActiveX DLL's
- How to create an ActiveX DLL
- Creating and naming your class modules
- Designing object properties
- Designing object methods
- Designing object enums
- Designing object hierarchies
- How to reference your ActiveX DLL's from a
VBA or VB project and distribute them
- How to create and run your objects from
Visual Basic
- How to create and run your objects from VBA
via Microsoft Excel
- How to package and distribute your Active X
DLLs
|
