Add Your Lambda Functions with VBA Macro Code

Importing/Sharing Your LAMBDA Functions

LAMBDA functions are amazing to standardize the way you perform complicated calculations or act as shortcodes for calculations you perform frequently whereas Excel has yet to have a dedicated function created.

If you want to share your LAMBDA functions, there isn't an official way to do it. There have been a few proposed methods including:

• Savings your LAMBDAs to an Excel Template file
• Storing them in an Excel Add-in that adds them to a Workbook on an Open Event
• Using the Advanced Formula Editor add-in and importing from a Git or a Text file
• Using a Personal VBA Macro to write them to a Workbook on command

In this tutorial, we are going to work through the Personal VBA Macro method as I think it's the most straightforward and easiest to maintain out of the currently available options. The VBA code I'll provide will automatically create (import) your Lambda functions into the ActiveWorkbook's Name Manager.

Let's dive into it!

Generating A Personal Macro Workbook

A Personal Macro Workbook is essentially a hidden Excel file that automatically opens in the background when you start up Excel. While the spreadsheet can't be viewed, the file can be accessed via the Visual Basic Editor and you can store your macro code. I have a great little tutorial that you can read through that shows you how to set up a Personal Macro Workbook in just a few clicks of the mouse.

Once your Personal Macro Workbook is created, navigate to a module within it and you can begin setting up a macro to import our favorite LAMBDA functions all at once.

VBA Code To Import Your LAMBDA Functions

Now we will get into the fun coding part. I'm going to assume you already know how LAMBDAs work since you want to load them into your Workbooks. Essentially, we will be writing some code to generate Named Ranges that will be stored in the ActiveWorkbook.

You will want to provide the following 3 inputs for each of your LAMBDAs that you want to import:

1. Your LAMBDA function's name (LambdaName)
2. Your LAMBDA function's formula (LambdaFormula)
3. A description of what your LAMBDA function does or the inputs that are required (LambdaComments)

Here is an example of what you can write for your VBA macro code:

Sub LoadLambdas()
'PURPOSE: Load your favorite LAMBDA functions into the ActiveWorkbook
'SOURCE: www.thespreadsheetguru.com

Dim LambdaName As String
Dim LambdaFormula As String
Dim LambdaComments As String
Dim LambdaList As String

'MilesToKM() Function
  'Lambda Info
    LambdaName = "MILESTOKM"
    LambdaFormula = "=LAMBDA(miles, miles * 1.60934)"
    LambdaComments = "Converts miles to Kilometers"
    LambdaList = LambdaList & LambdaName & "( )" & vbNewLine
      
  'Create Named Range + Formula
    ActiveWorkbook.Names.Add Name:=LambdaName, RefersToR1C1:=LambdaFormula
    
  'Add Comments for the LAMBDA function
    ActiveWorkbook.Names(LambdaName).Comment = LambdaComments
    
'******************************************************************************
 
 'HYPOTENUSE() Function
  'Lambda Info
    LambdaName = "HYPOTENUSE"
    LambdaFormula = "=LAMBDA(a, b, SQRT(a^2 + b^2))"
    LambdaComments = "Calculate the length of the hypotenuse of a " & _
      "right-angled triangle given the lengths of the other two sides"
    LambdaList = LambdaList & LambdaName & "( )" & vbNewLine
    
  'Create Named Range + Formula
    ActiveWorkbook.Names.Add Name:=LambdaName, RefersToR1C1:=LambdaFormula
    
  'Add Comments for the LAMBDA function
    ActiveWorkbook.Names(LambdaName).Comment = LambdaComments

'******************************************************************************

'CtoF() Function
  'Lambda Info
    LambdaName = "CtoF"
    LambdaFormula = "=LAMBDA(celsius, celsius * 9/5 + 32)"
    LambdaComments = "Converts Celsius to Fahrenheit"
    LambdaList = LambdaList & LambdaName & "( )" & vbNewLine
    
  'Create Named Range + Formula
    ActiveWorkbook.Names.Add Name:=LambdaName, RefersToR1C1:=LambdaFormula
    
  'Add Comments for the LAMBDA function
    ActiveWorkbook.Names(LambdaName).Comment = LambdaComments

'******************************************************************************

'Completion Message
  MsgBox "Your LAMBDA functions have been loaded into this workbook: " _
  & vbNewLine & vbNewLine & LambdaList

End Sub

As a small enhancement, I added the LamdaList variable which collects all your Lambda function names and after the code has finished, provides the user with a list of all the Lambda functions that were added to the ActiveWorkbook.

That should be all you need to quickly and easily load up your favorite LAMBDA functions to any workbook. You can assign a keyboard shortcut to your macro or create a button in your QAT to call this code even faster.

I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can write VBA code that allows you to store your custom LAMBDA functions in any Excel workbook you want. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

How The Heck Do You Use Excel's Lambda Function? [Guide]

Are you ready to dive into the magical world of Excel's Lambda function? Whether you're a seasoned Excel wizard or...

Calculate Quarter & FY Totals With Single Excel Function

Excel Function To Provide Quarter and Full Year Totals I've been on a custom function blitz ever since I learned...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.