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: also has links to tutorials 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 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
1. Create a user-defined function in Visual Basic
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
Ross McLean uses the SharpDevelop IDE; VB Express is similar.

Stage 3: Adding 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.

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++ :

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

Note that is old, not updated since 2006.

Microsoft and other sources
Convert VBA to VB .Net and C#
Free Book – Upgrading Microsoft Visual Basic 6.0 to Microsoft Visual Basic .NET
Converting Access VBA to VB.NET – General Principals
Converting Code from VBA to Visual Basic .NET
Office 2003
For more information, see Introduction to Visual Basic .NET for Visual Basic Veterans.
Introduction to Visual Basic .NET for Visual Basic Veterans
Help for Visual Basic 6.0 Users
Appendix A
What’s New and Different in VB .NET

About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing 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

  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 …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,

  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 – 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: Logo

You are commenting using your 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 )

Connecting to %s