Some questions when the old version is consistent with the new version

[Ash Hui]

My previous version of exceldna was relatively old (not "SDK-style project"), and I want to upgrade to the latest version.
So, I created a new "Class Library" template and used "ExcelDna.Addin 1.8.0"
Then I tried to port my original code to this new project.
During the transplantation process, I encountered some problems and would like to ask for confirmation. (The "vb.net" version I use)

1. Has the new version canceled support for range type parameters? If you want to pass in a range object in udf, you need to process it according to ExcelReference and then convert it?
Because in the old version, UDF supports directly defining the parameter type into the range format (it seems that it was not possible at the beginning, and then it was supported after installing the ExcelDna.Registration.VisualBasic library. I remember it very clearly)
But in the new version, I found that it is no longer possible to explicitly define the parameter type of udf into range format. What I'm referring to is this:
[Accepting Range Parameters in UDFs | Excel-DNA](https://excel-dna.net/docs/guides-basic/accepting-range-parameters-in-udfs)

2. Changes in citation methods

Assume that the anchor variable is of range type (converted by the ReferenceToRange function)
dim a= anchor.Offset(1, 0).Columns(1)).value
This code originally (in the old version) was valid (regardless of whether the number of cells in the range was one or more)
But in the new version, this operation will report an error:
System.MissingMemberException

Must be changed to:
Dim anchor_offset As Range = anchor.Offset(1, 0).Columns(1)
dim a= anchor_offset.Value
will be normal

Don't quite understand why this is happening?

[Govert van Drimmelen]

1. Nothing has changed in regards to the support for 'Range' parameters in Excel-DNA between version 1.1 and 1.8.

They are still supported if you use the 'Explicit Registration' approach, where you use the ExceDna.Registration.VisualBasic (and ExcelDna.Registration) helper library / package and then have some code called from your AutoOpen method to do the registration, including a parameter conversion for the Range paramaters.

This has not changed so you should compare your AutoOpen and related code before and after, and also the package references for the Registration helpers.

You should also add a project property like this to your project file, to tell Excel-DNA that you are doing the registration from code and not to attempt to register all <ExcelFunction> methods.

<ExcelAddInExplicitRegistration>true</ExcelAddInExplicitRegistration>

Other project properties are listed here: Excel-DNA properties in SDK-style project files | Excel-DNA

Instead of the ExcelDna.Registration helper library, you can also 'manually' support these functions by doing the ReferenceToRange conversion in your code. This is basically what the Registration helper does for you automatically, behind the scenes. But as far as I recall, nothing in this regard has changed from the Excel-DNA side.

2. The issue here might be how the COM reference to the Excel COM library is set up.

I suggest you use the package 'ExcelDna.Interop' and not the 'Microsoft.Office.Interop.Excel' NuGet package, as mentioned in an earlier discussion.

In the old version you might instead have added a reference to the Excel COM library in other ways through the IDE, and then you might get slightly different behaviour because you'd get the COM library from your installed Excel version.

Without comparing two projects with and without the error you show, it's a bit hard to guess at more details.

But that's where I'd start looking - the exact COM reference you're getting in the different projects.

-Govert

[Ash Hui]

Thank you very much for your patient answer

Regarding question one
Indeed, I discovered the PerformDefaultRegistration() annotation in AutoOpen before, because by default, when I start the plug-in, a function duplication error will appear. I didn't find it out at the time because I didn't transplant the ExternalLibrary settings in the original .dna to the .vbproj settings in the new project.
Now I understand.

Regarding question two
In fact, the reference libraries of my new and old versions are different. I can try it again and see.
I had both 'ExcelDna.Interop' and 'Microsoft.Office.Interop.Excel' installed in the old version

In the new version, I followed your suggestion in another answer and only installed 'ExcelDna.Interop' instead of 'Microsoft.Office.Interop.Excel'.
But I don’t know why. In this case, the "Imports Microsoft.Office.Interop.Excel" in the code can still be used normally. Use "Imports ExcelDna.Interop", but the line of code is displayed in gray (BC40056: Namespace or The type specified in Imports '<qualifiedelementname>' does not contain any public members or cannot be found).
Then I was too concerned about this problem and continued to use it. I don’t know if this problem originated from somewhere? The reason is because 'ExcelDna.Interop' contains "Microsoft.Office.Interop.Excel.dll" and then you can directly use "Import Microsoft.Office.Interop.Excel"?


There are a few more questions:

3. Direct compilation in a new project will report error MSB4062
After I first create a new project, during the process of porting the code, an error will be reported:
MSB4062 Failed to load task 'ExcelDna.AddIn.Tasks' from assembly C:\Users\Ash\.nuget\packages\exceldna.addin\1.8.0\build\..\tools\net452\ExcelDna.AddIn.Tasks.dll .SetLaunchSettings". Verify that the <UsingTask> declaration is correct, that the assembly and all of its dependencies are available, and that the task contains a public class that implements Microsoft.Build.Framework.ITask. ClassLibrary1_vb_test C:\Users\Ash\.nuget\packages\exceldna.addin\1.8.0\build\ExcelDna.AddIn.targets 149
I must add: <ExcelDnaPropsFilePath>false</ExcelDnaPropsFilePath> to the settings to resolve this error.
I checked the configuration documentation and the description is: <!-- Path for configuration properties file location. -->
But I still don't quite understand its function.

4. I have another question and I don’t know why I can’t reply to it.
[Some questions about the upgraded version](https://groups.google.com/g/exceldna/c/SvzgFutU1T4)
In this post, every time I reply later, "Deleted Post" will be displayed. I don't know why.

[Ash Hui]

I just tried it again,
Question 3 seems to have nothing to do with "<ExcelDnaPropsFilePath>false</ExcelDnaPropsFilePath>", because I just encountered that error again inexplicably. I just restarted the following projects and everything was normal again.

Then about using 'ExcelDna.Interop' and 'Microsoft.Office.Interop.Excel',
I found that my new version of the project is different from the old version of the project.
In the old version of the project, these two libraries must be installed at the same time. If any library is missing, many errors will appear in the project.

In the new version of the project, only 'Microsoft.Office.Interop.Excel' is installed, and an error will be reported when the project is started. Just install 'ExcelDna.Interop' and it will run normally, but there will be a problem that anchor.Offset(1, 0).Columns(1)).value cannot be called normally as I mentioned before. And even if both libraries are installed, the problem still exists.

[Govert van Drimmelen]

Inside the package ‘ExcelDna.Interop’ there is a version of the ‘Microsoft.Office.Interop.Excel’ assembly, and a few others.

So when you have the ExcelDna.Interop package installed (and not the other package) you would still add the

‘Import Microsoft.Office.Interop.Excel’ declaration into your code.

 

I’m not sure exactly how this affects your problem line of code.

 

-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/97a9baf5-1f9f-409f-96b7-66dbbc962faan%40googlegroups.com.