Lambda Bugs

Question

I've been using excel labs to write some longer LAMBDAs. I've noticed that lambda functions that work when they are created, arbitrary stop working when I close and reopen the file. Interestingly, some instances of the formulas work while other throw a #VALUE! error. If I open Excel Labs and simply press the floppy save icon, everything works again. But it requires going to each lambda to press the floppy save icon.

Has anyone else experienced this behavior or know how to fix it?

m_tarler · Answer

i have not and kinda weird that would happen.&nbsp; and you are sure you saved the workbook after doing a save on the labs?&nbsp; Can you attach a sample workbook showing this behavior?

ptroi · Answer

Yes, I experience this all the time. I often have to edit the lambda formula (add an arbitrary space somewhere) and then save. That usually works, but sometimes it does not. If it doesn't I shut down all Excel windows, check the Task Manager to ensure Excel is really not running (kill it if it is), and then reopen the file. While more of a pain, that too seems to work. However, some days, neither of these work.

Lastly, I am trying the macro approach shown here [see Mohit Bhatia2's post]:

https://answers.microsoft.com/en-us/msoffice/forum/all/lambda-custom-formulas-randomly-breaking/304df348-e4bc-4d78-98cc-ce8b782cb569

It works for me but today is Day 1 😐

data_junky · Answer

Thank you m_tarler. When I get a moment to strip out confidential info, I can provide a sample.

What I've learned, and this is weird, is that a lambda variable defined as word_word is someone being changed to word_Word (the second W capitalized). When I change the variable back to lower case and press save, it all works again. When I close, sometimes it reverts it back to the old state. Sometimes it doesn't. It isn't easy to replicate the error consistently.

ptroi · Answer

I want to close the loop on this. I've now been running with the macro solution for a little over two weeks. It seems to be the best solution for me. I usually need to run it no more than once a day. Some days I don't need it.To make it easier for people, I've posted the code below. It's the same as Mohit Bhatia2's except that I've added in provisions to carry over any comments that have been added to the lambda name.&nbsp;Sub LambdaRefresh()
    Dim nm As Name, formulaText As String, comment_txt As String
    Dim EvtState As Boolean, ScrnUpd As Boolean, CalcState As XlCalculation

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
            comment_txt = nm.Comment
            
            nm.RefersTo = formulaText ' Reassign it to refresh
            nm.Comment = comment_txt
        End If
    Next nm

Application.EnableEvents = EvtState
    Application.ScreenUpdating = ScrnUpd
    Application.Calculation = CalcState
    On Error GoTo 0
End SubHopefully the lambda bugs can be addressed by MS because needing to use a workaround like this is not a best practice.

m_tarler · Answer

since you also see this behavior, can you confirm if you also have Lambda naming of the form:

name_name

and maybe further check if data_junky observation that the above name_name becomes name_Name and the cause of the problem?

Forum Discussion

Lambda Bugs

6 Replies

Resources