Complete Simple VB.Net sample for Excel 2013

[Gert-Jan Fien]

Hello all!

I'm another keen novice of ExcelDNA, but am struggling to find a ready-made straightforward VisualStudio project for creating a simple add-in compiled from VB.net with fool-proof instructions. I tried Govert's "Hello World" sample for which I found the C# documentation and managed to get it compiled in VB.Net, but the "SayHello" function doesn't show up in Excel, even though the add-in seems to have properly loaded.

Any help MUCH appreciated!!

[Govert van Drimmelen]

Hi Gert-Jan,

 

Thank you for trying Excel-DNA, even though the documentation is fragmented and sparse.

 

For a VB.NET function to be registered with Excel it must either be a Function in a Public Module, or it must be a Public Shared Function in a Public Class.

It’s easy to put the code in a Class but forget to mark the function as “Shared” (the equivalent of “static” in C#).

 

The simplest instructions for a VB.NET add-in would be:

 

·         Create a new “Class Library” project.

·         Install the “ExcelDna.AddIn” package from NuGet.

·         Replace everything your .vb file with this code:

 

 

Imports ExcelDna.Integration

 

Public Module MyFunctions

 

    <ExcelFunction(Description:="My first .NET function")> _

    Public Function HelloDna(name As String) As String

        Return "Hello " & name

    End Function

   

End Module

 

·         Press F5 to compile and load in Excel.

·         Now check for the =HelloDna("Gert-Jan") in a cell

 

 

Let us know whether you are able to get it to work.

 

Regards,

Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Patrick O'Beirne]

You can get more examples here:
my tutorial shows the conversion from VBA to XLL:
 http://www.sysmod.com/vba-to-vb.net-xll-add-in-with-excel-dna.pdf

[Gert-Jan Fien]

Thanks to both Govert and Patrick. I'll give that a try. Good fun! Thanks for all your efforts.

[Gert-Jan Fien]

Hi Govert,

When I use the simple code you suggest, the "line continuation" character (underscore) goes missing and when I replace it, it just disappears again. When I press F7, however, (as opposed to F5), it seems to build the solution without any problem and the function registers properly with Excel.

Any idea why that "debugging" fails? You can tell that I'm new to VB.NET, after 20 years of VB6 and VBA :-)

Groeten!

[Govert van Drimmelen]

Hi Gert-Jan,

I'm not sure what the keyboard bindings on your Visual Studio would be.

You can check the menu:

  Debug -> Start Debugging (???)

If you pick this, debugging should work fine - just set a breakpoint somewhere.

The implicit line continuation for VB.NET was added a few versions ago (see https://msdn.microsoft.com/en-us/magazine/ee336123.aspx?f=255&MSPPError=-2147217396)

You can set under Tools -> Options -> Text Editor -> Basic -> Advanced whether to automatically reformat your code, which I think is why the line continuations are disappearing.

-Govert

[Gert-Jan Fien]

You're right! Undoing that "Advanced Setting" option for reformatting keeps the underscore in place and lets me do the debugging!  Good to know.

Thanks, again.

Gert-Jan Fien

gertja...@gmail.com

+44 (0)1606 889995

+44 (0)7415 909280

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/dXDTEbdH4ks/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.

To post to this group, send email to exce...@googlegroups.com.

Visit this group at https://groups.google.com/group/exceldna.

[Gert-Jan Fien]

Great tutorial, Patrick.

I'm trying to track down Ross McLean's blog, but it looks like the site has been hijacked or something. Just useless ads, it seems.

Would you happen to have a copy of that "pia-excel.zip" that you mention in your tutorial? Don't know how else to get hold of it.

THANKS!

On Wednesday, December 2, 2015 at 3:24:01 PM UTC, Patrick O'Beirne wrote:

[Gert-Jan Fien]

Hello again! Still having fun with this.

I noticed that my VB.NET add-in is listed under "Add-ins" both as an "Excel add-in" and as a "COM add-in". Is that correct? When I disable the COM add-in, the Excel add-in stays active. When I disable the Excel add-in, both are de-activated, but then Excel crashes when I close it down. Does that ring any bells?

[Gert-Jan Fien]

Hmmm, I think it's because I was experimenting with (un)loading the add-in through the Options>Add-ins dialogues. It seems to work much better and more predictably by simply double clicking on the (packed) XLL file. However I still get two add-ins listed: an active COM add-in and an inactive Excel add-in. Can I just live with that?

[Govert van Drimmelen]

Hi Gert-Jan,

If your add-in has a Ribbon, a Custom Task Pane or explicitly loads a COM add-in, then you'll see the extra COM add-in loaded as well. In order to support the ribbon, Excel-DNA dynamically registers and loads a COM add-in behind the scenes (I don't know of another way to support the Ribbon interface).

If you just want to make some user-defined functions available to Excel, your add-in should not need to load the extra COM part.

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Gert-Jan Fien [gertja...@gmail.com]
Sent: 16 December 2015 07:21 PM
To: Excel-DNA
Subject: Re: [ExcelDna] Complete Simple VB.Net sample for Excel 2013

Visit this group at https://groups.google.com/group/exceldna.