Creating an UDF with access to procedures in the xll file

[Marco Born]

Hello Forum,

I used ExcelDNA to create a XLL file with several procedures and functions. I now also want to add some UDFs which can be called by the users in Excel.

In the DNA file I added the following sample code:

<![CDATA[
  Public Module ExampleFunctions

  Public Function Example(
    <ExcelArgument(Name:="Value1", Description:="Value 1")> value1 As Integer,
    <ExcelArgument(Name:="Value2", Description:="Value 2")> value2 As Integer
  ) As Integer
  Example = value1*value2
  End Function
End Module
]]>

This example works well. But I want to use something like this:

  Public Function Example2(
    <ExcelArgument(Name:="Value1", Description:="Value 1")> value1 As Excel.Range,
    <ExcelArgument(Name:="Value2", Description:="Value 2")> value2 As Excel.Range
  ) As Integer
  Example2 = value1.value*value2.value
  End Function

The users should be able to select a cell or a range in Excel when they access the UDF with the FunctionWizard and my function then should be able to read the ranges as objects to get the data I need (e.g. the value, the formula, the adress and so on of these ranges).

How I can I get access to them from within the UDF?

Thanks a lot in advance,
M. Born

[Govert van Drimmelen]

Hi Marco,

 

You can try something like this (off the top of my head):

 

  Public Function Example2(
    <ExcelArgument(Name:="Value1", Description:="Value 1", AllowReference:=True)> value1 As Object,
    <ExcelArgument(Name:="Value2", Description:="Value 2", AllowReference:=True)> value2 As Object
  ) As Integer

 

Now the argument values will be either a primitive value, or an object of type ExcelReference, which you can use in C API calls, or even convert to a COM Range using code like

 

    Function ReferenceToRange(xlInput As Object) As Range

 

        Dim reference As ExcelReference = xlInput   ' Will throw some Exception if not valid, which will be returned as #VALUE

        Dim app As Application = ExcelDnaUtil.Application

 

        Dim sheetName As String = XlCall.Excel(XlCall.xlSheetNm, reference)

        Dim index As Integer = sheetName.LastIndexOf("]")

        sheetName = sheetName.Substring(index + 1)

        Dim ws As Worksheet = app.Sheets(sheetName)

        Dim target As Range = app.Range(ws.Cells(reference.RowFirst + 1, reference.ColumnFirst + 1),

                                        ws.Cells(reference.RowLast + 1, reference.ColumnLast + 1))

 

        For iInnerRef As Long = 1 To reference.InnerReferences.Count - 1

            Dim innerRef As ExcelReference = reference.InnerReferences(iInnerRef)

            Dim innerTarget As Range = app.Range(ws.Cells(innerRef.RowFirst + 1, innerRef.ColumnFirst + 1),

                                                 ws.Cells(innerRef.RowLast + 1, innerRef.ColumnLast + 1))

            target = app.Union(target, innerTarget)

        Next

        Return target

    End Function

 

 

-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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/0990c043-562a-4c5a-8e86-520104886e32n%40googlegroups.com.

[Marco Born]

Hello Govert,

thanks a lot for the fast reply. Do I put the ReferenceToRange-Function into the DNA-file or can it be in a different class or module?

Have a great day,

M. Born

[Govert van Drimmelen]

Hi Marco,

 

I think either would work, but I don’t really suggest putting the code into the .dna file anymore.

It’s much better if it is a normal pre-compiled project.

For now, the code in the .dan file is still supported, but some time in the future that will no longer be.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/1f100f23-8c36-444f-8eb4-114633a82379n%40googlegroups.com.

[Marco Born]

I changed my Example2 function:

Public Function Example2(
    <ExcelArgument(Name:="Value1", Description:="Value 1")> value1 As Object,
    <ExcelArgument(Name:="Value2", Description:="Value 2")> value2 As Object
  ) As Integer
  Example2 =  ReferenceToRange(value1)* ReferenceToRange(value2)
  End Function

