use of a XLL UDF in a XLL UDF in function wizard

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

use of a XLL UDF in a XLL UDF in function wizard
causes Excel to crash 
Henri Pellemans


Guest


Hi at ADX,

I noticed some strange behaviour of XLL UDFs in the function wizard. I can reproduce the issue. It can be explained by exemple.

Suppose we have two XLL UDFs: 


Public Shared Function testDates(ByVal thisDate As DateTime,
                                         ByVal nextDate As DateTime) As Integer
            Return (nextDate - thisDate).Days
        End Function

        Public Shared Function addSomeYears(ByVal thisDate As DateTime,
                                            ByVal years As Integer) As DateTime
            Return thisDate.AddYears(years)
        End F unction


Start typing =testDates( and go to the function wizard.
Enter thisDate e.g. by selecting a cell in the worksheet
Then try to enter the formula of the second UDF [still in the function wizard] into nextDate
When I want to set the value of nextDate to addSomeYears(...
I start typing a d d S ... and when I type the last s, Excel crashes

If I don't use the function wizard, the function =testDates(A1;addSomeYears(A1;2)) works as expected.

Any idea? I have never experienced such a behaviour before.

Software: ADX Pro, Win 7 PRO 64-bit, Excel 2010 32-bit, VS 2010 PRO, all software up to date

[Tested on two different computers]

Henri
Posted 31 Jul, 2012 06:15:19 Top
Eugene Astafiev


Guest


Hi Henri,

What version and build number of Add-in Express do you have installed on the PC? May I get the adxloader.log file from the problematic PC? Typically you can find the log files in the <My Documents>\Add-in Express folder.
Posted 31 Jul, 2012 08:58:02 Top
Henri Pellemans


Guest


Hi Eugene,

Add-in Express 2010 for Office and .NET, Professional
Hotfix 2010.6.7.3062.

This computer has Windows 7 Premium 64-bit [my home computer = W7 PRO 64-bit]

I just got an error message when I posted the log. I will post the log in two parts.

Part 1

Add-in Express Loader Log File: 07/31/2012 17:57:49

Startup directory: C:\Users\Henri\documents\visual studio 2010\Projects\MyXLLAddin1\MyXLLAddin1\bin\Debug\
Loader version: 6.7.3062.0
Operating System: Microsoft Windows 7 Home Premium Edition Service Pack 1 (build 7601), 64-bit
Process Owner: Administrator
Command Line: "C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE"
Run 'As Administrator': No
Process Elevated: No
Integrity Level: Medium
UAC (User Account Control): On
------------------------------------------------------------------------
17:57:49 1328 1992 Start - xlAutoOpen.
17:57:49 1328 1992 Creating a new instance of the XLL loader.
17:57:49 1328 1992 Loading mscoree.dll
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the CLSID of the managed XLL class.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Loading the configuration from the system registry.
17:57:49 1328 1992 Getting the latest CLR version.
17:57:49 1328 1992 The latest CLR version is 'v4.0.30319'.
17:57:49 1328 1992 The configuration has been loaded successfully.
17:57:49 1328 1992 Runtime version: v4.0.30319.
17:57:49 1328 1992 Assembly name: MyXLLAddin1, Version=1.0.0.0, Culture=neutral, PublicKeyToken=f0a1ccd199676c49.
17:57:49 1328 1992 Class name: MyXLLAddin1.XLLModule.
17:57:49 1328 1992 Registry key: CLSID\{38EEE532-ADFF-3C35-8DA8-9F5F3541EC05}.
17:57:49 1328 1992 Attempting to create a new instance of the managed XLL class: CLR - v4.0.30319
17:57:49 1328 1992 Loading CLR: v4.0.30319.
17:57:49 1328 1992 Calling CLRCreateInstance method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Calling GetRuntime method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Checking if the hosting API of .NET Framework v4.0 beta is installed.
17:57:49 1328 1992 The hosting API is up to date.
17:57:49 1328 1992 Calling GetInterface method for the CorRuntimeHost interface.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Starting CLR...
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the CLR version.
17:57:49 1328 1992 The CLR v4.0.30319 has been initialized successfully.
17:57:49 1328 1992 Creating a new domain setup.
17:57:49 1328 1992 Success.
Posted 31 Jul, 2012 11:10:32 Top
Henri Pellemans


Guest


Part 2

17:57:49 1328 1992 Getting the add-in directory.
17:57:49 1328 1992 Success. The directory is 'C:\Users\Henri\documents\visual studio 2010\Projects\MyXLLAddin1\MyXLLAddin1\bin\Debug\'
17:57:49 1328 1992 The 'shadow copy' is enabled.
17:57:49 1328 1992 Creating a new application domain.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the base directory for the domain.
17:57:49 1328 1992 Success. The directory is 'C:\Users\Henri\documents\visual studio 2010\Projects\MyXLLAddin1\MyXLLAddin1\bin\Debug\'.
17:57:49 1328 1992 Openning adxloader.dll.manifest.
17:57:49 1328 1992 Success. The manifest is 'C:\Users\Henri\documents\visual studio 2010\Projects\MyXLLAddin1\MyXLLAddin1\bin\Debug\adxloader.dll.manifest'.
17:57:49 1328 1992 Getting the 'assemblyIdentity' element.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the 'name' attribute.
17:57:49 1328 1992 Success. The name is 'MyXLLAddin1, PublicKeyToken=f0a1ccd199676c49'.
17:57:49 1328 1992 Getting the 'name' attribute.
17:57:49 1328 1992 Success. The class name is 'MyXLLAddin1.XLLModule'.
17:57:49 1328 1992 Attempting to create a new instance of the XLL class.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'autoOpen' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'autoClose' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'autoAdd' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'autoRemove' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'autoFree' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'autoFree12' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'addInManagerInfo' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'addInManagerInfo12' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'Dispose' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Getting the dispid of the 'initialize2' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 Invoking the 'initialize3' method.
17:57:49 1328 1992 Success.
17:57:49 1328 1992 The managed XLL class has been created successfully.
17:57:49 1328 1992 Forwarding xlAutoOpen to the managed code.
17:57:49 1328 1992 Success.
Posted 31 Jul, 2012 11:11:02 Top
Eugene Astafiev


Guest


Hi Henri,

Thank you for providing me with the log file. It looks good; I didn't notice anything strange on my road.

But I've noticed that you use the http://msdn.microsoft.com/en-us/library/system.datetime.aspx structure in your UDFs. Please note that Excel doesn't have such type. So, could you please replace the http://msdn.microsoft.com/en-us/library/system.datetime.aspx structure with the Double (or, at least, use the System.Object class). Does it help?

FYI Double is used in Excel for storing dates.
Posted 01 Aug, 2012 04:11:29 Top
Henri Pellemans


Guest


Hi Eugene, until now I have never had a problem with dates declared as DateTime. I have a lot of mutual dependent UDFs that have DateTime parameters. I run into a problem only with nested functions that are entered in the Function Wizard. Usually I do not do this. The arguments that I pass to my UDFs are almost always a reference to one or more Excel cells. In fact usually I give Excel columns or rows a name [Named Range] exactly the same as the corresponding parameter name of the UDF.

However, I gave it a try and this resulted in the following [in my opinion] very ugly code: 


Public Shared Function testDates(ByVal thisDate As Double,
                                 ByVal nextDate As Double) As Integer
            Dim tempThisDate As DateTime = DateTime.FromOADate(thisDate)
            Dim tempNextDate As DateTime = DateTime.FromOADate(nextDate)
            Return (tempNextDate - tempThisDate).Days
End Function

Public Shared Function addSomeYears(ByVal thisDate As Double,
                                    ByVal years As Integer) As Double
            Dim tempThisDate As DateTime = DateTime.FromOADate(thisDate)
            Return tempThisDate.AddYears(years).ToOADate
End F unction


I have not tested this code in depth, but the crash of Excel has gone!

Best regards,

Henri
Posted 01 Aug, 2012 10:27:41 Top
Eugene Astafiev


Guest


Hi Henri,

I have not tested this code in depth, but the crash of Excel has gone!


Nice shot! ;-)

Thank you for letting us know and good luck with your add-in projects!
Posted 01 Aug, 2012 10:38:47 Top