Caller Range in XLL Add In

Add-in Express™ Support Service
That's what is more important than anything else

Caller Range in XLL Add In
How to determine the Caller Range in a UDF in the XLL Containe 
Michael Kaden




Posts: 140
Joined: 2017-11-15
Thank you Andrei

this works well

Of course I can split [test2.xlsm]Sheet1!B2

Into test2.xlsm and Sheet1 and $B$2 as this is what I need. Unless there are Functions available to give me that. especially the cell Range with $

Sorry for asking all this, but in Vb.net & VSTO I know all the namespaces and functions letting me do this easily so ADX is a bit like learning a new language.

Of course, I could work through AddinExpress.MSO.2005.dll with the object browser, but it looks quite big, so please forgive me asking for the functions to be used.

kind regards
Michael

Michael
Posted 21 Nov, 2017 09:07:57 Top
Andrei Smolin


Add-in Express team


Posts: 15207
Joined: 2006-05-11
Hello Michael,

No need for excuses! Really!

I understand you of course. Please note that the problems above relate mostly to using XLL, not Add-in Express. XLL is a C based API so using it in VB is really difficult. To add, the Excel object model was created after XLL and this is why XLL doesn't contain many, many useful things that the object model provides.

FYI, I've found the GetDocument function above in Macrofun.hlp; see https://www.microsoft.com/en-us/download/details.aspx?id=1465. To get it functioning, see https://support.microsoft.com/en-us/help/917607/error-opening-help-in-windows-based-programs-feature-not-included-or-h.

Also, Steeve Dalton's "Financial Applications Using Excel Add-in Development in C-C++" is highly useful.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 21 Nov, 2017 09:29:55 Top
Michael Kaden




Posts: 140
Joined: 2017-11-15
I got it all working, however on opening Excel I get the following exception:

Detailed technical information follows:
---
Date and Time: 22.11.2017 12:44:56
Machine Name: VAIO
IP Address: fe80::48f1:896:3a76:57c3%14
Current User: VAIO\Alera 02

Application Domain: C:\aleraSoft\aleraXLL02\aleraXLL002\aleraXLL002\bin\Debug\
Assembly Codebase: file:///C:/Windows/assembly/GAC_MSIL/AddinExpress.MSO.2005/8.7.4430.0__4416dd98f0861965/AddinExpress.MSO.2005.dll
Assembly Full Name: AddinExpress.MSO.2005, Version=8.7.4430.0, Culture=neutral, PublicKeyToken=4416dd98f0861965
Assembly Version: 8.7.4430.0

Exception Source: AddinExpress.MSO.2005
Exception Type: System.ApplicationException
Exception Message: Unknown Data Type: aleraXLL002.XLLModule
Exception Target Site: SetTypeInfo

