Integrate the VBA 6 Development Environment into your

Integrate the VBA 6 Development Environment into your Application Rebecca Rinner Senior Software Engineer Summit Software Company 4 -307

Outline l l l 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 l l Your application becomes a platform Write portions of your application in VBA Ø Ø Ø l Change features after you ship Add features after you ship Eliminate one-off feature requests Create a 3 rd party community

“Got VBA? ” l VBA throughout Office 2000 Ø l l Outlook, Front. Page 100+ shipping applications New announcements every week

Types of Applications l l Traditional “thick client” Multi-threaded applications Ø l Multi-threaded designer VBA in the Middle-tier Ø Ø Allows customizable business objects Duwamish sample on MSDN Thin client Check out the VBA Pavilion! l

An Application with VBA DEMO

Outline l l l 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… l. Visual Basic Editor (VBE) ØProject Explorer ØCode Editor ØImmediate Window ØProperties Window ØLocals, Watch, . . . ØVBE Object model § “Visual Basic for Application Extensibility” § Defined in vbe 6 ext. olb Code. Module Command. Bar. Events VBProject VBComponent References. . .

VBA Building Blocks. . . Project Hierarchy VBA Project Items Host project Items Controls* User. Forms 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. . . l 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. . . l 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. . . l Project items. . . Ø Code module § § Ø Class module § Ø Contain VBA code only Global Creatable VBA classes User. Forms and Other Designers § User. Forms (built-in) § § Can be used to build UI for your application VB 6 Designers

VBA Building Blocks. . . l 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 l l 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. . . l 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 l l 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? l l l 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 3 rd party community supporting your product Required for VBA integration

Object Model Design Tips l Use standard constructs and behavior Ø Ø Ø l Events provide Ø Ø Ø l 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 l l l 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 ? ! Use APC

What is APC? l l l 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? l l l 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 Project. Items Project. Item Controls Host Project Item Apc Project Code Module Class Module Designer Item User. Form 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 l l Create the APC Host Associate the Application Object Manage windows and events Show the VBA IDE

Phase 1: Initial Steps Initializing APC l What is the Application object? Ø Merges methods & properties into namespace Sub Main Msg. Box “Hello” r = Application. Foo r = Foo End Sub Msg. Box 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 l l l VBA IDE is a top-level window Window parenting Tracking the active component Ø l Forward accelerators to active component Re-entrant message loop Ø Isolate to a single set of routines

Phase 1: Initial steps DEMO

Phase 2: Projects l l Project is the unit of persistence Create, Save, Load a Project Execute code An application can have multiple VBA projects

Phase 2: Projects Persistence l Requires OLE structured storage (IStorage) Ø Disk-based § § Ø In-memory § l l l In your document file In standalone file Stored in a database as a BLOB Must be transacted IApc. Project supports IPersist. Storage For VB Developers, APC supports: Ø Ø Storage Object Stream Object

Phase 2: Projects Executing VBA code l Named macros -- use the Run method The. Project. Run "This. Document", "Main" l Available for Project. Item, Procedure, and Project IApc. Project. Item: : Run IApc. Procedure: : Run IApc. Project: : Run l Macros dialog. . . Dim The. Scopes As MSAPC. Scopes Set The. Scopes = The. Project. Create. Scopes The. Scopes. Macros. Dialog. Show

Phase 2: Projects Other uses l Code sharing / reuse Ø Ø l 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 l l l 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 l Host project items are simply COM objects Ø Ø Ø Must derive from IDispatch and support IProvide. Class. Info Method’s parameters strongly typed and named Coclass exposes a source interface (events)

Phase 4: Controls l l VBA controls are simply COM objects Types Ø Ø l l Built-in COM objects External Active. X 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 l l VBA Extensibility Object Model Digital Signatures COM Add-ins Active. X Designers

Advanced Features DEMO

Outline l l l Key benefits of VBA Preparing for VBA Integration Integrating VBA Ø Ø Ø What is APC VBA Integration Architecture Phases of VBA Integration

For more information… l l http: //msdn. microsoft. com/vba http: //www. summsoft. com/vba