Any idea why xlfFormulaConvert / Application.ConvertFormula would fail in Excel?

Terry Aney 0 Reputation points
2024-05-28T19:29:28.36+00:00

If I try to call xlfFormulaConvert (C-API) with same result in COM Application.ConvertFormula on the following formula, it throws "#VALUE/Error 2015" exception, but if I do convert on each part of the formula, they work.  Is it because I need to encode the = (comparison) sign? At first I was thinking multiple sheets in the formula, but since the 'left hand' of the = comparison works and it has RBLResult and RBLInput, I don't think that is it.

Fails

?Application.ConvertFormula("=IF(RBLResult!$CL$22,OFFSET(RBLInput!EK$117,1-1,0),OFFSET(RBLInput!EA$117,1-1,0))=RBLInput!$E$40",xlA1,xlR1C1)

Error 2015

Works

?Application.ConvertFormula("=RBLInput!$E$40",xlA1,xlR1C1)

=[Conduent_Nexgen_DBEstimate_SE.xlsm]RBLInput!R40C5

Works

?Application.ConvertFormula("=IF(RBLResult!$CL$22,OFFSET(RBLInput!EK$117,1-1,0),OFFSET(RBLInput!EA$117,1-1,0))",xlA1,xlR1C1)

=IF([Conduent_Nexgen_DBEstimate_SE.xlsm]RBLResult!R22C90,OFFSET([Conduent_Nexgen_DBEstimate_SE.xlsm]RBLInput!R117C[135],1-1,0),OFFSET([Conduent_Nexgen_DBEstimate_SE.xlsm]RBLInput!R117C[125],1-1,0))

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,887 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,895 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.