Transcript Integrate the VBA 6 Development Enviornment into your

Integrate the VBA 6 Development
Environment into your Application
Rebecca Rinner
Senior Software Engineer
Summit Software Company
4-307
Outline



Key benefits of VBA
Preparing for VBA Integration
Integrating VBA



What is APC
VBA Integration Architecture
Phases of VBA Integration
Outline



Key benefits of VBA
Preparing for VBA Integration
Integrating VBA



What is APC
VBA Integration Architecture
Phases of VBA Integration
Visual Basic for Applications
The premier development technology found in
applications that enables your customers to:
Customize
Enable customers to tailor your
product to their specific needs
Integrate
Enable customers to create entire
line of business solutions
Leverage
Deliver the power of Visual Basic to
leverage the 3+ million VB developers
Buy vs. Build
Buy
Pros
Lower Initial Cost
Fast Deployment
Cons
Not Flexible
No Advantage
Build
Pros
Infinite Flexibility
Your Code & Data
Cons
High Risk
High Cost
Buy and Customize
Lower Initial Cost
Infinite Flexibility
Fast Deployment
Your Code & Data
Competitive Advantage!
Flexibility


Your application becomes a platform
Write portions of your application in
VBA




Change features after you ship
Add features after you ship
Eliminate one-off feature requests
Create a 3rd party community
“Got VBA?”

VBA throughout Office 2000



Outlook, FrontPage
100+ shipping applications
New announcements
every week
Types of Applications


Traditional “thick client”
Multi-threaded applications


Multi-threaded designer
VBA in the Middle-tier


Allows customizable business objects
Duwamish sample on MSDN
Thin client
Check out the VBA Pavilion!

An Application with VBA
DEMO
Outline



Key benefits of VBA
Preparing for VBA Integration
Integrating VBA



What is APC
VBA Integration Architecture
Phases of VBA Integration
Preparing for VBA Integration
VBA Building Blocks
COM-Enable Your Application
Design and Implement an Object Model
VBA Building Blocks…
Visual
Basic Editor (VBE)
Project
Explorer
Code Editor
Immediate Window
Properties Window
Locals, Watch, ...
VBE Object model
“Visual Basic for Application
Extensibility”
 Defined in vbe6ext.olb

CodeModule
CommandBarEvents
VBProject
VBComponent
References
...
VBA Building Blocks...
Project Hierarchy
VBA Project
Project Items
Host project Items
Controls*
UserForms
Code modules
Class modules
Host classes**
Designers**
*
**
Controls are not visible in the Project Explorer window. They appear only in the Object combo box of the Code Editor
Host Classes and Designers are normally visible in the Project Explorer window, just not this one.
VBA Building Blocks...

VBA Project





Unit of persistence
Uses IStorage to load/save
Often, a 1:1 association with
documents
An application can have multiple VBA
projects
Contains all code in “Project Items”
VBA Building Blocks...

Project Items

Host project items





Simply COM objects
Expose events to VBA
Code “behind” (using COM aggregation)
Can have controls
Host Classes

Just like host project items, except that
you can have multiple instances of them
VBA Building Blocks...

Project items...

Code module



Class module


Contain VBA code only
Global
Creatable VBA classes
UserForms and Other Designers

UserForms (built-in)


Can be used to build UI for your application
VB6 Designers
VBA Building Blocks...

Code Execution


Macros dialog box
Named macros executed
in response to UI




Toolbar, menu selection,
keystroke
Can pass parameters
Events
Code behind
Preparing for VBA Integration
VBA Building Blocks
COM-Enable Your Application
Design and Implement an Object Model
COM-Enable Your
Application




Follow the COM Specification
Adopt the COM Philosophy
Use COM as the Binary Standard for
Application Components
For MFC applications, use ATL for
COM support.
COM-Enable Your
Application...

You don’t have to rewrite your
application

Use “peer” objects
Application
C++ or MFC
classes
APC
“Peer”
COM Objects
VBA
COM-Enable Your
Application...
VB Applications are COM “friendly”
COM object model exposed as VB classes
Application
VB Classes
APC
VBA
Preparing for VBA Integration
VBA Building Blocks
COM-Enable Your Application
Design and Implement an Object Model
Design and Implement
an Object Model


Definition: An Object Model is the set
of objects, methods, and properties
your application exposes for
programmability
Required for VBA integration
Why Expose an Object Model?



Allow your application to be part of bigger
solutions
 Can be driven by a wide variety of tools
(VBA, VB, VC++, Delphi, and others)
Create a 3rd party community supporting
your product
Required for VBA integration
Object Model Design Tips

Use standard constructs and behavior




Events provide




