passing excel spreadsheet as a parameter

[Rob Dejournett]

I have a function in excel that creates a word document from the excel sheet.  I have created a .NET library for it and would like to use Excel DNA.  I have gotten to the point where I can create test functions and they work, so I know the procedure is working correctly.  However when I add the function for created the word library, I get the error:

Initialization [Error] Method not registered - unsupported signature, abstract or generic: 'Test.RunTripDoc'

This is my class in the .NET  library.

Imports ExcelDna.Integration

Imports Microsoft.Office.Interop

Public Class Test

    <ExcelFunction(Description:="Multiplies two numbers", Category:="XLDNA Example Functions")>

    Public Shared Function MultiplyThem(ByVal x As Double, ByVal y As Double) As Double

        Return x * y

    End Function

    <ExcelFunction(Description:="Add two numbers", Category:="XLDNA Example Functions")>

    Public Shared Function AddThem(ByVal x As Double, ByVal y As Double) As Double

        Return x + y

    End Function

    <ExcelFunction(Description:="RunTripDoc", Category:="XLDNA Example Functions")>

    Public Shared Function RunTripDoc(ByRef _excelDoc As Excel.Workbook, ByRef _isVirtualVisit As Boolean) As Double

        Try

            Dim obj As New TripDoc

            obj.Start_Linking(_excelDoc, _isVirtualVisit)

            Return 1

        Catch

            Return 0

        End Try

        'Public Sub Start_Linking(_excelDoc As Excel.Workbook, Optional isVirtualVisit As Boolean = False)

    End Function

End Class

[Govert van Drimmelen]

Hi Rob,

 

That does not look like something you can do easily as a function that would be called from a worksheet formula.

 

You might rather launch the process from a ribbon button or a shortcut key, or even a button control that you put on the workbook.

 

For these case you would make a “Shared Sub” which can then look at the ActiveWorkbook, and pass that on.

You can make full ribbon extensions with Excel-DNA (see https://github.com/Excel-DNA/Tutorials/tree/master/Fundamentals/RibbonBasics and https://www.youtube.com/watch?v=2oBQaQFgQow) but the easiest is just using the <ExcelCommand> attribute like this:

 

<ExcelCommand(MenuName:="My Menu", MenuText:="Start Linking")>

Public Shared Sub StartLinking()

    Dim app As Application

    Dim wb As Workbook

 

   app = ExcelDnaUtil.Application

    wb = app.ActiveWorkbook

 

    Dim obj As New TripDoc

    obj.Start_Linking(wb)

 

End Sub

 

You should find the menu entry under the AddIns tab.

You can also assign the “StartLinking” macro to a button you place on a workbook using Developer -> Insert -> Form Control.

 

-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/8a0a8298-f446-44b0-973f-563529cf46ccn%40googlegroups.com.

[Rob Dejournett]

Hi I just want to notify the community that this worked perfectly.  Thanks!  Why is there a difference in Function vs Command?   In all the languages i've seen, a function is a unit of code that returns a variable, and a subroutine (etc) is one that does not.   Here, function seemingly does not let the excel object be passed, but with a command you can get the same object via the addin as noted above. 

[Govert van Drimmelen]

Hi Rob,

 

It’s not about the programming language, in this case, but rather about Excel distinguishing between “worksheet functions” and “macros”.

There is no problem in VB.NET making a Sub that takes parameters, and such a Sub can even be registered as a “macro” with Excel, though that’s unusual.

Excel-DNA matches this distinction in how it registers your methods with Excel, according to the <ExcelFunction> or <ExcelCommand> attributes.

 

The restrictions from Excel are these:

• A “macro” cannot be called from a worksheet formula, only a “worksheet function” can be.
• A “worksheet function” can only take a limited number of argument types, so you can’t pass a Workbook to a “worksheet function”. There are also restrictions on what you can do inside a “worksheet function” when it is called from a formula, so passing in the workbook name as a string would work, but then you might still have limitations in what you can do with a workbook inside the function.
• Calling a “macro” that takes parameters is easy from VBA (using Application.Run) but is hard from a ribbon button and impossible from a button control on a workbbok.

 

So I redirected you from a Function marked as <ExcelFunction> to a Sub marked as <ExcelCommand> taking no parameters in order to register the procedure as  “macro” and give you an easy way to call it.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/d7a88257-047d-459c-b1f8-e3aa9672dc1en%40googlegroups.com.