Excel-DNA/Registration

Some unsupported signature "error" with Interop types when registering the add-in (Range, Application,...)

rellampec opened this issue · 10 comments

rellampec commented

The following errors did not affect the functionality of exported functions (Excel 2013 / Visual Studio 2017 up to date). So the functions worked out anyway:

image

I guess this comes from usingRange directly in the signature of the exposed functions (which is intended by the use of this extension as per this example). As said, the functions work fine, however, the registration throws that error. I have compared the

  • Is there a way to avoid this error when registering the add-in?

Although the functions work fine, it gives me the impression that I am doing something wrong but cannot tell what.

The file:

Option Explicit On
'Option Strict On

Imports Microsoft.Office.Interop.Excel
Imports ExcelDna.Integration

Public Module Globals
    ' connect the global Application to the Excel instance via ExcelDna 
    Public ReadOnly Property Application As Application
        Get
            Return ExcelDnaUtil.Application
        End Get
    End Property
End Module

Public Module ExtendedRange
    <ExcelFunction(Category:="ExtendedRange", Description:="Checksif any cell in the Range has a validation")>
    Public Function RngHasValidation(rnIn As Range) As Boolean
        Dim rnAux As Range
        On Error Resume Next
        RngHasValidation = Not (RngWithValidation(rnIn) Is Nothing)
    End Function

    <ExcelFunction(Category:="ExtendedRange", Description:="Given a Range it returns the Range thereof that has validations")>
    Public Function RngWithValidation(rnIn As Range) As Range
        Dim rnOut As Range
        If rnIn.Cells.Count = 1 Then
            rnOut = Application.Intersect(rnIn.SpecialCells(XlCellType.xlCellTypeAllValidation), rnIn)
        Else
            rnOut = rnIn.SpecialCells(XlCellType.xlCellTypeAllValidation)
        End If
        On Error GoTo 0
        RngWithValidation = rnOut
    End Function

    <ExcelFunction(Category:="ExtendedRange", Description:="Returns the NamedRange of a cell with validation list")>
    Public Function RngStrValidationNamedRangeInRange(rnIn As Range) As String
        Dim strName As String, nParts() As String, FormulaParts() As String, strFormula As String
        Dim rnWithValidation As Range
        Dim rnArea As Range, rnRow As Range, rnCell As Range
        Dim blnFound As Boolean

        On Error Resume Next
        RngStrValidationNamedRangeInRange = vbNullString
        If Not RngHasValidation(rnIn) Then Exit Function
        strName = vbNullString

        rnWithValidation = RngWithValidation(rnIn)
        blnFound = False
        For Each rnArea In rnIn.Areas
            For Each rnRow In rnArea.Rows
                For Each rnCell In rnRow.Columns
                    If RngHasValidation(rnCell) Then
                        If rnCell.Validation.Type = XlDVType.xlValidateList Then ' type = 3
                            strFormula = rnCell.Validation.Formula1
                            FormulaParts = Split(strFormula, "=")
                            If UBound(FormulaParts) < 1 Then
                                Exit Function ' too bad (the formula is broken)
                            Else
                                blnFound = True
                                strName = FormulaParts(1)
                                nParts = Split(strName, "!")
                                If UBound(nParts) > 0 Then strName = nParts(1)
                                Exit For
                            End If
                        End If
                    End If
                Next
                If blnFound Then Exit For
            Next
            If blnFound Then Exit For
        Next

        If Not blnFound Then Exit Function
        RngStrValidationNamedRangeInRange = strName
    End Function
End Module

Basic Excel-DNA configuration and usage

Before installing the Excel-DNA Registration I went through the basic examples, where you have to declare function parameters where could receive ExcelReference with <ExcelArgument(AllowReference:=True)>.

After some tries, the above worked just fine with Microsoft.Office.Interop.Excel types, such as Range by simply not exposing Range type in the function signature as so:

<ExcelFunction(Category:="ExtendedRange", Description:="Checks if any cell in the Range has a Formula", IsMacroType:=True)>
Public Function RngHasFormulas(<ExcelArgument(AllowReference:=True)> ByVal rngIn As Object) As Boolean