Users will already know it.
Users will intuitively understand it.
VBA will not choke on it.
Respond to user actions
Respond to detected conditions
Hook application internals
See the Summit white paper
Object Model
DEMO
Outline



Key benefits of VBA
Preparing for VBA Integration
Integrating VBA



What is APC
VBA Integration Architecture
Phases of VBA Integration
Integrating VBA
Q: How do you integrate VBA?
A: Use Microsoft APC.
Application
Programmability
Component
?
!
What is APC?



APC Application Programmability
Component
COM object with interfaces used to
host VBA (IApc*)
Can be used from most COM
consumers (VB, MFC, ATL, C++,
Delphi, etc.)
What is Required for Your
Application to use APC?



Must be able to call COM interfaces
Must sink COM source interfaces
If your application needs to show
the VBA Editor…

…it must provide access to its
message loop
VBA Integration Architecture
MFC
C++
VB
Delphi
…
Template Code
APC/MFC
Application Code
Integration Code
APC/C++ (CApc*)
APC COM Component (IApc)
Microsoft VBA (IVba*)
Core VBA Code
APC Object Model (Simplified)
Object
Projects
Collection
ProjectItems
ProjectItem
Controls
Host Project Item
Apc
Project
Code Module
Class Module
Designer Item
UserForm
Host Class
Control
VBA Integration Tasks
Complex
Moderate
Simple
Phase 1: Initial Steps
Phase 2: Projects
STOP
Phase 3: (Optional) Project Items
STOP
Phase 4: (Optional) Controls
STOP
Note: Advanced Features can be added to any
completed VBA integration project.
Phase 1: Initial Steps
Initializing APC




Create the APC Host
Associate the Application Object
Manage windows and events
Show the VBA IDE
Phase 1: Initial Steps
Initializing APC

What is the Application object?

Merges methods & properties into
namespace
Sub Main
MsgBox “Hello”
r = Application.Foo
r = Foo
End Sub
MsgBox is a VBA built-in keyword
Foo is a method of the Application
object
Foo is merged into VBA’s global
namespace and can be called
without further qualification
Phase 1: Initial Steps
Managing windows and events



VBA IDE is a top-level window
Window parenting
Tracking the active component


Forward accelerators to active
component
Re-entrant message loop

Isolate to a single set of routines
Phase 1: Initial steps
DEMO
Phase 2: Projects




Project is the unit of persistence
Create, Save, Load a Project
Execute code
An application can have multiple
VBA projects
Phase 2: Projects
Persistence

Requires OLE structured storage
(IStorage)

Disk-based



In-memory




In your document file
In standalone file
Stored in a database as a BLOB
Must be transacted
IApcProject supports IPersistStorage
For VB Developers, APC supports:


Storage Object
Stream Object
Phase 2: Projects
Executing VBA code

Named macros -- use the Run method
TheProject.Run "ThisDocument", "Main"

Available for ProjectItem, Procedure,
and Project
IApcProjectItem::Run
IApcProcedure::Run
IApcProject::Run

Macros dialog...
Dim TheScopes As MSAPC.Scopes
Set TheScopes = TheProject.CreateScopes
TheScopes.MacrosDialog.Show
Phase 2: Projects
Other uses

Code sharing / reuse



Microsoft Word templates
(NORMAL.DOT)
Global library
Custom event routing


Event goes first to main host project
item
If event not handled, routes to
template
Phase 2: Projects
DEMO
Phase 3: Project Items



Used to expose an object in your
COM object model’s hierarchy
VBA user can write VBA code to
handle events
VBA user can write code behind
Phase 3: Project Items

Host project items are simply COM
objects



Must derive from IDispatch and
support IProvideClassInfo
Method’s parameters strongly typed
and named
Coclass exposes a source interface
(events)
Phase 4: Controls


VBA controls are simply COM
objects
Types




Built-in  COM objects
External  ActiveX controls (.OCX)
Placed in the VBA hierarchy on a
host project item
Control name merged into
namespace
Phases 3 & 4:
Project Items and Controls
DEMO
VBA Integration Phases: Summary
Complex
Moderate
Simple
Phase 1: Initial Steps
Phase 2: Projects
STOP
Phase 3: (Optional) Project Items
STOP
Phase 4: (Optional) Controls
STOP
Note: Advanced Features can be added to any
completed VBA integration project.
Advanced VBA Features




VBA Extensibility Object Model
Digital Signatures
COM Add-ins
ActiveX Designers
Advanced Features
DEMO
Outline



Key benefits of VBA
Preparing for VBA Integration
Integrating VBA



What is APC
VBA Integration Architecture
Phases of VBA Integration
For more information…


http://msdn.microsoft.com/vba
http://www.summsoft.com/vba