Lambda Custom Formulas Randomly Breaking

Question

I'm having an issue where my created formulas using the Lambda function stop working when one of the inputs to the formula change. The only fix I've found is to copy the formula in the Name Manager, delete the formula, re-paste it and then confirm it again, but I'm wanting to stop it from happening in the first place. For example, I have a formula titled FX_Date2Period that takes a date, looks up its coinciding period number, and returns that period number. It works great, and then randomly I'll change the date being inputted and it'll break and show an error. As soon as I use that above fix, I don't have trouble with it again until I close the file and have to open it up again. Has this happened to anyone else, and if so, did you come up with a better/more permanent fix?

Answer 1

Just to add to this. Seems to be an excel bug for sure. I was struggling with the same issue for a week and was close to giving up and rebuilding my files without any lambda functions and then came across your solution. I have built on it further by using the below VBA code and calling it on workbook open to then let it automatically run. This simply searches for any lambda formulas and reapplies them. I hope Microsoft fixes this bug at some point.

Private Sub Workbook_Open()

Call RefreshLambdaFormulas 

End Sub

Sub RefreshLambdaFormulas()

Dim nm As Name 

Dim formulaText As String 

On Error Resume Next 

EvtState = Application.EnableEvents 

ScrnUpd = Application.ScreenUpdating 

CalcState = Application.Calculation 

If Not EvtState = False Then Application.EnableEvents = False 

If Not ScrnUpd = False Then Application.ScreenUpdating = False 

If Not CalcState = xlCalculationManual Then Application.Calculation = xlCalculationManual 

' Loop through all named ranges in the workbook 

For Each nm In ActiveWorkbook.Names 

    ' Check if the name refers to a formula and starts with =LAMBDA( 

    If nm.RefersTo Like "=LAMBDA(\*" Then 

        formulaText = nm.RefersTo ' Store the current formula 

        nm.RefersTo = formulaText ' Reassign it to refresh 

    End If 

Next nm 

Application.EnableEvents = EvtState 

Application.ScreenUpdating = ScrnUpd 

Application.Calculation = CalcState 

On Error GoTo 0 

End Sub

Answer 2

Hi Zach Bene,

Welcome to Microsoft Community.

I realize that you've encountered a problem with copy the formula in the Name Manager, delete the formula, re-paste it and then confirm it again, but you want to stop it from happening in the first place and I understand how you feel.

This problem seems to be related to the calculation cache of the Lambda function in Excel or some formula dependencies, here are some possible solutions.

1.Check the version of Excel, if you are using an older version, it is recommended that you update to the latest version.

2.You can try to check if there is a mix of absolute references (e.g. $A$1) and relative references in the input cells to make sure the references are updated correctly.

3.Using IFERROR or IFNA to wrap Lambda functions may reduce breaks. for example:

=IFERROR(FX_Date2Period(A1), “Error”)

4.Excel may cache the results of the Lambda function, especially when the file is closed and reopened, you can try using the CALCULATE function or other refresh mechanism to ensure that the function is recalculated each time the input changes.

excelCopy Code=IFERROR(FX_Date2Period(A1), “Error”)

Feel free to post back if you need further assistance. 

Best Regards,  

Gina-MSFT | Microsoft Community Support Specialist