Advanced formula environment: suggested improvement to UI

We all come up against the same production problems.  I had a look at AFE/Modules and cannot quite understand where this is going.  What would be required IMHO is a link in Excel to a file containing Lambdas that gets automatically pulled in whenever the spreadsheet is opened.  We do something similar in our environment already with .bas files.  So I sat down last night and cooked up a solution that extends our staging to Lambdas. I am happy to donate the code as a belated Xmas present to the community.  Insert this code into a VBA module (adjust "Root" to your environment):

Option Explicit

Const Root As String = "C:\Users\some_file_path\"
Const charsToTrim As String = " " & vbTab & vbCr & vbLf

Private Function trimLeft(aString As String) As Variant
    Dim i As Long, n As Long
    
    n = Len(aString)
    For i = 1 To n Step 1
        If InStr(charsToTrim, Mid(aString, i, 1)) = 0 Then
            trimLeft = Mid(aString, i, n)
            Exit Function
        End If
    Next

    trimLeft = ""
End Function

Private Function trimRight(aString As String) As Variant
    Dim i As Long, n As Long
    
    n = Len(aString)
    For i = n To 1 Step -1
        If InStr(charsToTrim, Mid(aString, i, 1)) = 0 Then
            trimRight = Mid(aString, 1, i)
            Exit Function
        End If
    Next

    trimRight = ""
End Function

Private Sub addGlobal(aName As String, RefersTo As String)
    On Error Resume Next
    Names(aName).Delete
    Names.Add aName, RefersTo
End Sub

Private Function getLineFromFile(fileNo As Long, ByRef aLine As String) As Boolean
    Do Until EOF(fileNo)
        Line Input #fileNo, aLine
        aLine = trimLeft(aLine)
        If Len(aLine) > 0 Then
            If Left(aLine, 1) <> "'" Then
                getLineFromFile = True
                Exit Function
            End If
        End If
    Loop
    
    getLineFromFile = False
End Function

Public Sub installLambdas(modName As String)
    Dim lbdFile As String, aLine As String
    Dim aName As String, aLambda As String
    Dim iToken As Long

    lbdFile = Root & modName & ".lbd"
    Open lbdFile For Input As #1

    aLambda = "="
    Do While getLineFromFile(1, aLine)
        If aLambda = "=" Then
            iToken = InStr(1, aLine, "=")
            If iToken = 0 Then GoTo Cleanup
            aName = trimRight(Left(aLine, iToken - 1))
            If Left(aName, 1) = "." Then aName = modName & aName
        Else
            iToken = 0
        End If
        
        aLambda = aLambda & trimLeft(trimRight(Mid(aLine, iToken + 1, 9999)))
        
        If Right(aLambda, 1) = "_" Then
            aLambda = Mid(aLambda, 1, Len(aLambda) - 1)
        Else
            addGlobal aName, aLambda
            aLambda = "="
        End If
    Loop

Cleanup:
   Close #1
End Sub

Public Sub Auto_Open()
    installLambdas "myLambdas"
End Sub

To make this stuff work, you must change your XLSX into an XLSM (which means that it won't work in Excel Online.  But you are unlikely to be much concerned about that).

The code above reads a text file named "myLambdas.lbd" (inside the "Root" folder on your corporate OneDrive).  This file contains the lambdas that you want to maintain centrally in a format akin to that proposed by AFE.  An example:

' .perSum simply sums up any numbers in given row iRow of the loan data (LD) that fall within the date bracket [EOFY_1;EOFY].
' 30/12/2022 V1.0, OVR

.perSum = LAMBDA(LD,iRow,EOFY_1,EOFY,LET( _
	Dates, INDEX(LD,1,0), _
	Items, INDEX(LD,iRow,0), _
	SUMIFS(Items,Dates,">"&EOFY_1,Dates,"<="&EOFY) _
))

The VBA code lacks the IntelliSense of AFE so cannot count brackets etc.  To ease my life, I fall back on the VBA idea of suffixing a "_" where I want to break a Lambda over another line.  I also use the VBA syntax for comments ("'").

A couple of things to know:

1  VBA does not understand localization when assigning Range Names.  Therefore, you MUST use "," in your composition of Lambdas (not eg ";" as Europeans would).  (I also suspect that you must write decimals as "2.0", not "2,0" - have not tried.)

2  You can insert empty lines as you please.  Even inside Lambdas (ie between "_").  Ditto lines with comments.  What you CANNOT do is append a trailing comment (on the same line as other code).

3  You can space out with tabs or spaces, as you like.

4  Like in AFE/Modules, every Lambda is introduced with "Name = Lambda(...)".  In my world, this creates a global "Name".  In the world of AFE, you would create "Module.Name".  I very much like the idea of namespaces for Lambdas, so I taught the script a little trick.  Write ".Name = Lambda(...)" in your myLambdas.lbd file and you will get "myLambdas.Name".  So you can have the best of both worlds.

This solution does not nearly have the power of AFE and you must be super careful with your "_"s but assuming that the .lbd files are created by a central development team, they are not likely to fall victim of total numbnutz, so Notepad might be an acceptable editor.

Happy New Year !