From VBA to VB.Net using ExcelDna

This is a collection of links for learning about VB.Net and ExcelDna. Firstly, here are my two documents: a collection of resources and a worked example of migrating an addin to VB.Net

Getting Started with VB.Net using ExcelDNA.docx

Practice project converting XLAM to ExcelDna.docx  This shows the steps involved in taking a simple addin that creates a Table Of Contents worksheet in a workbook. It illustrates the basics of creating a dll, a Ribbon button, and access to the Excel object model. It does not use forms. Source code zip

Here for convenience are the links from the first document:
The latest Excel-DNA version is available on the CodePlex site: http://exceldna.codeplex.com also has links to tutorials
http://groups.google.com/group/exceldna Primary support channel

All applications use the ExcelDna.xll addin. The stages of learning successively add files:
1) Using only a text file MyAddin.dna which includes the source code text.
2) Add an external dll which you create from VB.net source code and compile using either the vbc.exe compile or the Visual Basic Studio IDE. I used the free Express edition.
3) Ease the transition using the official MS Primary Interop Assemblies (PIA) or third party libraries such as NetOffice
4) Simplify the deployment by packing the components into one .XLL file.
Stage 1: using only a .DNA test file
Getting Started with ExcelDna – Do this first
http://exceldna.codeplex.com/wikipage?title=Getting%20Started
1. Create a user-defined function in Visual Basic

http://www.blog.methodsinexcel.co.uk/2010/09/03/excel-da-hello-world-function/
Similar to above. Ideal if you want to write User Defined Functions (UDF), or want to copy a UDF from VBA to DNA for a quick and easy-ish performance improvement.

Stage 2: Compiling  a .DLL
http://www.blog.methodsinexcel.co.uk/2010/09/22/writing-an-excel-dna-function-using-an-ide/
Ross McLean uses the SharpDevelop IDE; VB Express is similar.

Stage 3: Adding Intellisense to a DNA solution
http://www.blog.methodsinexcel.co.uk/2010/10/28/ading-intellisense-to-a-dna-solution/
Download the PIA .Zip file from the link in that post and extract to your hard disk. Setup up a DNA project in the normal way, go to the “Project” menu, “Add reference”, then “.Net assembly Browser” then go and find the PIA folder you just extracted, and pick the right version for your needs.

Also see NetOffice, a version-independent set of Office interop assemblies put together by Sebastian Lange.
http://excel-dna.net/2012/01/30/excel-vba-to-vb-net-with-excel-dna-and-netoffice/
http://netoffice.codeplex.com/
http://groups.google.com/group/exceldna/browse_frm/thread/f356a30adf9130d0/c7625c8836affd3d

Stage 4: Simplify deployment by packing the components into one .XLL file.
ExcelDnaPack is a command-line utility to pack ExcelDna add-ins into a single .xll file.
Going further to C#
Charles Williams recommends this automatic code converter to ease the transition from VB to C# and C++ :
http://tangiblesoftwaresolutions.com/

Background reading
Currently (Feb 2012) the only documentation on ExcelDna is effectively the google group.

Note that http://exceldna.typepad.com is old, not updated since 2006.

Microsoft and other sources

http://blogs.msdn.com/b/pstubbs/archive/2004/01/15/59049.aspx
Convert VBA to VB .Net and C#

http://msdn.microsoft.com/en-us/vstudio/ms788236
Free Book – Upgrading Microsoft Visual Basic 6.0 to Microsoft Visual Basic .NET

http://www.upsizing.co.uk/Art52_VBAToNet2.aspx
Converting Access VBA to VB.NET – General Principals

http://msdn.microsoft.com/en-us/library/aa192490%28v=office.11%29.aspx
Converting Code from VBA to Visual Basic .NET
Office 2003
For more information, see Introduction to Visual Basic .NET for Visual Basic Veterans.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vboriintroductiontovisualbasic70forvisualbasicveterans.asp
http://msdn.microsoft.com/en-us/library/kehz1dz1(vs.71).aspx
Introduction to Visual Basic .NET for Visual Basic Veterans

http://msdn.microsoft.com/en-us/library/kehz1dz1(v=vs.90).aspx
Help for Visual Basic 6.0 Users

http://oreilly.com/catalog/vbdotnetnut/chapter/appa.html
Appendix A
What’s New and Different in VB .NET

Excel Developers’ Conference Jan 25 London report

http://xlconf.wordpress.com/2011/11/22/uk-excel-developer-conference-london-january-2012/

Simon Murphy of Codematic had lined up an array of speakers and topics that drew a packed crowd. Morten Siersted’s company F1F9 sponsored the lunch for all the delegates.  Simon opened the conference with a summary of the evolution of Excel development and closed it with a personal view of project management.

Stephen Allen of ACBA spoke on his auditor’s Electronic Working Papers (EWP) application, which he developed from the need to demonstrate a chain of evidence from the system being audited through the risk to the audit finding, conclusions, and recommendations. He describes this as “a recursive programming approach required to rearrange branched thought processes into linear output for reporting”.  A trial copy of ACBA-EWP can be requested from ACBA (UK) where you can also download a demonstration workbook.

