Converting XLA add-ins to COM Add-ins with VB6.0

1. Converting XLA add-ins to COM Add-ins with VB6.0 Simon Murphy simon.murphy@codematic.net Developer – Codematic Ltd

2. Spreadsheet background • Up to 200 Mb size • Up to 1 Million formulas • 1-10,000 unique formulas • 5-10,000 lines of VBA • £Billions in values • Often linked to other technologies such as OLAP, ADO, COM or .net etc. • Finance, Banking and Sales and Marketing areas • Development cost up to $1M • Active member of Eusprig – European Spreadsheet Risk group – dedicated to raising awareness of dangers and error rates in commercial spreadsheets www.eusprig.org

3. Agenda • Solution design patterns • What is COM? • Reasons for using COM • Reasons for avoiding COM • Automation Add-ins • Intro to toolbar XLA • Intro to VB6 Add-in projects • Discussion of IDTExtensibility2 • Structure of an Excel Add-in • Code and test • Package and deployment • .net

4. Security • Is everybody’s concern • Spreadsheets can be used as a staging board for privilege escalation (with your login details!) • Consider SD3 +C • Secure by • Design • Default • Deployment • Communication • Threat Modelling – Assets, Threats • Threat Types – STRIDE • (Spoofing, Tampering, Repudiation, Information Disclosure, Denial of Service, Elevation of Privilege) • Threats – rate with DREAD • (Damage potential, Reproducibility, Exploitability, Affected Users, Discoverability) • Spreadsheets (all flavours) are fairly insecure • Compiled UDFs (.net, COM, XLL) and Database servers can help • Set macro security to high and use code signing certificates. • See Microsoft MOC 2840A – Implementing security for more info.

5. Solution design patterns

6. What is COM? • COM – Component Object Model, also known as ActiveX and automation. • Allows interoperability between heterogeneous systems • Revolves around registering (and then searching) interfaces in the Windows registry • Excel looks in the registry at certain times and can load and use what it finds. • VB is the language of COM, but COM components can be written in many languages, including .net • Can be expensive performance-wise

7. Possible reasons for using COM add-ins • Improved security (from meddlers and malicious users) • Improved intellectual property protection • Better forms engine, and richer UI features • Better development tool support • Installed so may avoid macro security warnings (depends on settings) • Code is out of the VBAIDE (Project Explorer) • If functionality is used by multiple workbooks • If functionality is used by multiple office apps • If embedded workbook not required • If determined to use a none VB language (eg: C#/C++) • Possible application stability? • Possible execution speed improvements???

8. Possible reasons for avoiding COM add-ins • VB6 is out of mainstream support • Targets Office 2000 or newer not 97 • .net may supercede COM • More complex development than XLAs • More complex testing than XLAs • More complex deployment than XLAs • Solution design uses an internal workbook • VB runtime library required on client • Access to development tools may be difficult • Security concerns of potential users

9. One performance test

10. 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

11. Agenda (again) • Solution design patterns • What is COM? • Reasons for using COM • Reasons for avoiding COM • Automation Add-ins • Intro to toolbar XLA • Intro to VB6 Add-in projects • Discussion of IDTExtensibility2 • Structure of a COM Excel Add-in • Code and test • Package and deployment • .net

12. Toolbar • The issue: • Opening files from other people causes the reviewing toolbar to appear, there is no obvious way to turn this off. (possible registry setting?) • Solution • Capture the workbook open event and close the toolbar, that seemed too early so capture the activate event and close it there. • Requires no use of worksheet, may be useful in other office apps. Just for me so no deployment problems, active all the time so better out of the VBA IDE – Ideal candidate for a COM add-in.

13. Migration • Code sensibly in VBA, with a view to migration • Provide a user interface • create a class to wrap the application to trap events • Add a global variable to represent the application as an instance of this class • Create folder for VB project • Export Excel VBA code files to VB project folder • In VB6 create a new add-in project in that folder • Set designer to Excel • Import code files • Set application to be correct type • Manage application object lifecycle • manage user interface actions and lifecycle • Add start up code • Debug and deploy.

14. IDTExtensibility2 • Private Sub IDTExtensibility2_OnConnection( • Private Sub IDTExtensibility2_OnDisconnection( • Private Sub IDTExtensibility2_OnStartupComplete( • Private Sub IDTExtensibility2_OnAddInsUpdate( • Private Sub IDTExtensibility2_OnBeginShutdown( • (Populated by a wizard, just need tidying up)

15. Basic COM Add-in Structure

16. 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

17. Questions? • simon.murphy@codematic.net • Spreadsheet consulting, reviewing, maintaining, rescuing, migrating, add-in development etc. • Staff coaching, mentoring and training • Websites • www.codematic.net • www.xlanalyst.co.uk