VB.Net code into excel functions - some help?

[Agustin Ferreira]

Hi, 

I have a long list of functions written in VB.Net code, I want to implement them into excel but I am finding much difficulty in doing so. 

This is what I have been trying:

-  I'm using Visual Studio Community 2013

-  My code is in a Vb.Net Class library project with ExcelDNA installed. 

-  I run the code, go to the bin/debug folder and copy three files to a folder on my desktop: .dna file, .dll file and -AddIn64.xll file. (I have read on various pages that the .dll      file is not needed for distribution, but If I'm right this is only for ExcelDNA written in C#?) 

-  In Excel 2013, I load the .xll file in the Addins window. Thus far Excel loads it with no problem, I am not prompted to enable macros or any such thing. 

-  I click on an empty cell and enter '=V' to see if my function will popup in the list of available functions. It doesn't

-  I try using my function despite it not appearing as an available function. Excel returns '#NAME?'

through my own troubleshooting these are things I have changed:

- used the AddIn64.xll instead of AddIn.xll file (my PC is x64, although most all PCs in my workplace are x32) NOTE: when loading excel gave a warning that the .DNA config file could not be found. I simply added a '64' to the end of the .DNA filename and then it loaded.

- added 'Imports ExcelDna.Integration' to the top of my .vb file in Visual studio

- tried loading Addin on a coworker's computer? (I have a trial version of MS office, if that matters) went exactly as when I loaded addin in my computer. loads fine, function(s) won't appear. 

Here's sample code for one of my functions

    Public Function V_TdbW(Tdb, W, P)

        ' This function calculates Moist air volume given dry bulb temperature(F), humidity ratio(lblb), and pressure(psi).

        ' ASHRAE Fundamentals (2009) ch. 1 eqn. 28

        ' V_TdbW (o) Specific Volume [ft3lb]

        ' TDryBulb (i) Dry bulb temperature [F]

        ' HumRatio (i) Humidity ratio [lbH2OlbAIR]

        ' Pressure (i) Atmospheric pressure [psi]

        V_TdbW = 0.370486 * (FTOR(Tdb))(1 + 1.607858 * W)(P)

    End Function

That's pretty much it. I tried all of the links and tutorials I could find online. I found a lot of the links were no longer working, such as the Methodsinexcel.co.uk tutorials. Other tutorials explained how to implement a C# addin which is not what I need. I think a tutorial video on youtube would be extremely beneficial, or one simple tutorial online. 

[Patrick O'Beirne]

Did you try

http://www.sysmod.com/vba-to-vb.net-xll-add-in-with-excel-dna.pdf

[Govert van Drimmelen]

Hi Agustin,

The most likely issue is that your function is inside a Class but the function is not "Shared", or that it is inside a Module, but the Module is Not Public.

Change your code file to only have the following:

Public Module MyFunctions

   Public Function V_TdbW(Tdb, W, P)

        ' This function calculates Moist air volume given dry bulb temperature(F), humidity ratio(lblb), and pressure(psi).

        ' ASHRAE Fundamentals (2009) ch. 1 eqn. 28

        ' V_TdbW (o) Specific Volume [ft3lb]

        ' TDryBulb (i) Dry bulb temperature [F]

        ' HumRatio (i) Humidity ratio [lbH2OlbAIR]

        ' Pressure (i) Atmospheric pressure [psi]

        V_TdbW = 0.370486 * (FTOR(Tdb))(1 + 1.607858 * W)(P)

    End Function

End Module

That should work.

Alternatively you might make it:

Public Class MyFunctions

     Public Shared Function V_TdbW(Tdb, W, P)

        ' This function calculates Moist air volume given dry bulb temperature(F), humidity ratio(lblb), and pressure(psi).

        ' ASHRAE Fundamentals (2009) ch. 1 eqn. 28

        ' V_TdbW (o) Specific Volume [ft3lb]

        ' TDryBulb (i) Dry bulb temperature [F]

        ' HumRatio (i) Humidity ratio [lbH2OlbAIR]

        ' Pressure (i) Atmospheric pressure [psi]

        V_TdbW = 0.370486 * (FTOR(Tdb))(1 + 1.607858 * W)(P)

    End Function

End Class

Excel-DNA does not register instance methods.

Now for some of the other questions:

* The easiest way to get started is to make a new Class Library project, and then install the "Excel-DNA" NuGet package. That configures everything properly, and the displayed Readme.txt file gives you some code to just paste into a file and run. I suggest you try that.

* You need to distinguish between a Windows installation that is 64-bit, and an Excel (or Office) installation that is 64-bit. Even on a 64-bit installation of Windows, the default Office install is still 32-bit. You must choose the -AddIn.xll or -AddIn64.xll according to the bitness of the Excel installation. Most common these days is a 64-bit Windows with a 32-bit Excel, in which case you still need the -AddIn.xll rather thatn -AddIn64.xll. If you get it wrong, the message Excel shows will be something like: "The file format and extension of ... don't match. The file could be corrupted or unsafe. ...". In that case you need to other version of the .xll.

* If you install the NuGet package, a "packed" version of the add-in will also be created. You can then either redistribute:

  - the MyProject-AddIn.xll, MyProject.dna and MyProject.dll (or the AddIn64.xll as appropriate), or

  - Only the MyProject-AddIn-packed.xll (or MyProject-AddIn64-packed.xll as appropriate). You can freely rename the -packed.xll files to anything you like, say MyProject.Version1.xll before distributing.

I'm not so good at making videos or writing tutorials, but I'm happy to answer any of your questions here.

-Govert

[Agustin Ferreira]

Govert, 

Thank you. My functions appeared immediately once I applied the change you indicated. Following that and a little more troubleshooting, I was able to get my functions to work. I had not seen the link that Patrick posted, but the link did help me a little by demonstrating how to declare the attributes of my new excel functions. 

The only thing I still haven't figured out is the 'hover text'. I have set the category, description and name of my new functions through the excel attribute property, however when I start entering the function in an empty cell the function's description ('hover text' as dubbed by coworker) does not appear. Here's an image of what I'm talking about:

The VALUE function displays some text describing the function next to it when my cursor hovers over the function or when I have the function selected in the list. I would like my functions to do the same. 

I tried setting the HelpTopic argument of the excel attribute but that did not help.

[Govert van Drimmelen]

Hi Agustin,

The on-sheet IntelliSense (what you call the 'hover text') is not supported by Excel for any kind of user-defined functions, whether you are using VBA, C/C++ or Excel-DNA to define your own functions.

The information you fill in for the category, function and argument descriptions is only visible in the function wizard ("Function Arguments") dialog that you get when you press the "fx" button.

We have started a project to build on-sheet IntelliSense for user-defined functions. Since it is done without any Excel support it's pretty tricky work, and going quite slowly. You can follow the progress of the "Excel-DNA IntelliSense" project on GitHub (https://github.com/Excel-DNA/IntelliSense). I hope to get to a first version in the next year or so.

-Govert