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

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