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

Advertisements

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA, ExcelDna and tagged , , . Bookmark the permalink.

9 Responses to From VBA to VB.Net using ExcelDna

  1. ross says:

    I saw you were asking a few questions recently Patrick, glad to see you getting into the DNA world, good isn’t it
    Ross

  2. MSimms says:

    Thanks for this compilation Patrick. I would be moving to C# instead of VB.Net. The latter just hasn’t gained any traction in the dev community. The main reasons for moving to dot-net vs. VBA of course is the superior dev environment…no worries about references and DLL function declarations, etc. The biggie however is the source code protection which VBA never had. If I create an addin, I don’t want it to be stolen.

    • sysmod says:

      Thanks, Ross, for pointing me to ExcelDna originally!
      Mark, C# is next target, first I want to wrap up VB.Net with a userform project. For better source code protection I think one needs Dotfuscator or equivalent for any .Net language, might have to look at that too.

  3. MSimms says:

    Yeah Patrick, thanks for pointing out .NET Reflector at http://www.reflector.net/compare/ …looks like it reverse engineers the DLL’s….pretty impressive. That being said, if you obfuscate the code, that makes it a really lengthy process to understand what it is doing since variable and object names will be so obtuse. Interestingly, I have a code obfuscator for VBA….I just never employed it or tested it out recently.

  4. Patrick,
    Nice compiled article. I will make a blogarticle about it.
    It has taken some years but now You’re up to speed with .NET. It’s funny, for some years ago it was much rant about .NET and VSTO in the online Excel community and people like me were not seen as “true” Excel developers. Now people have forgotten much of it and are happy user of .NET & Excel. Progress seems to be the word.

    Thanks and please keep up the good work Patrick!

    Kind regards,
    Dennis

  5. Pingback: From VBA to VB.NET using ExcelDna « VSTO & .NET & Excel

  6. Tom says:

    Hi Patrick,
    really like the way your pulled all the ExcelDNA stuff together. I have always preferred simple DLLs over XLLs hence my negligence of ExcelDNA uptill now. Additionally so as with XLLs the user has to make one additional step of installing your AddIn, whereas, with a DLL (if loaded dynamically) no installation is needed.

    I always thought it would be much better to simple have a simple DLL generator for VBA that could easily export the code for you. Hence played with creating an AddIn for just that – http://www.analystcave.com/vba-compiler-add-in-to-vb-net/. Let me know your thoughts!

  7. Debasaus says:

    How does one extend the use of UDFs into access?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s