As explained here (by Patrick O'Beirne), in the body, I had to use the following helper to transform the ExcelReference to a Range:

Public Function ReferenceToRange(ByVal xlRef As ExcelReference) As Object
    Dim cntRef As Long, strText As String, strAddress As String
    strAddress = XlCall.Excel(XlCall.xlfReftext, xlRef.InnerReferences(0), True)
    For cntRef = 1 To xlRef.InnerReferences.Count - 1
        strText = XlCall.Excel(XlCall.xlfReftext, xlRef.InnerReferences(cntRef), True)
        strAddress = strAddress & "," & Mid(strText, strText.LastIndexOf("!") + 2)
        ' +2 because IndexOf starts at 0
    Next
    ReferenceToRange = ExcelDnaUtil.Application.Range(strAddress)
End Function

Adding the Excel-DNA Registration extension

With the aim of avoiding such syntax in my VBA to vb.net migration, I decided to add this other extension. Then I followed the guideline how install the package in a Visual Basic project.

This is the dna file:

<DnaLibrary Name="MyAddIn Add-In" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MyAddIn.dll" ExplicitRegistration="true" ExplicitExports="true" LoadFromBytes="true" Pack="true" />
  <Reference Path="ExcelDna.Registration.dll" Pack="true" />
  <Reference Path="ExcelDna.Registration.VisualBasic.dll" Pack="true" />
</DnaLibrary>

Comparing with your Visual Basic example I saw that the package installation added some entries in the packages.config file that are not in the counterpart file of your example. So then I commented those two lines just to see if that would fix the registration issue (no luck in this; yet the exported functions kept working):

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="ExcelDna.AddIn" version="0.34.6" targetFramework="net45" />
  <package id="ExcelDna.Integration" version="0.34.6" targetFramework="net45" />
  <package id="ExcelDna.Interop" version="14.0.1" targetFramework="net45" />
  <!--package id="ExcelDna.Registration" version="0.34.7" targetFramework="net45" />
  <package id="ExcelDna.Registration.VisualBasic" version="0.34.7" targetFramework="net45" /-->
  <package id="Newtonsoft.Json" version="11.0.2" targetFramework="net45" />
</packages>

The error has been already reported at the top of this comment.

rellampec commented

hmm... just found the one that does not register:

    <ExcelFunction(Category:="ExtendedRange", Description:="Given a Range it returns the Range thereof that has validations")>
    Public Function RngWithValidation(rnIn As Range) As Range
        Dim rnOut As Range
        If rnIn.Cells.Count = 1 Then
            rnOut = Application.Intersect(rnIn.SpecialCells(XlCellType.xlCellTypeAllValidation), rnIn)
        Else
            rnOut = rnIn.SpecialCells(XlCellType.xlCellTypeAllValidation)
        End If
        On Error GoTo 0
        RngWithValidation = rnOut
    End Function

Is this because it returns Range as a type? (not supported?)

If it is that, why Application is also marked as Error in the registration? (none of my signatures has Application)

govert commented

Yes - Range return types are not supported - can you return an Object[,] array to return the actual values instead?

I think the Application message probably comes from some other Public Shared method that takes in an Application parameter. Maybe it is mistakenly marked as an <ExcelFunction>.

rellampec commented

Application registration comes from this one:

Public Module Globals
    ' connect the global Application to the Excel instance via ExcelDna 
    Public ReadOnly Property Application As Application
        Get
            Return ExcelDnaUtil.Application
        End Get
    End Property
End Module

which it technically should not, according this line in the dna file:

<ExternalLibrary Path="MyAddIn.dll" ExplicitRegistration="true" ExplicitExports="true" LoadFromBytes="true" Pack="true" />

perhaps a bug of the Excel-DNA Addin extension?

returning Range

Is there any chance that the Registration extension for Excel-DNA could do the same mapping it does for Range as parameter (or ExcelReference) for the returned values as well?

I guess is not as easy working in the other direction (.xll to VBA), but all the types classified as ExcelReference are objects, aren't they? and as net framework is highly typified, there should not be many concerns as how to define the mapping rules at registration time.

Any insights on this? (I could help if there is no much time for this, although would need some training / assistance during dev time, if it was possible, of course)

rellampec commented

hmm... perhaps the way the xll handles memory is different to the way VBA does and the data representation is not the same?

I am unsure as per why you can treat a Range as a Range when you receive it from VBA in your Addin but it does not work the other way around. Is that something native to the Addins of MS Office? or it has more to do with the ByVal of a parameter, that handles a copy of the object when passed as parameter that does not entail data representation issues, while ByRef would not be allowed?

Many doubts, I know. Just want to keep things smooth to other developers when I migrate to .Net.

rellampec commented

Thanks @govert for you early answer.

Let's say I need to return Range in certain functions for internal use (i.e. want to have a library provided by the Addin that I can use with macros in any spreadsheet), but not to use as Excel Function.

Is the Excel-DNA the way to go? Perhaps is just a combination of (COM) Interop and using the Excel DNA as a way to expose certain functions to be used in worksheets ?

I was hoping that xll would allow cross-platform compatibility (Windows and Mac users). We need to generate spreadsheets to collect data. So first thing is to get rid of macros via addin.

Now I see that's only for worksheet functions (or excel functions) and that to cover other tools it will be required to use other means, for instance:

  • Interop: for things to be run in Excel, at the price of losing cross-platform compatibility
  • EEPlus, SpreadsheetLight or similar: for parts that can be run without Excel (then using Mono anyone can use our internal tools).

Just wondering how to build the different components of the Solution in a way that I can get the correct code in the correct spot with no replication of code (optimizing re-usability).

Basically the process would be:

  1. create Excel spreadsheet to gather data => this doesn't require Excel (so this part should be able to be run on Mono, with many options on how to generate the spreadsheet or transform our system pages into worksheets).
  2. once data received:
    • (when required) treat data, using some custom forms with operation options => on Excel using a .net self-made COM lib (so users with Excel can do it)
    • data validation and map final data to codes importable to our platform => here is where the Excel Functions via Excel-DNA Addin can help I guess

All this is a project in VBA that currently works fine. It includes some classes using VBE to create subprojects of the master project (targeting specific code for each tool suite).

The problem is that VBA does not offer a way to efficiently manage your project, does not provide with all the advantages of class inheritance, polymorphism, class extension, method overriding, and a long etcetera that makes you wonder what the MS guys are working on to update Office macros to the XXI Century.

As I have no other option than moving to .net to boost collaborative work, project documentation, multi-component approach, etc. I want to create it as cross-platform as possible to solve those other issues that are not source of fun.

Any suggestions? Did I get it clear or I am missing something?

Thank you Govert!

govert commented

Excel-DNA allows you to define a COM Server that you can use from VBA as part of your add-in, and in this context you will be able to define functions that return Range objects. See the sample and instructions here: https://github.com/Excel-DNA/Samples/tree/master/DnaComServer

This COM Server would be part of the .xll add-in, and has the advantage over other methods like VSTO or a normal COM library project you make with C# that:

  • you don't need admin permissions to load it, and
  • your code runs in the same AppDomain as the worksheet UDF functions, ribbon etc. you define in the Excel-DNA add-in.

Tools like EEPlus and ClosedXML allow you to read and write Excel files without going through the Excel application. Excel-DNA has no role in this process - it is for making add-ins that run inside the Excel process.
The .xll add-ins (and hence anything related to Excel-DNA) are only available on the Windows desktop version of Excel.

Excel-DNA allows you to access both the full C API of Excel as described in the Excel SDK, as well as the full COM object model (I think this is what you are calling 'Interop') from your add-in.

Microsoft's approach to cross-platform Excel add-ins (beyond just Windows and Mac) is the JavaScript API.

My suggestion is that you limit your support to the Windows desktop version of Excel, and then to use Excel-DNA for all the Excel add-in requirements you have.

rellampec commented

Wow Govert, you just clarified so many things in one post. Thank you so much. You saved my time. I really appreciate. It is kind of going blind for too long about how and where aim your efforts to, you know?

I will definitely explore the DnaComServer. I did not because I thought it was kind of only provided by remote host or dedicated server. But if it is included as part of your Addin, eureka!

I will post here anything related to your extension if applicable.

btw, I saw some page in MS docs and one Addin in GitHub in regards to JavaScript API. I wondered which advantages it offered and what its purpose was. Perhaps as a next step, once I manage the basics of the Addin project environment.

Thank you Govert!

rellampec commented

@govert could it be that Excel-DNA Registration is registering functions I have not marked as<ExcelFunction>? Is that normal or it should ignore the ones that do not have that token?

This was the reason why it was trying to register:

Public ReadOnly Property Application As Application
    Get
        Return ExcelDnaUtil.Application
    End Get
End Property

as soon as I made it private, the registration error vanished.

This is my dna's file content:

<DnaLibrary Name="MyAddIn Add-In" RuntimeVersion="v4.0">
  <ExternalLibrary Path="MyAddIn.dll" ExplicitRegistration="true" ExplicitExports="true" ComServer="true" LoadFromBytes="true" Pack="true" />
    <Reference Path="ExcelDna.Registration.dll" Pack="true" />
    <Reference Path="ExcelDna.Registration.VisualBasic.dll" Pack="true" />
</DnaLibrary>

btw, the ComServer suggestion worked out (will explore data transformations I was struggling with in VBA).

govert commented

I've fixed the problem where the VB registration attempts to register public properties as functions in ca14841

  • 👍1
rellampec commented

@govert is there any fast way to update the ExcelDna.Registration? or I should rather re-install, and remake the .dna file?

EDIT

After running

Install-Package ExcelDna.Registration

it got to version 0.34.7, but still getting error when declaring Application as public.

  • Am I doing something wrong?