Announcing LAMBDA: Turn Excel formulas into custom functions

Andrew Duncan HennSarv 

Since I never use direct cell references, I would be totally screwed on a Mac!  Rather like Microsoft 365 Online.  In the later context existing names seem to work but there is no user interface.  How much support does the Mac offer for VBA?  Would the following work?

Sub CreateName()
Dim LambdaName As String
Dim RefersTo As String
Dim selectedNames
Dim cell As Range
Dim n As Long
Dim ans
Dim OldLambda As String

    LambdaName = ""
    On Error GoTo fail
    LambdaName = Selection.Name.Name
    If Left(LambdaName, 6) <> "Define" Then GoTo fail
    LambdaName = Replace(LambdaName, "Define", "", 1, 7)
        

    RefersTo = Selection.Formula
    n = InStr(1, RefersTo, ")(")
    RefersTo = Left(RefersTo, n)
    RefersTo = Replace(RefersTo, vbLf, "")
    RefersTo = Application.WorksheetFunction.Trim(RefersTo)
    Debug.Print RefersTo
    
    On Error Resume Next
    With ActiveWorkbook.Names(LambdaName)
        OldLambda = .RefersTo
        .Delete
    End With
        
    On Error GoTo fail
    ActiveWorkbook.Names.Add LambdaName, RefersTo
    ans = MsgBox("Name " & LambdaName & " set to refer to: " & RefersTo & vbLf & "ACCEPT?", vbOKCancel)
    If ans = vbCancel Then _
        ActiveWorkbook.Names(LambdaName).RefersTo = OldLambda

Exit Sub

fail: MsgBox "Failed to set " & LambdaName
End Sub

What is does on a pc is read the formula from a selected cell that is named DefineXXXX and from that it creates the Lambda function XXXX.  If you can't name a cell then picking up the name from the cell to the left of the selected cell would be a better option.