Lambda Expressions in Excel VBA

[Gregor y]

I googled across a short description on the implementation of a "Lambda function" within Excel the other day while searching for a way to pass a function or sub as a parameter to another function/sub, and I thought I'd share it with the group.

So here's the code after I gave it a bit of a face lift and added some example subs:

Please Login or Register  to view this content.

The only short comming I had with it is that Evaluate() will evaluate a worksheet function. So I wasn't able to pass VBA Code, only what would resolve via worksheet functions; however I have not yet fully looked into the VBA Extensibilty Library so there may yet still be a way to improve the process so that it'll run VBA Code instead...

[TMS]

Looks interesting, but I don't really understand what it is meant to achieve.

What is the problem that this is the answer to?

Regards, TMS

[Gregor y]

@TMS, I'm not quite sure how to answer that question. The short answer would be that as far as I know Excel VBA doesn't nativly support Lambda Expressions, so this would be an extension to Excel VBA's functionality.

Re-writing the example subs: ToProperSelection, ToUpperSelection, ToLowerSelection, TrimSelection and ScrubSelection, without the Lambda would mean making a copy of the optimized loop body found in LambdaSelectionByVal into each of these functions individually. So essentually Lambda Expressions add a layer of polymorphism to the code so that you can write and maintain one sub/function (in this case an optimized itterator over selected cells) that will perform any sub/function that you hand to it. In other words writing your own sub to apply FunctionX to the currently selected cells that have data is now just as easy as a one-liner:

Please Login or Register  to view this content.

The seccond Lambda based example sub LambdaRangeByVal is maybe a little more obtuse. In my past experience with excel I've found that when I fill a range of cells with a function =MyVBAFunction(B1,C1) that this works fine untill you start trying to do ranges of somewhere around 100,000 cells or more, then the run time of the MyVBAFunction starts to weigh heavily into the calculation time on the speadsheet and even causes Excel to crash from time to time. So I wrote this Lambda based sub to apply <Insert your favorite MyVBAFunction of one or two parameters here> across a range of three columns {result column, B column, C column} row by row storing only the result along the way.

Even though the two Lambda based subs I had were designed around the itterator concept you needn't stop there, you could just as easily develop a Lambda based recursive sub that updates the Lambda function on the fly (I'm thinking AI and/or FSM here).

So I suppose the longer answer is: like subs, function calls, and class modules it's just another tool in the programmer's tool box.

[TMS]

Thanks for the explanation. Still a little beyond me, at least at the moment.

I guess I'd need to take a few steps back as I am unfamiliar with the term "lambda expression", let alone what you would/can do with one.

One for the back burner, I think.

Regards, TMS

[shg]

I'm puzzling over what the advantage might be. Certainly not speed, no?