UDF's returning #VALUE errors temporarily - Add-in Express .NET forum

Sean Devenish

Posts: 78
Joined: 2015-11-30

Hi,

we have an XLL addin with some simple formulas, the main one being the below:

public static object CurrentCell()
{
try
{
return Module.ExcelApp.ActiveCell.Address;
}
catch
{
return ("A1");
}
}

One of our clients is experiencing a scenario where when first creating a workbook from a document management system, the XLL addin is shown correctly enabled, and the 'CurrentCell' formula is available using Formula AutoComplete, yet the formula produces a #VALUE! error. We have some other simple functions that also return #VALUE! when this happens.

Closing the workbook down and re-opening it will result in the formula returning the correct values. Similarly, while the workbook is still open, disabling and then re-enabling the XLL addin also causes the formula to produce the correct result.

The adxloader log file has no errors.

Do you have any ideas as to why our formulas would be returning this value under certain circumstances?

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Sean,

An XLL isn't supposed to use the Excel object model. At http://support.microsoft.com/kb/301443, they say:

A function that is defined in an XLL can be called under three circumstances:
1. During the recalculation of a workbook
2. As the result of Excel's Function Wizard being called on to help with the XLL function
3. As the result of a VBA macro calling Excel's Application.Run Automation method
Under the first two circumstances, Excel's Object Model does not expect, and is not prepared for, incoming Automation calls. Consequently, unexpected results or crashes may occur.

Try this function instead:

public static string ActiveCellAddress() {
    AddinExpress.MSO.ADXExcelRef activeCell = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.ActiveCell) as ADXExcelRef;
    if (activeCell != null) {
        return activeCell.ConvertToA1Style();
    } else {
        return "no active cell";
    }
}

I've tested it using this formula: =CONCAT(ActiveCellAddress()," ", + NOW()). Using NOW makes the formula volatile and it gets updated when you modify any cell.

Andrei Smolin
Add-in Express Team Leader

Sean Devenish

Posts: 78
Joined: 2015-11-30

Thanks for this and we will definitely incorporate your changes into the code. However all our formulas are returning the same #VALUE! result, including this other simple one which doesn't use the Excel object model?

public static object NamedRange(string rangeName, string emptyMessage = "")
{
try
{
object nRange = Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Evaluate, rangeName);
if (!(nRange is ADXExcelRef))
{
return emptyMessage;
}
var value2 = ((ADXExcelRef)nRange).GetValue();
if (value2 == null || value2.GetType().Name == "ADXXlReturnValue" || value2 is ADXExcelError)
{
return emptyMessage;
}
return value2;
}
catch(Exception e)
{
AppState.RaygunClient.Send(e);
return ADXExcelError.xlErrorNull;
}
}

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Sean,

Check a function returning System.DateTime.Now.ToLongTimeString().

Also, check if your add-in is correctly mentioned in the Addins dialog. Try to register the project once again.

Andrei Smolin
Add-in Express Team Leader

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Sean,

On the function returning System.DateTime.Now.ToLongTimeString(). You can also try to create a new UDF project to check if the function works correctly.

Andrei Smolin
Add-in Express Team Leader

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Oh, I see your other post at https://www.add-in-express.com/forum/read.php?FID=5&TID=14685. Will respond soon.

Andrei Smolin
Add-in Express Team Leader

Sean Devenish

Posts: 78
Joined: 2015-11-30

Thanks Andrew,

By the way this is a separate issue to the other thread, with a different client. On this one, the xLL addin is correctly loaded, and with excel still open, we can disable and reenable the XLL addin and the formulas instantly recalculate and work. We are surprised at the behaviour on initial load of a new workbook and the fact we are getting a #VALUE! error in particular.

It is difficult to install different versions with the client so was hoping there might be some other debug avenues before resorting to testing new projects and/or new functions.

Appreciate your assistance

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Sean,

Please reproduce the issue and check whether adxloader.log is updated or not. If it is, please send it to the support email address or post it here.

Andrei Smolin
Add-in Express Team Leader

Sean Devenish

Posts: 78
Joined: 2015-11-30

Hi Andrei,

adxloader after the error:

Add-in Express Loader Log File: 09/25/2017 14:52:50:479