Bob Phillips spoke on Personal BI (Business Intelligence) made possible by the PowerPivot addin for Excel 2010. He covered the DAX language and mentioned the work of Tom Gleeson, the datasmith. There were some questions from the Access developers in the audience, as Bob indicated that PowerPivot is the future of data access and reporting by Excel.

Dr. Mike Staunton of the London Business School spoke on his approach to coding style to permit portable code from VBA to VB.Net and C#. He likes the free tool ExcelDNA and TangibleSoftwareSolutions.com code translators, who feature his Wilmott article on VBA to C#.Net.

Mathias Brandewinder spoke on VSTO, showing that version 2 was not the bugbear that version 1 had been. He demonstrated several simple applications including Ribbon and Custom Task Panes; see VSTOStocks.codeplex.com. For UDFs he recommends ExcelDNA and XLL-plus. The audience were with him right up to the point he mentioned that VSTO needs a Visual Studio 2010 Professional licence at $800. However, ask your national MS developers’ contact who may know of special offers with eg Technet.

Charles Williams  recounted his experience of migrating 8000+ lines of VBA code in his FastExcel addin to C++. This was necessitated by the introduction of the 64-bit version of Excel. To save him time, he used the Plantech XLL+ addin for Visual Studio and the STL/Boost library. His blog at decisionmodels.com is worth following for tips on improving Excel performance which can be better done by more efficient algorithms than language tweaks.

Morten Siersted of F1F9 spoke of the FAST modelling standard. Aimed at the general spreadsheet financial modeller, it stresses virtues such as transparency (comprehensibility) and being “beautifully simple”. This is achieved through a few rules for organising code in consistent formula blocks, grouped detail rows. He enlivened the rules by comparing their recommendations of staged inputs to a TV cook and font color coding to plumbing diagrams.

Simon provided the usual swag that conference attendees love to collect and also gave away draw prizes – I had contributed my book on Spreadsheet Check and Control, and the Q-Validus Spreadsheet Safe training manual. Some of us,because of our connection with EuSpRIG, were interviewed by the BBC for their upcoming programme on spreadsheet risks in “More or Less” (about statistics and economics) to go out on the World Service. It was good to meet so many faces belonging to people I had hitherto known by email or newsgroups, such as Simon Hurst, Anthony Prince, Andy Pope, Russell Proctor of BetterSolutions, Philip O’Brien of Pasigraphy, and colleagues from previous work. I’ve taken away some New Year resolutions for things to work on!

Talk at SoftTest 26 Jan 6pm Dublin on “Test Centres of Excellence”

http://softtesteventjan2012.eventbrite.co.uk/

Annex meeting room in the Central Hotel, 1 – 5 Exchequer Street Dublin 2 Thursday, 26 January 2012 from 18:00 to 20:00 

Too often companies are relying on hard work, experience and luck rather than planning and executing tests in an appropriate and focused way. With increasing demands these systems grow to be more inefficient, unreliable, costly and can ultimately become a serious risk to the business.

So how are organisations meeting this challenge head on? The answer is by developing and growing Test Centres of Excellence (CoE). A Test CoE is a professional service organisation that delivers testing services to the IT group and the business. It comprises a dedicated and independently managed team of people with a core competency in testing, operational processes to support testing for the enterprise and supporting tools and technologies.

Presenter: David Cass. David is a Senior Manager in Strategy and Operations Consulting for Deloitte Ireland with over 9 years experience in software testing, test programme management, test strategy development and organisational change. David specialises in developing organisational test strategies, end to end quality assurance and test organisation planning & optimisation for large organisations.

Microsoft Translator VBA code sample

MicrosoftTranslator.com provides an API that is free up to 2MB per month.
MS are moving from the Bing appID to an authorization token for their translator but as this forum post says have not brought the documentation up to date and don’t have VBA examples. So  I’ve decided to post my own so people can suggest improvements.

MicrosoftTranslator VBA module

Happy new year!

Twelve years ago I wrote the book on the Euro Changeover in IT systems (“Managing the Euro in Information Systems”, Addison-Wesley, 1999). I wonder will 2012 see the year some countries have to switch back? Internally it should be easy enough if they adopt 1 new NC (eg púnt nua) = 1 euro then national prices remain the same. International prices will then need foreign currency handling. Ah well. At the time (1999-2001) I had believed there were controls in place on financial discipline but the realpolitik proved otherwise.

On the side of End User Computing and Compliance and Best Practices and Spreadsheet Controls and all those good things, well, I’m still developing techniques, tools, processes, and training for organizations to get to grips with the spreadsheet Hydra – ask me for more info!

Happy Christmas and good wishes for 2012

Our card this year is Megan’s photo of the Festival Arbres et Lumières in Geneva last Christmas. Her calendar for 2012 features photos she took at the Annecy Venetian Festival.

http://www.meganobeirne.com/

Dictionary VBA Class update

I’ve uploaded a new version of the Dictionary Class and a helper KeyValuePair Class. This exposes the KeyValuePairs collection so iteration is now very fast, about 2 seconds to iterate 1 million items using a for each loop:

 
dim oKeyValuePair as KeyItemPair
For Each oKeyValuePair In td.KeyValuePairs ' direct access to collection
   var = oKeyValuePair.Value 'either of .key,.value
Next

Updated 25-Nov-11: The need for the helper class is to allow updates to the .Item without changing the order.