If I put the ReferencetoRange function into a Module I get an error during execution, that the function is not declared. When I put it into the DNA file I get errors during execution that Application, Range and Worksheet are not declared. If the function is stored within a class I get error during execution that ReferenceToRange not is declared.

Where do I have to store it and how to declare it so that it all works together?

Thanks again,

M. Born

[Govert van Drimmelen]

Hi Marco,

 

Are you using Visual Studio?

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/8c625a74-febf-41ca-a4f2-536b477bf626n%40googlegroups.com.

[Marco Born]

Yes, but I don't get any warnings before or during the compilation.

[Govert van Drimmelen]

OK, I think you also need to add the “ExcelDna.Interop” NuGet package to your project, to get the interop assemblies where Range etc. are defined.

 

Then in your function you need to add the AllowReference:=True parts to the attributes, else you’ll never get passed the ExcelReference objects.

 

Finally you need to decide what to do with the Range – if you try to write to the range it will fail, of course.

But are you using the address or something from the range?

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/f62f9b92-14d1-43cf-90ed-5078531b2436n%40googlegroups.com.

[Marco Born]

When I install the Interop package I get a lot of error messages which might be because it also installs a C#-package (which might cause conflicts as I only use VB). I'll try to upgrade my .NET to V7 and hope then I'm able to install the interop without any problems.

[Govert van Drimmelen]

It should not install any C# package.

What error messages do you see?

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/76cc2550-7311-437b-8cb2-15d6aa403aean%40googlegroups.com.

[Marco Born]

I now installed Visual Studio 22, also the .NET 7 Runtime and the .NET 7 SDK. But in the project properties I only can select .NET 4.8. When I try to install the Nuget package of Exceldna.Interop 15.0.1. it says that there is a dependency with Microsoft.CSharp 4.3.0.
At least in VS 2022 I don't get the thousands of error message I got with VS 2019.

In a tutorial from Patrick O'Beirne I found the following example for an UDF:

Function AddThem(x, y)
AddThem = x + y
End Function

There the result of the function is written into the cell where the UDF is stored. I want to create similiar functions but with the enhancements of descriptive parameter names and so on in the function wizard using e.g. <ExcelArgument(Name:="Value1", Description:="Value 1", AllowReference:=True)>

My example now looks like this:

  Public Function Example2(

    <ExcelArgument(Name:="Value1", Description:="Value 1", AllowReference:=True)> value1 As Object,
    <ExcelArgument(Name:="Value2", Description:="Value 2", AllowReference:=True)> value2 As Object

  ) As String
  Example2 = "=" & value1
  End Function

I enter the UDF into a cell and enter two integer values as value1 and value2 in the function wizard. I would expect to get the value1 as result in the cell with the UDF but nothing happens. I get a #Value error.

Do you have any ideas what still is wrong?

[Govert van Drimmelen]

Ok, so if you just want a function that gets inputs and returns the result to the sheet, then you don’t need the AllowReference:=True or any of the COM stuff.

 

For example, this function takes two input values and returns the first value to the cell.

 

Imports ExcelDna.Integration

 

Public Module Functions

 

    Public Function Example2(

      <ExcelArgument(Name:="Value1", Description:="Value 1")> value1 As Object,

      <ExcelArgument(Name:="Value2", Description:="Value 2")> value2 As Object

    ) As Object

        Example2 = value1

    End Function

 

 

End Module

 

Note that I have made some changes:

• No more AllowReference:=True – this is an advanced feature that you probably don’t need at this stage
• I am not returning a string that looks like a formula (with “=” & xxxxx) but returning the result that should go into the cell.
• These two lines are equivalent in VB.NET

        Example2 = value1

        Return value1

      I prefer the second way where you say ‘Return’ because if you change the name of the function, things don’t break.

      The first line is the old VBA style, where the function name is like a magic variable, that gets the return value.

• I also changed the return type to “Object” instead of “String” since we want to return whatever the first input value was, and it might have been a number of something.

 

I hope you can try it like this.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/29804547-ad18-4e79-b87a-56e106c9f18an%40googlegroups.com.

