Caching LAMBDA results

Question

Caching LAMBDA results

Anonymous

In VBA

Function LambdaPerfUdf(inVar As Variant) As Variant 

    Debug.Print Application.Caller.address 

    LambdaPerfUdf = inVar 

End Function

In Name Manager

Name: LambdaPerfTest

Refers to: =LAMBDA(a,LambdaPerfUdf(a))

On Worksheeet

	A	B
1	1	2
2
3	=LambdaPerfTest(A1:B1)
4	=LambdaPerfTest(A1:B1)
5
6	=A3#
7	=A3#

From this experiment, I can see that LambdaPerfUdf is run twice whenever A1 or B1 are updated. Once for A3 and once for A4.

This implies that a LAMBDA function added to the name manager does not cache results for identical parameters. I ran a similar test for a parameterless LAMBDA function and got the same result.

Does this mean that for best performance we should "cache" results in Cells and reference those the cells if the results are needed multiple times? Are there any other ways to cache results? Is there a plan to have caching built-in?

Thank you and Best Regards

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Answer 1

Hi Deven,

Thanks for posting in the community.

I have limited experience on sharing valuable ideas regarding this specific scenario. Sorry for that.

To get more insights, I would recommend you post questions also on the this Excel Tech Community and more expert may have chance to see this. Hopefully, someone can provide their constructive thinking on your query.

Thanks for your understanding.

Best Regards,

Ryan | Microsoft Community Moderator

Share via

Caching LAMBDA results

1 answer