Startup directory: C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Smart Workpapers\2.2.0.27\
Loader version: 8.6.4408.0
Operating System: Microsoft Windows Server 2012 R2 Standard Edition (build 9600), 64-bit
Process Owner: User
Command Line: "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE" /dde "\\BMOFS02\HowNow\businessfitness\HowNow\DATA\Records\Clients\AAATEST\2016-2017\Accounting and Tax\2017 EOY Individual Workpapers -_r686925.xlsx"
Run 'As Administrator': No
Process Elevated: No
Integrity Level: Medium
UAC (User Account Control): Off
------------------------------------------------------------------------
14:52:50:488 2496 6204 Creating a new instance of the XLL loader.
14:52:50:488 2496 6204 Loading mscoree.dll
14:52:50:489 2496 6204 Success.
14:52:50:489 2496 6204 Getting the CLSID of the managed XLL class.
14:52:50:490 2496 6204 Success.
14:52:50:490 2496 6204 Loading the configuration from the system registry.
14:52:50:490 2496 6204 Getting the latest CLR version.
14:52:50:490 2496 6204 The latest CLR version is 'v4.0.30319'.
14:52:50:490 2496 6204 The configuration has been loaded successfully.
14:52:50:490 2496 6204 Runtime version: v4.0.30319.
14:52:50:490 2496 6204 Assembly name: Workpapers.Next.Addin.
14:52:50:490 2496 6204 Class name: Workpapers.Next.XLLModule.
14:52:50:490 2496 6204 Registry key: CLSID\{AD5BAD6F-7A3B-33D8-A336-637850011F7A}.
14:52:50:490 2496 6204 Attempting to create a new instance of the managed XLL class: CLR - v4.0.30319
14:52:50:490 2496 6204 Loading CLR: v4.0.30319.
14:52:50:490 2496 6204 Calling CLRCreateInstance method.
14:52:50:490 2496 6204 Success.
14:52:50:490 2496 6204 Calling GetRuntime method.
14:52:50:491 2496 6204 Success.
14:52:50:491 2496 6204 Checking if the hosting API of .NET Framework v4.0 beta is installed.
14:52:50:501 2496 6204 The hosting API is up to date.
14:52:50:501 2496 6204 Calling GetInterface method for the CorRuntimeHost interface.
14:52:50:501 2496 6204 Success.
14:52:50:501 2496 6204 Starting CLR...
14:52:50:501 2496 6204 Success.
14:52:50:501 2496 6204 Getting the CLR version.
14:52:50:501 2496 6204 The CLR v4.0.30319 has been initialized successfully.
14:52:50:501 2496 6204 Creating a new domain setup.
14:52:50:503 2496 6204 Success.
14:52:50:503 2496 6204 Getting the add-in directory.
14:52:50:503 2496 6204 Success. The directory is 'C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Smart Workpapers\2.2.0.27\'
14:52:50:504 2496 6204 The configuration file is 'C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Smart Workpapers\2.2.0.27\Workpapers.Next.Addin.dll.config'
14:52:50:504 2496 6204 The 'shadow copy' is disabled.
14:52:50:504 2496 6204 Creating a new application domain.
14:52:50:509 2496 6204 Success.
14:52:50:509 2496 6204 Getting the base directory for the domain.
14:52:50:509 2496 6204 Success. The directory is 'C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Smart Workpapers\2.2.0.27\'.
14:52:50:509 2496 6204 Opening adxloader.dll.manifest.
14:52:50:510 2496 6204 Success. The manifest is 'C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Smart Workpapers\2.2.0.27\adxloader.dll.manifest'.
14:52:50:510 2496 6204 Getting the 'assemblyIdentity' element.
14:52:50:510 2496 6204 Success.
14:52:50:510 2496 6204 Getting the 'name' attribute.
14:52:50:510 2496 6204 Success. The name is 'Workpapers.Next.Addin'.
14:52:50:510 2496 6204 Getting the 'name' attribute.
14:52:50:510 2496 6204 Success. The class name is 'Workpapers.Next.XLLModule'.
14:52:50:510 2496 6204 Attempting to create a new instance of the XLL class.
14:52:50:539 2496 6204 Success.
14:52:50:540 2496 6204 Getting the dispid of the 'autoOpen' method.
14:52:50:541 2496 6204 Success.
14:52:50:541 2496 6204 Getting the dispid of the 'autoClose' method.
14:52:50:541 2496 6204 Success.
14:52:50:541 2496 6204 Getting the dispid of the 'autoAdd' method.
14:52:50:541 2496 6204 Success.
14:52:50:541 2496 6204 Getting the dispid of the 'autoRemove' method.
14:52:50:542 2496 6204 Success.
14:52:50:542 2496 6204 Getting the dispid of the 'autoFree' method.
14:52:50:542 2496 6204 Success.
14:52:50:542 2496 6204 Getting the dispid of the 'autoFree12' method.
14:52:50:542 2496 6204 Success.
14:52:50:542 2496 6204 Getting the dispid of the 'addInManagerInfo' method.
14:52:50:542 2496 6204 Success.
14:52:50:542 2496 6204 Getting the dispid of the 'addInManagerInfo12' method.
14:52:50:542 2496 6204 Success.
14:52:50:542 2496 6204 Getting the dispid of the 'Dispose' method.
14:52:50:542 2496 6204 Success.
14:52:50:542 2496 6204 Getting the dispid of the 'initialize4' method.
14:52:50:542 2496 6204 Success.
14:52:50:542 2496 6204 Invoking the 'initialize4' method.
14:52:50:590 2496 6204 Success.
14:52:50:590 2496 6204 The instance of the managed XLL class has been created successfully.
14:52:50:590 2496 6204 Forwarding xlAutoOpen to the managed code.
14:52:50:611 2496 6204 Success.