[Marco Born]

Hello Govert,

again thank you very much. My example was very simple. But I not only need the values to be used as input and output of the functions but also more complex data. For instance, if the user not only selects 1 single cell but a range of several cells then I need to get the range as an Excel object so I can read the values of each cell or the range adress (e.g. "A1:C5") and so on. I also want to be able to send back a formula to a cell as the result of a function (e.g. "=Sum(A1:C5)".

That's why I wanted to specify the input and output as Excel objects like Range or Cell instead of "Object" or "Variant". How can I do that?

Have a great day,

M. Born

[Govert van Drimmelen]

Hi Marco,

It's easier with Excel-DNA than VBA to receive an array input.

Without doing anything special, an input parameter of type Object, when called like "=MyFunction(A1:C5)" will receive a 2D array of values, of type Object(,).

You can either set the type as Object, and then add checks to see whether it is passed a single value or an object.

Or you can set the input parameter to be of type Object(,) - in VB.NET it looks like this in the function declaration: "values(,) As Object"

Then you can get the number of rows and columns by calling values.GetLength(0) and values.GetLength(1) respectively - and also remember that .NET arrays are 0-based.

For example, this function concatenates all the inputs from the input range:

Function ConcatThem(values(,) As Object) as String
    Dim i As Integer
    Dim j As Integer

    ConcatThem = ""

    For i = 0 To values.GetLength(0) - 1
        For j = 0 To values.GetLength(1) - 1   
            ConcatThem = ConcatThem & values(i,j).ToString()
        Next i
    Next j
End Function

You also ask about this:

>  I also want to be able to send back a formula to a cell as the result of a function (e.g. "=Sum(A1:C5)".

Excel does not support this. A cell formula can't return a new formula to the cell as a string, it must return the value that the cell will display.

It could return a string that looks like a formula, equivalent to typing in a string starting with an apostrophe like this

    '=Sum(A1:C5)

But then Excel does not calculate the formula, it is just a string, like if you put 

    '=Something that is not a function + 45%! /?

-Govert

[Marco Born]

Hello again,

your example helps me a lot. But if I understand it right the array always contains the values of the cells the user selected. Is there also a way to get the rows and columns of those cells (or other properties of these cells like the background color)?

Another question:
I put your ConcatThem-Function into the DNA file and it works like expected. As I don't have any syntax highlighting and no Intellisense there and you recommended not to add code into the dna file: How can I put functions like this one into a "normal" class or module and access them from the UDF function I need to leave in the dna file?

Thanks again,

M. Born

[Govert van Drimmelen]

Hi Marco,

You don't need to keep any UDF function in the .dna file.

If you have a normal VB.NET class library project with the code in, you don't need to make the .dna file yourself - the ExcelDna.AddIn package from NuGet creates it when building the project.

If you do have a .dna file, you just indicate which assembly has the functions in by adding an ExternalLibrary tag:

    <ExternalLibrary Path="MyLibrary.dll" />

-Govert

[Marco Born]

Hello Govert,

I started my project several years ago with ExcelDNA 0.6 or so. I used SharpDevelop where no Nuget was available. I mainly use the DNA file to enhance the Excel GUI by additional ribbon buttons. The reference to my DLL file is already included there.

If I move my UDF function from the DNA file into another file then it is not recogniced as an UDF in Excel and I can't call it there. Do I need to change my UDF so that it gets visible in Excel?

Thanks again,

M. Born

[Govert van Drimmelen]

Hi Marco,

 

The most common problem is if the function is inside a Class instead of a Module.

Functions inside a class can be instance methods or “Shared” methods – for the Excel UDF it must be “Shared”.

All functions in a Module are automatically “Shared” so that’s normally easier.

 

To be registered with Excel it must either be a Public Function in a Public Module or a Public Shared Function in a Public Class.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/3aa7a27d-25c3-4f4a-9f5e-9c89bde7c5cen%40googlegroups.com.

[Marco Born]

This was the missing information. My Module was not declared as Public. Now it works. Thanks for all your help.