Docstoc

Excel 2003 Vba Find After

Document Sample
Excel 2003 Vba Find After Powered By Docstoc
					Using and Extending Excel with
        Visual Studio
A high level discussion of the main options


      Simon Murphy
      simon.murphy@codematic.net
      Developer – Codematic Ltd
      www.codematic.net – Excel Development.

DDD
                            Agenda
•    Background
•    Patterns and technical options
•    VSTO demo
•    The main questions (how to choose)
•    Automation v COM Add-ins
•    Demos
      – Com Add-in
      – Automation Add-in
• Deployment
• Justification
• Summary & questions


    DDD
                           Background
•    On-line confusion
•    No (easy to find) succinct info from MS
•    Mild panic after VB6 retirement (VB6 ~ VBA)
•    Few books (changing rapidly)
•    Why target Excel?
      – 400 Million installations
      – Very powerful, very programmable client
          •   ~   200 objects
          •   ~   80 events
          •   ~   200 enumerations
          •   ~   4,500 methods and properties
• Note: this presentation pretty much ignores VBA
    DDD
           Design Patterns
• Workbook with code behind
• Excel Add-in
  – COM Add-in
  – Automation Add-in
• Automation executable




DDD
     Working with Excel - The options
•    VSTO (Visual Studio Tools for the Office System)
•    COM Add-ins
•    Automation Add-ins
•    Standard automation projects
      – Open, Automate, use features and Close Excel
      – Excel 97 maybe earlier?
• XLL Add-ins
      – Native win32 DLLs (ie pretty much C++)
      – Excel 4.0 maybe earlier?
      – Still very relevant
• VBA Add-ins – Not discussed here
    DDD
               VSTO Demo
• VSTO (Visual Studio Tools for the Office
  System)
  – Special VS template projects that incorporate
    strong office links.
  – Closest replacement for workbooks with
    embedded VBA.
  – Excel 2003 professional or better
  – VSTO 2003 US only VSTO 2005 any locale.
  – Locale still very much an issue for other .net
    developments
 DDD
               4 Big Questions
1.     What version of Excel?
2.     Function or Feature?
3.     Application or Workbook Level?
4.     Who initiates connection?
     •   Or Hosted v None Hosted




 DDD
       1. Which Version of Excel
• If Excel 2003 all options are possible
• Anything less than 2003
  (professional)
   – VSTO (Visual Studio Tools for the
     Office System) is not possible
• Anything less than Excel XP
   – Automation Add-ins not possible       Excel Versions ~

• Anything less than Excel 2000            2003 – 15% - 60M

   – COM Add-ins not possible              2002 – 35% - 140M

• Total Office Installs – around 400M      2000 – 40% - 160M
                                           1997 – 10% - 40M

 DDD
         2. Function or Feature?
• Function (as in worksheet function)
   – Automation add-in
       • Visual Studio 6 (generally VB6.0)
       • Visual Studio .net
       • VSTO not appropriate
   – XLL
       • Best performance
• Feature
   – COM Add-in
   – VSTO
   – Standard automation

 DDD
 3. Application or Workbook Level?
• Application
   – COM Add-in
   – Automation Add-in
   – Standard automation project
• Workbook
   – VSTO
   – VBA


 DDD
   4. Who Initiates connection?
• Excel (hosted)
   – VSTO
   – COM Add-in
   – Automation Add-in
• Your application (not hosted)
   – Standard automation project




 DDD
                     Demo
• COM Add-in – load up events
  – Application level functionality
  – Excel 2000 or better
  – Visual Studio 5 >>




DDD
       Automation v COM Add-in
• Automation Add-ins are COM add-ins that can be
  called directly from a worksheet cell.
• COM Add-ins
   – Must Implement IDTExtensibility2
   – Generally load on Excel start up (can be changed)
• Automation Add-ins
   – May Implement IDTExtensibility2 (discouraged)
   – Loaded on demand only



 DDD
                    Demo
• Automation Add-in – Worksheet formula
  – Directly callable worksheet formulas
  – Excel XP (2002) or better
  – Visual Studio 5 >>




DDD
       Development & Deployment
• Excel is written in unmanaged C with COM
  interfaces for VBA and other apps.
• VBA/XLL has the easiest initial deployment
  (updates are more complex though)
• VSTO has the most flexible and overall
  easiest deployment (server or local)
• COM Add-ins & Automation Add-ins
   – standard COM local registry issues.
   – Should have a shim (Custom CLR loader)
 DDD
                  COM Shims
• What are they?
  – COM dll that loads Mscoree.dll which then
    loads our managed component
  – Excel>>COMSHIM>>Mscoree>>MyAddin.dll
• Why use them?
  – Security (code signing)
  – AppDomain Isolation
      • Protect other apps from failures in our component
      • Protect our component from other failures


DDD
                 Why bother?
• Why change from the current VBA / COM
  approach?
  – Deployment and maintenance easier
      • 1 version
      • Write controlled location
      • Cleaner tiered architecture
  – .net framework functionality
  – Visual studio .net IDE better than VBAIDE
  – Enterprise code reuse
  – .net security may be better than macro
    security?
DDD
                 When to bother?
• Note: my personal guess only!
• Mainstream in 2007!
• Excel 12 due mid/late 2006
   – Excel 12 is a genuine improvement
• VSTO 3 will probably match that time scale
   – Very likely to include more application level features
       • Task panes, Add-ins, etc
• Approx 12 months to get reasonable uptake
• VBA will be looking very shabby by then
• VSTA (Visual Studio Tools for Applications) will have fully
  arrived
• .net framework should have reached saturation level.


 DDD
      Resources




DDD
                       Summary
                    Solution Type
                     Workbook     Application    Custom     Automation
                     With Code Level Feature    Worksheet     Project
Technology Type        Behind                    Function

VSTO                     
COM Add-in                            
Automation Add-in                                  
Win32 XLL                                         
VBA                                              
Standalone exe                                                  

 • VSTO for modern workbook based solutions
 • COM Add-ins for application level features
 • Automation Add-ins for worksheet functions

    DDD
                   Finally
• Any questions?



• Simon Murphy
• simon.murphy@codematic.net
• www.codematic.net – Excel Development.


 DDD

				
DOCUMENT INFO
Description: Excel 2003 Vba Find After document sample