Function decorations - in Excel

Aug 1, 2012 at 8:23 PM

Hi Govert,

 

I been using Excel DNA and I really like it. One thing - is there anyaway to decorate the functions. like how some excel functions hint you on the input arguments etc?

also, what about operators in the functions? for example I've made some functions using keith's xll and it allows you to assign exact string to the function in excel - e.g. =library1.function1(). I can't obviously have a "." in my C# source to achieve this but is there a way to do it?

Coordinator
Aug 1, 2012 at 8:46 PM
Edited Aug 1, 2012 at 8:49 PM

Hi,

You can add attributes to your C# or VB.NET code to set the function name, category, descriptions etc. Like this:

using ExcelDna.Integration;

public static class MyFunctions
{

    [ExcelFunction(Name="Library.FunkyFunction", Description="A cool function", Category="My Funky Functions")]
    public static string TheFunction(
        [ExcelArgument(Name="First.Param", Description=" is the first parameter")] string param1,
        [ExcelArgument(Name="[2nd.Param]", Description=" is the second parameter")] object param2)
    {
        return "Whatever!";
    }
}

This function will be then be used as =Library.FunkyFunction("asd, 123). The descriptions are only displayed in the function wizard. Excel does not support user-defined functions of any sort appearing in the in-sheet Intellisense.

In general, anything you can do with Keith's XLL library can be done with Excel-DNA. We fully support the same C API that is used there (and much more...).

-Govert

Aug 2, 2012 at 10:16 PM

Perfect, thank you!

Aug 9, 2013 at 1:49 PM
Hi, govert

As I am typing a function on the cell, the function decoration is not shown. However, its shown just fine when you insert function button on the excel.

What am I missing here? is there anyway to have function decoration shown as I am typing a function on the cell.

Thank in advance
Coordinator
Aug 11, 2013 at 5:35 PM
Hi,

Excel does not support the in-cell IntelliSense for any kind of user-defined functions. Only the built-in functions get that help.

I've done some experimental work on how one might add such support from the add-in's side, but it only works on Excel 2013 running on Windows 8. You can have a look here: https://github.com/Excel-DNA/IntelliSense

I'm bit stuck with that, though. Older versions of Excel and Windows don't have the required support for using the techniques from that project, and the Excel 2013 / Windows 8 combination is not that common yet.

-Govert
Aug 12, 2013 at 2:59 PM
Thank you so much for your answer. I really appreciate your help.
Feb 1, 2014 at 1:53 PM
using ExcelDna.Integration;

public static class MyFunctions
{

    [ExcelFunction(Name="Library.FunkyFunction", Description="A cool function", Category="My Funky Functions")]
    public static string TheFunction(
        [ExcelArgument(Name="First.Param", Description=" is the first parameter")] string param1,
        [ExcelArgument(Name="[2nd.Param]", Description=" is the second parameter")] object param2)
    {
        return "Whatever!";
    }
}
What will be the equivalent code for vb?
Coordinator
Feb 1, 2014 at 2:16 PM
Edited Feb 1, 2014 at 2:23 PM
Imports ExcelDna.Integration

Public Class MyFunctions

    <ExcelFunction(Name := "Library.FunkyFunction", _
                   Description := "A cool function", _
                   Category := "My Funky Functions")> _
    Public Shared Function TheFunction( _
            <ExcelArgument(Name := "First.Param", _
                           Description := " is the first parameter")> _
            param1 As String, _
            <ExcelArgument(Name := "[2nd.Param]", _
                           Description := " is the second parameter")> _
            param2 As Object) As String
    
        Return "Whatever!"
        
    End Function
    
End Class
(I'm straining a bit to make the lines short enough to fit in the CodePlex display.)

For VB.NET it's also nice to use a Module instead of a Class - then the functions need not be marked as Sealed. So you'd have:
Public Module MyFunctions

    <ExcelFunction(Name := "Library.FunkyFunction", _
                   Description := "A cool function", _
                   Category := "My Funky Functions")> _
    Public Function TheFunction( _
            <ExcelArgument(Name := "First.Param", _
                           Description := " is the first parameter")> _
            param1 As String, _
            <ExcelArgument(Name := "[2nd.Param]", _
                           Description := " is the second parameter")> _
            param2 As Object) As String
    
        Return "Whatever!"
        
    End Function
    
End Module
Feb 27, 2014 at 11:36 AM
Thank you very much for your help. Really appreciate your generosity.
I didn't really understand what you've mentioned by "then the functions need not be marked as Sealed". Kindly pardon me. I'm new to VB .NET
For VB.NET it's also nice to use a Module instead of a Class - then the functions need not be marked as Sealed.
Coordinator
Feb 27, 2014 at 11:43 AM
Sorry - I meant Shared.

If you make a Class in VB.NET, then methods marked as Shared can be accessed using the class name directly without having to instantiate an instance of the class. This is the equivalent of static in C#. Only such methods are registered by Excel-DNA.

In a VB.NET Module, the Functions and Subs are implicitly Shared, so you don't need the extra qualifier.

-Govert
Mar 3, 2014 at 10:32 AM
Dear govert,
Thank you very much for your help. Once again I really appreciate your generosity.