Startup directory: C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Office Add-in\2.0.2.2\
Loader version: 8.0.4330.0
Operating System: Microsoft Standard Edition (build 9200), 64-bit
Process Owner: User
Command Line: "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE" /dde "\\BMOFS02\HowNow\businessfitness\HowNow\DATA\Records\Clients\AAATEST\2016-2017\Accounting and Tax\2017 EOY Individual Workpapers -_r686925.xlsx"
Run 'As Administrator': No
Process Elevated: No
Integrity Level: Medium
UAC (User Account Control): Off
------------------------------------------------------------------------
14:52:51:537 4208 6204 Start.
14:52:51:537 4208 6204 Creating a new instance of the add-in loader.
14:52:51:537 4208 6204 Loading mscoree.dll
14:52:51:537 4208 6204 Success.
14:52:51:537 4208 6204 Loading the configuration from the system registry.
14:52:51:538 4208 6204 Getting the latest CLR version.
14:52:51:538 4208 6204 The latest CLR version is 'v4.0.30319'.
14:52:51:538 4208 6204 The configuration has been loaded successfully.
14:52:51:538 4208 6204 Runtime version: v4.0.30319.
14:52:51:538 4208 6204 Assembly name: OfficeAddin.
14:52:51:538 4208 6204 Class name: OfficeAddin.AddinModule.
14:52:51:538 4208 6204 Registry key: CLSID\{8342E722-196E-4D2A-80AF-0640BEBC6D98}.
14:52:51:538 4208 6204 Attempting to create a new instance of the managed add-in class: CLR - v4.0.30319
14:52:51:538 4208 6204 Loading CLR: v4.0.30319.
14:52:51:538 4208 6204 Calling CLRCreateInstance method.
14:52:51:538 4208 6204 Success.
14:52:51:538 4208 6204 Calling GetRuntime method.
14:52:51:538 4208 6204 Success.
14:52:51:538 4208 6204 Checking if the hosting API of .NET Framework v4.0 beta is installed.
14:52:51:538 4208 6204 The hosting API is up to date.
14:52:51:538 4208 6204 Calling GetInterface method for the CorRuntimeHost interface.
14:52:51:538 4208 6204 Success.
14:52:51:538 4208 6204 Starting CLR...
14:52:51:538 4208 6204 Success.
14:52:51:538 4208 6204 Getting the CLR version.
14:52:51:538 4208 6204 The CLR v4.0.30319 has been initialized successfully.
14:52:51:538 4208 6204 Creating a new domain setup.
14:52:51:538 4208 6204 Success.
14:52:51:538 4208 6204 Getting the add-in directory.
14:52:51:538 4208 6204 Success. The directory is 'C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Office Add-in\2.0.2.2\'
14:52:51:539 4208 6204 The configuration file is 'C:\Users\oliverh\AppData\Local\Business Fitness\HowNow Office Add-in\2.0.2.2\OfficeAddin.dll.config'
14:52:51:539 4208 6204 The 'shadow copy' is disabled.
14:52:51:539 4208 6204 Creating a new application domain.
14:52:51:539 4208 6204 Success.
14:52:51:539 4208 6204 Creating an instance of the managed class. Assembly identity: 'OfficeAddin'
14:52:51:581 4208 6204 Success.
14:52:51:581 4208 6204 Unwrapping the managed class.
14:52:51:592 4208 6204 Success.
14:52:51:592 4208 6204 Querying the add-in extensibility.
14:52:51:592 4208 6204 Success.
14:52:51:592 4208 6204 Querying the custom task panes.
14:52:51:592 4208 6204 Success.
14:52:51:592 4208 6204 Querying the ribbon extensibility.
14:52:51:592 4208 6204 Success.
14:52:51:592 4208 6204 Querying the form regions.
14:52:51:592 4208 6204 Success.
14:52:51:592 4208 6204 Querying the encryption provider.
14:52:51:593 4208 6204 The 'EncryptionProvider' interface is not supported.
14:52:51:593 4208 6204 Getting the dispid of the 'Dispose' method.
14:52:51:594 4208 6204 Success.
14:52:51:595 4208 6204 The instance of the managed add-in class has been created successfully.
14:52:51:631 2496 6204 Start.
14:52:51:632 2496 6204 Creating a new instance of the add-in loader.
14:52:51:632 2496 6204 Success.
14:52:51:632 2496 6204 Loading the configuration from the system registry.
14:52:51:632 2496 6204 Getting the latest CLR version.
14:52:51:632 2496 6204 The latest CLR version is 'v4.0.30319'.
14:52:51:632 2496 6204 The configuration has been loaded successfully.
14:52:51:632 2496 6204 Runtime version: v4.0.30319.
14:52:51:632 2496 6204 Assembly name: Workpapers.Next.Addin.
14:52:51:632 2496 6204 Class name: Workpapers.Next.AddinModule.
14:52:51:632 2496 6204 Registry key: CLSID\{80E92C65-0F44-465B-82D2-C4B368238E6B}.
14:52:51:632 2496 6204 Attempting to create a new instance of the managed add-in class: CLR - v4.0.30319
14:52:51:632 2496 6204 Creating an instance of the managed class. Assembly identity: 'Workpapers.Next.Addin'
14:52:51:715 2496 6204 Success.
14:52:51:715 2496 6204 Unwrapping the managed class.
14:52:51:719 2496 6204 Success.
14:52:51:719 2496 6204 Querying the add-in extensibility.
14:52:51:719 2496 6204 Success.
14:52:51:719 2496 6204 Querying the custom task panes.
14:52:51:719 2496 6204 Success.
14:52:51:719 2496 6204 Querying the ribbon extensibility.
14:52:51:719 2496 6204 Success.
14:52:51:719 2496 6204 Querying the form regions.
14:52:51:719 2496 6204 Success.
14:52:51:719 2496 6204 Querying the encryption provider.
14:52:51:719 2496 6204 The 'EncryptionProvider' interface is not supported.
14:52:51:719 2496 6204 Getting the dispid of the 'Dispose' method.
14:52:51:721 2496 6204 Success.
14:52:51:721 2496 6204 The instance of the managed add-in class has been created successfully.
14:52:54:016 2496 6204 Forwarding xlAddInManagerInfo12 to the managed code.
14:52:54:017 2496 6204 Success.

Andrei Smolin

Add-in Express team

Posts: 18827
Joined: 2006-05-11

Hello Sean,

Thank you. The log files are okay.

Could you please provide more details about "first creating a workbook from a document management system"? How do you do this? Is Excel started when you create a workbook?

Have you tried to debug these functions in this scenario?

Andrei Smolin
Add-in Express Team Leader