Excel DNA and Ribbon & Forms interaction

Mar 8, 2012 at 2:09 PM

Govert and co.,

I'm going to ask a rather broad newbie question here; if this is the wrong place then please just reply with a suggestion of where I should go and I will do so.

I have a large (i.e. many thousands of lines of VBA code) add-in in Excel that I would to update. Excel DNA is a very attractive upgrade route since it will allow me to use Visual Studio and keep the code base out of the hands of the users (I don't rate Excel's native security). However I'm not entirely sure whether the exact functionality that I need exists, and, if it does, how to go about making it all happen.

My specific goal is to write an Excel 2010 add-in (no other versions need to be supported) that creates a populated ribbon with three buttons on it upon start-up. Each button calls a form that allows the user to perform some specific actions before executing the main body of code, the output of which is a new Excel workbook that contains a bunch of data imported from a text file and a sophisticated set of automated analyses. The exact workflow would be:

Create ribbon and buttons > User clicks button > Execute form code > Display form > User interaction > Execute main code

So the noob question is: how difficult is this in Excel DNA and what resources are available on the web to illustrate the methods I need to use? 

Muchas gracias in advance.

Matt

Coordinator
Mar 8, 2012 at 2:28 PM

Hi Matt,

Excel-DNA is the perfect tool for you. However, if you are concerned about code security, you do need to investigate and understand the issues with .NET decompilation and using an obfuscator. By default .NET does not secure your code much, though Excel-DNA can help by hiding it away a bit.

Otherwise, you can start by looking at these recent posts, both with some pointers to examples of going from VB to VB.NET with Excel-DNA;

http://excel-dna.net/2012/01/30/excel-vba-to-vb-net-with-excel-dna-and-netoffice/

http://sysmod.wordpress.com/2012/02/06/from-vba-to-vb-net-using-exceldna/

The main source of information if the Google group history at http://groups.google.com/group/exceldna.

I look forward to your specific questions.

Regards,

Govert

 

Mar 11, 2012 at 1:46 PM

Govert,

Thanks for the links, they are very helpful. My first dumb question however is more Visual Studio related. The forms I use in my VBA code I'm reasonably happy with, so I'd like to recreate them in VS 2010 Express (VB edition). The problem is that if I create a new project as a Class Library, then I can't really test the functionality I write into them since VS produces an error when I click "Play". What is the best way to go about development in this case? At the moment my workaround is to create a new project as a Windows Form Application and create the forms, making the *assumption* that I can import them into the Class Library later. However...

Matt

Coordinator
Mar 11, 2012 at 1:56 PM

Hi Matt,

If you make a Class Library project, you'll have to explicitly add the reference to System.Windows.Forms. To start it debugging in Excel you'd normally set the 'Start External Program' setting in the Debug tab of the project properties.

However, with Visual Studio Express edition you need another trick to set the debugging to an external program - I think the Express edition hides that setting in the IDE. You need to edit the .vbproj.user file to add the StartAction and StartProgram. Something like this:

From my test project user file MyFirstUDF.vbproj.user 
(<ProjectName>.<ProjectTypeExtension>.user): 

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> 
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Debug|'AnyCPU' "> 
    <StartAction>Program</StartAction> 
    <StartProgram>C:\Program Files\Microsoft Office\Office12\EXCEL.EXE</StartProgram> 
    <EnableUnmanagedDebugging>false</EnableUnmanagedDebugging> 
  </PropertyGroup> 
  <PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|AnyCPU' "> 
    <StartAction>Program</StartAction> 
    <StartProgram>C:\Program Files\Microsoft Office \OFFICE11\EXCEL.EXE</StartProgram> 
    <EnableSQLServerDebugging>false</EnableSQLServerDebugging> 
  </PropertyGroup> 
  <PropertyGroup> 
    <ProjectView>ShowAllFiles</ProjectView> 
  </PropertyGroup> 
</Project> 

-Govert

Mar 14, 2012 at 7:23 PM

Govert,

Do you have any experience with Office Primary Interop Asssembly installation problems? I have now attempted to install this:

http://www.microsoft.com/download/en/details.aspx?id=3508

On two separate Windows 7 machines without success. In both cases I can find references to it being installed in the manage programs location and also in the Windows\Assembly folder. However nowhere can I find an excel.dll file and there is no Microsoft.Office.Interop.Excel option in the add references dialogue in Visual Basic Express 2010.

This is annoying me greatly. I had begun to think it was a permissions issue since the first machine I installed it on was my work laptop and the company policy is to prevent registry alteration. However the second machine is my home PC that I have full administration rights over - no joy.

I posted in the Microsoft Developer Network forums in response to a similar problem - no further replies yet:

http://social.msdn.microsoft.com/Forums/en-US/Vsexpressvb/thread/0bec7efd-37aa-499c-846b-12e4c2327fa8

Anything you can advise would be greatly appreciated.

Matt

