How to edit advanced Lambdas in your favorite text editor
All the Excel Gurus and Lambda specialists use the Excel Labs add-in, formerly known as Advanced Formula Environment, to create and debug their Lambda functions. While certainly feature-rich and convenient, it doesn’t match the versatility and familiarity of writing code in your favorite text editor. Plus, the Excel Labs add-in is not available to everyone – if you have your Excel locked down by IT, your access to the Microsoft Store may be blocked.
Having found myself in that situation, I came up with a method to use VS Code to write my Lambda functions and have them imported into Excel at the click of a button.
I currently write my Lambdas in text files that reside in the same folder as the target xlsx file. At the click of a button in Excel, a single macro checks the Active workbook path for any text files containing lambdas (I name them *.lda) and puts them in the Name Manager for the workbook. It can also pull Lambdas from the workbook into text files in the path folder and resolve conflicts based on last update datetime, always keeping the most recent version of the function. It skips named ranges (if a named formula does not start with a =LAMBDA, it is skipped), and removes any unescaped spaces, new-line characters and comments starting with //, ‘ (single quote), and comment blocks (/ */).
This enables me to write my LAMBDA functions in VS code with syntax highlighting. I use the Visual Basic highlighter, as it works reasonably well for the Excel formula language. You could create your own syntax highlighter, but that requires building a VS Code Add-in, and I’d much rather like to focus on writing actual Lambdas.
On that note, the entirety of the VBA code for the update macro was generated by ChatGPT in response to a single prompt, with very little corrections. I must say, AI’s get pretty decent at VBA, as long as you provide proper guidance.
This is the prompt I used:
Write a VBA Excel macro that will synchronize lambdas in Named Formulas from the name manager with text files in the folder.
On execution, it should:
- Go over all of the named formulas in the active file, check which ones are lambda functions (wrapped in LAMBDA()), make a list.
- Go into the workbookcustomproperties and read the custom property “LambdaSync”. If it exists, it should contain last sync for each lambda in a format such as function1;lastsyncdatetime;function2;lastsyncdatetime;…
- For each function, check if a last synchronization datetime is greater than the last modified datetime of the textfile corresponding to the given function.
- If the last sync datetime is greater than the last modified datetime of the text file, do nothing, unless the text file does not exist – in such case, create it and put in the function.
- If the last sync datetime is smaller than the last modified datetime of the text file, try to update the function in the workbook with the value from the file and update the last sync datetime. If it fails, msgbox the user.
- When putting functions from the text files in the workbook, make sure you remove all tabs, all spaces and newline characters which are not in “”.
The textfiles should reside in the same folder as the workbook. if the workbook is not yet saved, msgbox the user to save the workbook before syncing. The textfiles should have the extension .lda and their names should correspond to the names of the functions.
This successfully created most of the code. The few changes involved unicode handling (the ReadFile and WriteFile functions had to be rewritten to use ADODB.Stream) and some corrections to the Regex that strips whitespace and comments.
I also had to add a GetWorkbookPath function from StackOverflow to handle paths of workbooks in one-drive enabled folders.
You can get the entire macro here:
Sub SyncLambdaFunctions()
' Initialize objects
Set wb = ActiveWorkbook
Set fso = CreateObject("Scripting.FileSystemObject")
Set lambdaFunctions = CreateObject("Scripting.Dictionary")
Set syncDict = CreateObject("Scripting.Dictionary")
' Ensure workbook is saved
If wb.Path = "" Then
MsgBox "Please save the workbook before synchronizing LAMBDA functions.", vbExclamation
Exit Sub
End If
folderPath = GetWorkbookPath() & Application.PathSeparator
' Read custom property "LambdaSync" if it exists
On Error Resume Next
syncData = wb.CustomDocumentProperties("LambdaSync")
On Error GoTo 0
If syncData <> "" Then
Dim syncPairs As Variant
Dim i As Integer
syncPairs = Split(syncData, ";")
For i = LBound(syncPairs) To UBound(syncPairs) - 1 Step 2
syncDict(syncPairs(i)) = CDate(syncPairs(i + 1))
Next i
End If
' Collect LAMBDA functions from Named Formulas
For Each nm In wb.Names
If InStr(1, nm.RefersTo, "LAMBDA(", vbTextCompare) > 0 Then
lambdaFunctions(nm.Name) = nm.RefersTo
End If
Next nm
' Sync process
failedUpdates = ""
For Each nm In lambdaFunctions.Keys
nameOfFile = Replace(nm, "\", "=")
filePath = folderPath & nameOfFile & ".lda"
lastSync = 0
If syncDict.exists(nm) Then lastSync = syncDict(nm)
needsUpdate = False
' Check if file exists
If fso.FileExists(filePath) Then
Set file = fso.GetFile(filePath)
lastModified = file.DateLastModified
' Compare timestamps
If lastModified > lastSync Then
' Read and clean the file content
txt = ReadFile(filePath)
cleanFormula = CleanFormulaText(txt)
' Try updating Named Formula
On Error Resume Next
wb.Names(nm).RefersTo = cleanFormula
If Err.Number <> 0 Then
If ActiveCell.Value = "" Then
ActiveCell.Value = "'" & cleanFormula
Else
ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Activate
ActiveCell.Value = "'" & cleanFormula
End If
failedUpdates = failedUpdates & nm & ": " & Err.Description & vbNewLine
Err.Clear
Else
needsUpdate = True
End If
On Error GoTo 0
End If
Else
' File does not exist, create it
WriteFile filePath, lambdaFunctions(nm)
needsUpdate = True
End If
' Update last sync timestamp
If needsUpdate Then syncDict(nm) = Now
Next nm
' Check for new .lda files in the folder and add them as Named Formulas if missing
Set folderObj = fso.GetFolder(folderPath)
newFunctions = ""
For Each fileObj In folderObj.Files
fileName = fileObj.Name
If LCase(fso.GetExtensionName(fileName)) = "lda" Then
Dim functionName As String
functionName = Replace(Left(fileName, Len(fileName) - 4), "=", "\") ' Remove .lda extension
If Not lambdaFunctions.exists(functionName) Then
txt = ReadFile(folderPath & fileName)
cleanFormula = CleanFormulaText(txt)
' Try adding new Named Formula
On Error Resume Next
wb.Names.Add Name:=functionName, RefersTo:=cleanFormula
If Err.Number <> 0 Then
failedUpdates = failedUpdates & functionName & ": " & Err.Description & vbNewLine
Err.Clear
Else
newFunctions = newFunctions & functionName & vbNewLine
syncDict(functionName) = Now
End If
On Error GoTo 0
End If
End If
Next fileObj
' Update custom property with new sync times
syncData = ""
For Each nm In syncDict.Keys
syncData = syncData & nm & ";" & syncDict(nm) & ";"
Next nm
' Write back to custom properties
On Error Resume Next
Set customProp = wb.CustomDocumentProperties("LambdaSync")
If customProp Is Nothing Then
wb.CustomDocumentProperties.Add Name:="LambdaSync", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=syncData
Else
customProp.Value = syncData
End If
On Error GoTo 0
' Show final messages
Dim finalMessage As String
finalMessage = "Lambda functions synchronized successfully." & vbNewLine
If failedUpdates <> "" Then
finalMessage = finalMessage & vbNewLine & "The following functions failed to update:" & vbNewLine & failedUpdates
End If
If newFunctions <> "" Then
finalMessage = finalMessage & vbNewLine & "The following new functions were added:" & vbNewLine & newFunctions
End If
MsgBox finalMessage, vbInformation
End Sub
' Reads a Unicode text file and returns its contents as a string
Function ReadFile(filePath) As String
Dim stream As Object
Set stream = CreateObject("ADODB.Stream")
With stream
.Type = 2 ' adTypeText
.Charset = "utf-8" ' Change to "utf-16" if needed
.Open
.LoadFromFile filePath
ReadFile = .ReadText
.Close
End With
Set stream = Nothing
End Function
' Writes a Unicode string to a text file
Sub WriteFile(filePath, text)
Dim stream As Object
Set stream = CreateObject("ADODB.Stream")
With stream
.Type = 2 ' adTypeText
.Charset = "utf-8" ' Change to "utf-16" if needed
.Open
.WriteText text
.SaveToFile filePath, 2 ' adSaveCreateOverWrite
.Close
End With
Set stream = Nothing
End Sub
' Cleans up formula text by removing unnecessary spaces, tabs, and newlines
Function CleanFormulaText(txt) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
' Remove inline comments starting with // and extending to the end of the line
regex.IgnoreCase = True
regex.Global = True
regex.Pattern = "//[^\n\r]*"
txt = regex.Replace(txt, "")
regex.Pattern = "\'[^\n\r]*"
txt = regex.Replace(txt, "")
' Remove block comments between /* and */
regex.Pattern = "/\*.*?\*/"
txt = regex.Replace(txt, "")
' Remove spaces, tabs, and newlines outside of quoted strings
regex.Pattern = "(\s+)(?=(?:[^""]*""[^""]*"")*[^""]*$)"
CleanFormulaText = regex.Replace(txt, "")
End Function
Function GetWorkbookPath(Optional wb As Workbook)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Purpose: Returns a workbook's physical path, even when they are saved in
' synced OneDrive Personal, OneDrive Business or Microsoft Teams folders.
' If no value is provided for wb, it's set to ThisWorkbook object instead.
' Author: Ricardo Gerbaudo
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If wb Is Nothing Then Set wb = ThisWorkbook
GetWorkbookPath = wb.Path
If InStr(1, wb.Path, "https://") <> 0 Then
Const HKEY_CURRENT_USER = &H80000001
Dim objRegistryProvider As Object
Dim strRegistryPath As String
Dim arrSubKeys()
Dim strSubKey As Variant
Dim strUrlNamespace As String
Dim strMountPoint As String
Dim strLocalPath As String
Dim strRemainderPath As String
Dim strLibraryType As String
Set objRegistryProvider = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
strRegistryPath = "SOFTWARE\SyncEngines\Providers\OneDrive"
objRegistryProvider.EnumKey HKEY_CURRENT_USER, strRegistryPath, arrSubKeys
For Each strSubKey In arrSubKeys
objRegistryProvider.GetStringValue HKEY_CURRENT_USER, strRegistryPath & "\" & strSubKey & "\", "UrlNamespace", strUrlNamespace
If InStr(1, wb.Path, strUrlNamespace) <> 0 Or InStr(1, strUrlNamespace, wb.Path) <> 0 Then
objRegistryProvider.GetStringValue HKEY_CURRENT_USER, strRegistryPath & "\" & strSubKey & "\", "MountPoint", strMountPoint
objRegistryProvider.GetStringValue HKEY_CURRENT_USER, strRegistryPath & "\" & strSubKey & "\", "LibraryType", strLibraryType
If InStr(1, wb.Path, strUrlNamespace) <> 0 Then
strRemainderPath = Replace(wb.Path, strUrlNamespace, vbNullString)
Else
GetWorkbookPath = strMountPoint
Exit Function
End If
'If OneDrive Personal, skips the GUID part of the URL to match with physical path
If InStr(1, strUrlNamespace, "https://d.docs.live.net") <> 0 Then
If InStr(2, strRemainderPath, "/") = 0 Then
strRemainderPath = vbNullString
Else
strRemainderPath = Mid(strRemainderPath, InStr(2, strRemainderPath, "/"))
End If
End If
'If OneDrive Business, adds extra slash at the start of string to match the pattern
strRemainderPath = IIf(InStr(1, strUrlNamespace, "my.sharepoint.com") <> 0, "/", vbNullString) & strRemainderPath
strLocalPath = ""
If (InStr(1, strRemainderPath, "/")) <> 0 Then
strLocalPath = Mid(strRemainderPath, InStr(1, strRemainderPath, "/"))
strLocalPath = Replace(strLocalPath, "/", "\")
End If
strLocalPath = strMountPoint & strLocalPath
GetWorkbookPath = strLocalPath
If Dir(GetWorkbookPath & "\" & wb.Name) <> "" Then Exit Function
End If
Next
End If
End Function
