23
Views
4
Comments
Solved
[Advanced Excel] Calculate Error Message: Object reference not set to an instance of an object
advanced-excel
Service icon
Forge component by Carlos Freitas
Application Type
Reactive
Service Studio Version
11.54.23 (Build 62741)

I received an "Object reference not set to an instance of an object" error message from the Worksheet_Calculate and Workbook_Calculate actions.

I received this error from the Worksheet_Calculate action when calculating a specific sheet (see attachment) and received the error from Workbook_Calculate when the beforementioned sheet was in the workbook.

Below are the accompanying error logs. Could you help me find out what could have caused the issue? 


Stack

Object reference not set to an instance of an object.

   at OfficeOpenXml.FormulaParsing.DependencyChainFactory.FollowChain(DependencyChain depChain, ILexer lexer, ExcelWorkbook wb, ExcelWorksheet ws, FormulaCell f, ExcelCalculationOption options)

   at OfficeOpenXml.FormulaParsing.DependencyChainFactory.GetChain(DependencyChain depChain, ILexer lexer, ExcelNamedRange name, ExcelCalculationOption options)

   at OfficeOpenXml.FormulaParsing.DependencyChainFactory.GetWorksheetNames(ExcelWorksheet ws, DependencyChain depChain, ExcelCalculationOption options)

   at OfficeOpenXml.FormulaParsing.DependencyChainFactory.Create(ExcelWorksheet ws, ExcelCalculationOption options)

   at OfficeOpenXml.CalculationExtension.Calculate(ExcelWorksheet worksheet, ExcelCalculationOption options)

   at OutSystems.NssAdvanced_Excel.CssAdvanced_Excel.MssWorksheet_Calculate(Object ssWorksheet)

   at ss[module name].RssExtensionAdvanced_Excel.MssWorksheet_Calculate(HeContext heContext, Object inParamWorksheet)


Stack

Object reference not set to an instance of an object.

   at ss[module name].RssExtensionAdvanced_Excel.MssWorksheet_Calculate(HeContext heContext, Object inParamWorksheet)

   at ss[module name].Actions.ActionCalculate_Workbook(HeContext heContext, Object inParamWorkbook)

   at ss[module name].Actions.ActionCalculate_Machine_Forwards(HeContext heContext, Int64 inParamLineSetupMachineId, String inParamIn_Capacity, RLout_CapacityList inParamProductInput, RLout_WarningsList& outParamout_Warnings, RLout_InformationList& outParamout_Information, RLout_CapacityList& outParamout_ProductOutput, RLout_MachineOutputList& outParamout_MachineOutput, RLout_CapacityList& outParamout_CapacityInput, String& outParamOut_Capacity, RLout_CapacityList& outParamout_ProductInput, RLout_CapacityList& outParamout_MachineInput, String& outParamOut_ProductSpeed, Byte[]& outParamOut_Checkfile)

   at ss[module name].Actions.ActionCalculate_LineSetup(HeContext heContext, Int64 inParamLineSetupId, RLMachineOutputList& outParamLineSetupOutput, RLout_CapacityList& outParamProductInput, Byte[]& outParamOut_Checkfile)

   at ss[module name].RsseSpace[module name].MssCalculate_LineSetup(HeContext heContext, Int64 inParamLineSetupId, RecordList& outParamLineSetupOutput, RecordList& outParamProductInput, Byte[]& outParamOut_Checkfile)

   at ss[module name].Actions.ActionCalculate_LineSetup(HeContext heContext, Int64 inParamLineSetupId, RLMachineOutputList& outParamLineSetupOutput, RLout_CapacityList& outParamProductInput, Byte[]& outParamOut_Checkfile)

   at ss[module name].ScreenServices[module name]_WebBlocks_WB_LineSetup_Detail_Controller.b__1b(HeContext heContext, String screenName, JObject screenModel, JObject inputParameters, JObject clientVariables)

   at OutSystems.RESTService.Controllers.ScreenServicesApiController.endpoint(String input, String endpointName, String apiVersion, EndpointImplementationDelegate implementation)


Stack

Calculate_LineSetup

 System.NullReferenceException: Object reference not set to an instance of an object.

    at Object.s [as getException] (https://XXXXXXXXXXX/scripts/OutSystems.js?RnlDcii3Xz75iIHHERIZtA:2:10241)

    at c.onSuccess (https://XXXXXXXXXXX/scripts/OutSystems.js?RnlDcii3Xz75iIHHERIZtA:3:7232)

    at XMLHttpRequest. (https://XXXXXXXXXXX/scripts/OutSystems.js?RnlDcii3Xz75iIHHERIZtA:3:2648)


Note: the references that are usually

ErrorWorkbook.xlsx
Excel_Calculate_Error.oml
Solution

I have found the issue. The issue was caused by links to external sources. The external sources were not used by the calculations, but because they were available for use to certain sheets, it caused the error. In short: removed the links to external sources and it now works perfectly fine!

TSO.png
 
MVP

Hi Nadine

I see there is quite a large number of formulas in that sheet. Maybe there is some kind of loop between the formulas?

Hanno


Hi Hanno, 

Sorry, I forgot to respond to your message and directly posted it below...

Hi Hanno,
When I remove the inputs from the other sheets (the if statements to be precise), it calculates it the sheets perfectly fine. So I excpect the issue to originate from the dependencies of other sheets.

Kind Regards,
Nadine

Solution

I have found the issue. The issue was caused by links to external sources. The external sources were not used by the calculations, but because they were available for use to certain sheets, it caused the error. In short: removed the links to external sources and it now works perfectly fine!

TSO.png
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.