Advanced Microsoft Excel VBA Manual Outline

Module: Introduction

Key Module Topics:

• Copyright and support information

• Mouse terminology

• Digital manual and example organization

• Where to find the Excel examples

Module: Excel for Mac Supplementary Manual

Key Module Topics:

• Mouse terminology

• Key capability differences

• Key keyboard differences

• Key VBA differences

• Compatibility issues between Mac and PC

Module 5: VBA Editor and Module Basics For Engineers

Key Module Topics:

• Overview of how VBA can help the engineering process

• VBA editor overview including VBA project, module and procedure overview

• Controlling and typing in VBA modules

• Referencing and integrating VBA engineering projects

• Using the VBA debugger to debug your engineering code

Module 6: VBA Language Basics for Engineers

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 engineering tasks in VBA

• How to use the Object Browser

• How to error proof your VBA code

• Controlling key Excel objects with VBA

• Data processing methods using VBA

• Automated chart construction

Module 7: Function Procedure Design for Engineers

Key Module Topics:

• Benefits of using Function procedures and User Defined Worksheet Functions (UDF's) for engineering tasks

• Function procedure and UDF differences

• How to declare engineering Function procedures and UDF’s in VBA

• How to create arguments for engineering Function procedures and UDF’s

• How to call engineering UDF’s from a worksheet formula

• Calling engineering Function procedures from other VBA procedures

• Function procedure and UDF procedure interface design

• How to pass worksheet ranges as arguments to engineering UDF's

• Returning scalar and array values from UDF’s to worksheet engineering formulas

• Using VBA and Excel Worksheet functions in your engineering procedures

Module 8: VBA Sub Procedure Design For Engineers

Key Module Topics:

• Benefits of using Sub procedures for engineering 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 engineering graphics

• Executing Sub procedures via events

• Sub procedure interface design

• Sub procedure design tips for engineers

• Property procedure overview

Module #9: Graphical User Interface Design for Engineers

Key Module Topics:

• Developing engineering GUI's and applications in Excel

• Drawing engineering graphics on the worksheet

• Importing engineering graphics into Excel

• Creating graphics that display engineering data on the worksheet

• Assigning VBA Sub procedures to worksheet based graphics

• Using ActiveX and Form controls to create engineering GUI’s on the worksheet

• Creating event procedures for controls placed on a worksheet

• Engineering wizard overview

• Using userforms to create engineering wizards

• Creating userforms in Excel

• Creating controls and graphics on userforms

• Commanding userforms and controls with VBA

• Engineering wizard design tips

• Creating toolbars and menus to control your Excel engineering tools

• Controlling Excel’s toolbar and menu system with VBA

Module #10: FORTRAN/VBA Mixed Language Programming

Key Module Topics:

• Why should engineers and scientists develop FORTRAN DLL’s

• How VBA locates and communicates with a FORTRAN DLL

• FORTRAN DLL calling conventions from VBA

• Why use Excel as a pre/post processor for FORTRAN DLL's

• FORTRAN DLL design tips (using old FORTRAN code and writing new code)

• Designing VBA procedures to communicate with FORTRAN DLL's

• How to build a FORTRAN DLL in Digital Visual FORTRAN

• How to debug a FORTRAN DLL

• How to manage FORTRAN DLL files

Module #11: Team Linking in Excel

Key Module Topics:

• Integrating teams and information using workbook links

• Creating/managing linked workbooks on a single computer and network

• Linked workbook distribution techniques

• Controlling linked workbooks with VBA

• Engineering and scientific uses of Excel’s shared workbook feature

• Construction techniques for shared workbooks

• Shared workbook operational procedures

Module #12: Packaging Applications in Excel

Key Module Topics:

• Storing and distributing Excel based tools in various Excel file types

• Constructing verified, standardized engineering and scientific tools in Excel

• Packaging/distributing engineering and scientific tools in workbook files

• Engineering and scientific uses of Add-Ins and workbook templates

• Constructing/packaging/distributing engineering tools in templates

• Constructing/packaging/distributing engineering in Add-Ins

• Distributing/maintaining Excel engineering tools on networks

• Engineering and scientific procedure library design (i.e. rapid prototyping and analysis)

• Developing security for Excel based software

Module 13: 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 14: 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

Module #15: Design Optimization in Excel Using Solver

Key Module Topics:

• Overview of design optimization

• How to layout an optimization problem in Excel

• Command and control worksheet overview

• How to drive multiple workbooks using Solver

• How to run VBA and FORTRAN while using Solver

• How to use Solver in Microsoft Excel

Module #16: Constructing Large System Models in Excel

Key Module Topics:

• How to determine your model equations and how they interact with each other

• Using Excel’s structure to organize system teams

• Laying out the system model in Excel

• Library workbook introduction and design

• Single and multi-workbook system model layout