Coordinator
Mar 14, 2012 at 7:54 PM
Edited Mar 14, 2012 at 8:04 PM

Hi Matt,

My first reaction is that you should rather try the NetOffice assemblies (http://netoffice.codeplex.com). They're version-independent and don't need to be installed in the GAC - you just copy the files next to your Excel-DNA add-in, or evan pack them inside the .xll for easy distribution.

About your actual question - if the installation ran OK and the assemblies are perfectly happily installed in the GAC, it might still not show in the References dialog. Contrary to what you might expect, the References dialog is not displaying a list of assemblies from the GAC, but from various locations according to the list of 'Assembly Folders' specified in the registry keys here: HKLM\SOFTWARE\Microsoft\.NETFramework\.NETMinimumVersion\AssemblyFoldersEx\.

These StackOverflow question give some more detail and the right pointers:

http://stackoverflow.com/questions/871984/why-does-the-net-tab-in-the-add-reference-dialog-in-visual-studio-not-list-th

http://stackoverflow.com/questions/495973/getting-assemblies-to-show-in-the-net-tab-of-add-reference

http://weblogs.asp.net/jdanforth/archive/2003/12/16/43841.aspx

So it's not impossible that you might find that the Visual Studio Express edition has a different (restricted) view of what assemblies to show in the References dialog box. Maybe there is some kind of filter or setting that is missing.

 

Anyway, this should give you some ideas for investigating the unexpected behaviour you are seeing.

Maybe you can look for the actual file Microsoft.Office.Interop.Excel.dll on your computer, then in the References dialog you Browse, and pick it from there.

 

Let me know what you find out!

Regards,

Govert

 


Mar 14, 2012 at 8:22 PM

Govert,

Thanks again for the quick response. Unfortunately the problem appears to be simply that microsoft.office.interop.excel.dll or excel.dll do not install. They're NOT on my hard-disk as far as I can see. You cannot explore the c:\windows\assembly folder with Explorer, so I tried via the command prompt and discovered that whilst there's supposedly a reference to office there's no actual folder or files. A full disk search has not yielded anything.

Not really sure what to do next. I take your recommendation of using NetOffice, but at the moment I was just getting used to Visual Studio without starting to integrate with Excel.

Matt

Coordinator
Mar 14, 2012 at 8:48 PM
Edited Mar 14, 2012 at 8:50 PM

Hi Matt,

There is no file called "excel.dll" that I know of.

If you have successfully run the Excel 2010 Primary Interop Assemblies (PIA) installer that you linked to earlier, you should have a directory like this:

C:\Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\14.0.0.0__71e9bce111e9429c

with a file inside called:

Microsoft.Office.Interop.Excel.dll (1 550 200 bytes)

You should check this from a command prompt, and not via the Windows Explorer (which has a special display handler for the GAC).

If you don't have this file, then your installation of the PIA installer must have failed, and you can check for an install log or event log entry or something. I think you should investigate.

Otherwise, using NetOffice is not an alternative to Visual Studio. It's just a set of libraries that give you alternative interop assemblies to the version-specific Primary Interop Assemblies.

-Govert 

Mar 14, 2012 at 9:03 PM

Govert,

Success! I was looking in windows\assembly\GAC, rather than GAC_MSIL... d'oh!

I copied the file to a browseable folder and the problem was solved. Many thanks for your quick responses; that would have been hours of my life wasted. I owe you a beer if you're ever in London!

Matt

Jun 18, 2014 at 3:51 AM
Hi govert, your links in the article of your first response are broken in http://excel-dna.net/2012/01/30/excel-vba-to-vb-net-with-excel-dna-and-netoffice/ - the external links talking about the RiskGen add in. I too am deciding whether to take the plunge and migrate a 15 year old vba add-in to Excel DNA...but I have 5-6 Excel UserForms that I'm looking for samples on how to migrate/replicate in Excel DNA. Any other sources of samples other than the mentioned articles?
Coordinator
Jun 18, 2014 at 9:39 AM
Ah - I see those links have rotted. I don't really have an example where forms are migrated.

Are you forms complex? Do you use RefEdit controls?
Then it can be a bit tricky. Otherwise you should just have to recreate the forms, and then copy-and-paste most of the code behind.

I'm happy to have a look and help a bit though - you are welcome to contact me directly.

-Govert
Jun 18, 2014 at 5:13 PM
Thanks govert. I guess I've seen a lot of posts of people asking about it (also the contrast between Win Forms and WPF Forms)...so it seemed to be a bit of an issue. I haven't attempted, but you are just saying it is as simple as making a new Win Form and calling ShowDialog() on it from a ribbon button click?
Coordinator
Jun 18, 2014 at 6:18 PM
It depends a lot on how much interaction there is between the form and Excel. If the form is purely to display or gather some information that will then be used after the form is dismissed, then I don't think there are any issues. If the form needs to interactively drive Excel, there might be some issues with the coordination. I don't have much experience with this myself.

-Govert