---- Stack Trace ----
AddinExpress.MSO.ADXExcelParameterInfo.SetTypeInfo(type As Type, isReturnType As Boolean)
AddinExpress.MSO.2005.dll: N 2115 (0x843) IL
AddinExpress.MSO.ADXExcelMethodInfo..ctor(targetMethod As MethodInfo, functions As List`1, modBuilder As ModuleBuilder)
AddinExpress.MSO.2005.dll: N 0540 (0x21C) IL
AddinExpress.MSO.ADXExcelMethodInfo.ConvertToXlMethodInfos(methodInfos As List`1, functions As List`1)
AddinExpress.MSO.2005.dll: N 0000 (0x0) IL



When I click ok the window closes and Excel works as intended without problems.

Any pointers to where the exception comes from?

Regards

Michael
Posted 22 Nov, 2017 07:07:19 Top
Andrei Smolin


Add-in Express team


Posts: 15207
Joined: 2006-05-11
Hello Michael,

Excel accepts only certain argument types. In a newly-created project, find the AllSupportedExcelTypes method in the XLL module; the method is created as commented out.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 22 Nov, 2017 08:14:52 Top
Michael Kaden




Posts: 140
Joined: 2017-11-15
Hello Andrei

This will give me the type of any arg I put in cell formula '= AllSupportedExcelTypes(arg)

I checked all arguments of the functions I created, They are either String or Double

In the ADX functions used there are some integer and Boolean arg's as well. I did not touch any of these.

Also the calculations work perfect after I ignore the exception warning, so all the function arg's must be correct?

When I took the sample project code
http://temp.add-in-express.com/support/MyXLLAddin23-GetCallerAddressVb.zip.
into my ADX XXL I got a message that the loaders were different, but I got this message only once.

Any other pointer ?

Regards

Michael
Posted 22 Nov, 2017 10:08:34 Top
Andrei Smolin


Add-in Express team


Posts: 15207
Joined: 2006-05-11
Michael Kaden writes:
AddinExpress.MSO.ADXExcelParameterInfo.SetTypeInfo(type As Type, isReturnType As Boolean)


Add-in Express scans methods defined in your XLL container and fails to register one of the methods with the XLL API. Comment out all the methods and uncomment them one by one to find the method producing the issue. I suppose the issue is cause by the parameters you pass to that method.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 22 Nov, 2017 10:17:50 Top
Michael Kaden




Posts: 140
Joined: 2017-11-15
Thank you, this worked

When I leave

<ComRegisterFunctionAttribute()>
Public Shared Sub RegisterXLL(ByVal t As Type)
AddinExpress.MSO.ADXXLLModule.RegisterXLLInternal(t)
End Sub

<ComUnregisterFunctionAttribute()>
Public Shared Sub UnregisterXLL(ByVal t As Type)
AddinExpress.MSO.ADXXLLModule.UnregisterXLLInternal(t)
End Sub

in the program I get a warning on build

1>C:\Program Files (x86)\MSBuild\14.0\bin\Microsoft.Common.CurrentVersion.targets(4604,5): warning MSB3061: Unable to delete file "C:\aleraSoft\aleraXLL02\aleraXLL002\aleraXLL002\bin\Debug\aleraXLL002.dll". Access to the path 'C:\aleraSoft\aleraXLL02\aleraXLL002\aleraXLL002\bin\Debug\aleraXLL002.dll' is denied.
1> aleraXLL002 -> C:\aleraSoft\aleraXLL02\aleraXLL002\aleraXLL002\bin\Debug\aleraXLL002.dll

When I comment these out, the warning does not exist.

The program however works a intended with both alternatives.

does it matter if I leave above Sub's out?

regards

Michael
Posted 22 Nov, 2017 13:46:02 Top
Andrei Smolin


Add-in Express team


Posts: 15207
Joined: 2006-05-11
Hello Michael,

Leave the subs non-commented. You need to close Excel in order to build the project.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 23 Nov, 2017 00:52:58 Top
Michael Kaden




Posts: 140
Joined: 2017-11-15
Sorry Andrei, perhaps I was not clear enough.

Of course EXCEL is closed when I build/register/unregister otherwise it would not do it.

I have case no 1 where Register XLL/Unregister XLL in uncommented i.e.active

I have case no 2 where Register XLL/Unregister XLL in commented out i.e. not active

Both cases react in exactly the same way:

Build will update aleraXXLOO2. dll & .pdb & .tlp
Register will update aleraXXLOO2. .dll & .pdb & .tlp
Unregister update aleraXXLOO2. .dll & .pdb and erase .tlp

Everything works as intended and without ERROR in both cases.

Only with case 1 (uncommented i.e. Register XXL/Unregister XXL is active) I get the Warning that it cannot erase/write to aleraXXL002.dll. I only get this on Register/Unregister command not on build command. However the aleraXLL.002 is actually updated, even with the warning.

Now as the Warning does not have any effect, I could just ignore it, but we have the policy that Warnings are never ignored unless we understand fully and can decide to leave them there. I mad the experience that ignored warnings can come back as errors later on.

Therefore I would welcome pointer to resolve.

BTW the previous issue (wrong CType) was resolved not because a function in the XLL container had the wrong argument type, but that by accident we have copy and paste a function twice by accident. Your advise to comment everything out and then uncomment one by one revealed this issue. I was somehow not to careful on copy and paste as in VB.net or VSTO a duplicate function will be highlighted.

So I already made sure that Register XLL/Unregister XLL is not duplicated in the XLL module / XLL container

regards

Michael
Posted 23 Nov, 2017 09:24:43 Top
Michael Kaden




Posts: 140
Joined: 2017-11-15
Update

I just found out that the warning comes on all ADX projects even the samples which I run without alteration.

Could it have to do with file and folder security settings?

All my Files and folders are set:

Authenticated users = Full Control
System = Full Control
Administrators = Full Control
Users = Read & Execute / List folder contents / Read allowed
...... Modify / Write denied

I always run Visual Studio as Administrator.

Regards

Michael
Posted 23 Nov, 2017 10:34:56 Top