×

Add Your Lambda Functions with VBA Macro Code

By Chris Newman •  Updated: 02/20/24 •  5 min read
Import Lambda Functions VBA 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!

Excel Name Manager showing Lambda Functions

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.

Message box of Lambda Functions imported into Microsoft Excel workbook

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.

Keep Learning

Chris Newman

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.