TABLE OF CONTENTS
Understanding How Scheme Fits Into Excel
A Primer on Scheme
For the first version of AcceλerateTM for Microsoft 365 we chose Scheme as the language of Lambda development.
With Acceλerate for Microsoft 365, you will be able to take the formulas from multiple Scheme resource books and, with very little massaging, use them directly in your Excel spreadsheets. We have listed a few good books in the Helpful References and Resources section of the Resources for Acceλerate for Microsoft 365.
Moreover, the Scheme that we provide on top of .NET and refer to as “VSA®” (aka Visual Scheme®, Visual Scheme for ApplicationsTM) will serve you well as you endeavor to leverage the power of both technology stacks to bring your Office artifacts into the world of linked-data microservices and real data interoperability. Again, in addition to built-in Excel functions and features, other professional Add-Ins you might use, and the huge VBA solutions space, you now also have at your fingertips two brand new “worlds” of existing code to explore for functions you can grab and use: the stable and proven world of Scheme proper and the robust world of .NET, with its vast ecosystem of commercial and open source frameworks and libraries.
In contrast to commodity scripting languages like Python, Ruby and JavaScript, Scheme has an extremely simple and unambiguous, yet profoundly expressive, syntax based on what are called “S-expressions.” The gist of it can be taught in about an hour. Scheme sports a syntax extension capability, which means you can mold it into precisely the language you need it to be - the language of your domain. Indeed, the very language of your business.
S-Expressions: A First Glance
Most languages that use “infix” notation instead of “prefix” notation of S-expressions have to define elaborate, precise precedence rules for mathematical operators, like *, /, + and -.
5 * 3 / 5 + 1 - 5 => ???
Answer: It depends: are we evaluating left to right, right to left, or are we assuming some order of precedence? For example, are * and / evaluated before + and -, as is common in C-like languages, or is there some other order of precedence that we simply must know in advance?
Vastly different answers can result from this ambiguity. But there is no ambiguity of this kind with S-expressions.
Observe:
(- (+ (/ (* 5 3) 5) 1) 5) => -1
\ \ \ \ / / / /
\ \ \ \ / / / /
\ \ \ \/ / / /
\ \ \ 15 / / /
\ \ \ / / /
\ \ \/ / /
\ \ 3 / /
\ \ / /
\ \/ /
\ 4 /
\ /
\/
-1
There simply is no other answer that could possibly be computed from that expression. We compute the answer by evaluating the whole expression from the value of the innermost expression to the value of the outermost expression. Another way to look at this is by following these steps:
(* 5 3) => “multiply 5 and 3” => 15
(/ 15 5) => “divide 15 by 5” => 3
(+ 3 1) => “add 3 and 1” => 4
(- 4 5) => “subtract 5 from 4” => -1
There are other ways we could have written that expression, but this is the order and therefore the result we wanted; the others would have yielded different, and equally unambiguous, answers. We did not have to consult a language spec to make up our minds. We simply had to understand our algorithm, and implement it. The language stays out of our way, and does what we ask it to do.
In Scheme (and any other Lisp) making “precedence order” part of the language spec is simply unnecessary: the parentheses remove all doubt about the order of computation, and you can order your expressions any way you want or need, based on the problem you’re solving. The meaning will be clear to any other coder reviewing your code today, tomorrow, or twenty years or more from now. While you may want to document why you chose the implementation you did, what you did is 100% self-documenting in Scheme with no ambiguity even theoretically possible.
S-Expressions: A Deeper Look
S-expressions are basically lists or some kind of sequential data type enclosed by some delimiter, usually parentheses.
Note: Both Scheme and Clojure, another popular Lisp language that we will be incorporating into future AcceλerateTM products, sometimes use brackets of diverse kinds to refer to other specific data constructs besides the traditional Lisp “list” data structure.
As you saw above, S-expressions can contain other S-expressions as elements, and they can be deeply nested. These expressions use a “prefix” notation that means the operation or function is identified before its operands. This is in contrast to languages with “infix” notation, in which operations can be specified “inside” an expression. You saw the contrast of these notations in the preceding example.
So, with all that in mind, now consider:
(this is an s-expression)
The first item in the list is either a name referring to a function (aka a Lambda), in which cast it is referred to as a “form” and the rest of the items in the list are passed to it for evaluation, or it’s not, in which case the whole list is considered “just data.” In our above example involving mathematical operators, each of the operators were just symbols representing lambdas defined in the standard library, and so Scheme knew just what to do with them.
But the above S-expression would not be recognized as a valid S-expression by Scheme (or any other Lisp) because neither “this” nor any of the other symbols in the list are defined or otherwise built-in forms; and yet the expression as it stands expects to be evaluated somehow.
Key point: S-expressions always expect to be evaluated to a value. This evaluation is the main job of the interpreter and runtime. By default this evaluation is eager, meaning that unless told otherwise, Scheme will try to turn it into a value now, not later. Evaluation that can happen later, when “needed,” is known as “lazy evaluation.” An example of such a construct is what are called “promises” - computations that we start up, and then check on later, to see if they’re done, which allows us to do other computational work in the meantime. We won’t be dealing with lazy evaluation or promises much in an Excel execution context, so we won’t cover that topic here.
In order to tell Scheme that this is just a list, you have two options: either make the first element the literal word “list,” or use “shorthand syntax” to identify it as a list:
> (list this is an s-expression) Unhandled exception during evaluation: &undefined &message: "attempted to use undefined symbol" &irritants: (this)
> '(this is an s-expression) (this is an s-expression)
Of the two of those, only the second expression will be tolerated by Scheme out of the box. The first expression is ordinarily the formal way to introduce a list, however none of the identifiers or symbols in the list are known to Scheme, so it will fail to evaluate it, starting with the first unknown symbol it encounters - in this case, “this.”
The second expression is shorthand for:
> (quote (this is an s-expression)) (this is an s-expression)
The (quote…) form, as you can see, has syntax sugar you can type instead, namely, the apostrophe, and all it means is “quote, but do not evaluate, the following s-expression as-is.” So that’s what it does. It worked (or rather, seemed to work) precisely because it did not try to evaluate the expression.
What is (quote …) for? Well, it’s used mainly in the context of metaprogramming, where you want to manipulate a passed-in S-expression before evaluating it to a value, usually a new S-expression which will then be evaluated. It’s heavily used in writing macros, or what Scheme prefers to call “syntax extensions,” a very advanced topic we will cover in depth elsewhere. Scheme, and all dialects of Lisp, support the ability to write programs that generate their own code at a fundamental level. With Acceλerate for Microsoft 365, Scheme is now officially the first language with first-class metaprogramming capability ever to be usable for Excel functions.
Naming Things in Scheme
With AcceλerateTM for Microsoft 365, you have the ability to give names to things. Naming things in Scheme, as in mathematics, makes it easy for you to think about the order in which evaluations must occur and the values that are passed into them, which in turn determines the outcome of every computation you design when building Lambdas in Acceλerate for Microsoft 365.
One important point to make up front is that normally, when Lispers and Schemers talk about “lambdas,” they are referring to what other languages call anonymous functions. These are functions defined in-place without a given name, because they are considered single-purpose and their purpose is usually obvious in the code. When, however, lambdas are given proper names, they are called forms or functions; and usually they will be defined at some scope in which they can be used for some kind of processing.
In Scheme, names are bestowed upon things in a uniform way, and every “thing” lives in a single “namespace” at runtime that is built up by loading code from libraries on top of the symbols that come predefined.
For the more curious: The property of having a single namespace makes Scheme what is called a “LISP-1” Lisp. Common Lisp, by contrast, keeps two namespaces—one for functions, and a separate one for every other kind of named “thing”--making it what is called a “LISP-2” Lisp. Clojure, another popular Lisp in wide use today, is also a LISP-1.
The important thing to know about names is that they are changeable. You can assign different things to the same name at different times; but at any one time, a name only refers to a single thing.
In Scheme, names are assigned to things primarily by means of the (define …) form. The (define …) form assigns a name or symbol to a value, by which that value can now be referred. The (define …) form roughly looks like this:
(define <symbol> <initial-value>)
Another context in which things can be given names is inside what are called (let ..) clauses. These allow symbols to be defined in a narrower lexical scope, that retain those names until that scope is removed from memory. Regular programming language might refer to such things as “local” variables. Thoughtful consideration should be given to how you name things.
The identifier or name you give a thing is represented by its symbol, which can be composed of a fairly permissive collection of one or more alphanumeric Unicode characters. Scheme allows symbols used as identifiers to be composed of a surprising range of characters that most languages would reject for grammar/syntax reasons. By convention, symbols are “kabob-cased,” meaning that they are all lower-cased with dashes to represent spaces between words, so they look like shish-kabobs. But this is only a convention, and it's not unusual to see symbols made of punctuation marks, and characters in non-English alphabets.
For example, the *, /, + and - symbols we encountered are all valid identifiers that just so happen to be bound already to code that performs the expected mathematical operations. You can reassign those names to your own implementation of these operations, but it’s generally best not to do that.
Rather than explain all the possible character combinations of valid identifiers, it is in fact much easier to list the characters that generally cannot be used in identifier symbols. These include: parentheses, quote, unquote (the ` character), commas, the at symbol (@), and semicolons. Quote, unquote, commas, and the @ symbol are all used in macros, and the semicolon is Scheme’s “end of line” comment:
(define π 3.15) ; note everything after the semicolon is a comment ignored by the compiler
A comment that can span multiple lines is started by #| and ended with |#, e.g.:
#| I am a multiline comment |#
But pretty much every other combination of characters are allowed as symbols to be used to identify values, including “lambdas” (aka functions), with one important caveat: Numerical characters should not be used at the beginning of a name—it might be misinterpreted as a number and rejected as a malformed one.
Scheme’s uniform approach means that creating a “named” function is like creating any other named thing. For example, imagine a function that takes a number, which we will “name” n, and adds 2 to it. We will call it “plus-2” and define it thus:
(define plus-2
(lambda (n) ; 'n' is the name we give to the value to be passed in when called
(+ n 2)))That is the canonical way to define a “named” lambda (aka a “function”) in Scheme. But Scheme has syntax sugar that lets you define it slightly more concisely:
(define (plus-2 n) (+ n 2))
Behind the scenes, the syntax sugar version just expands to the canonical version. What’s nice about the syntax sugar version is it lets you picture what calling the function will look like. Substituting some number for “n”:
(plus-2 2) => 4 (plus-2 3) => 5
But you can also use “anonymous” (unnamed) lambdas to achieve the same thing:
((lambda (n) (+ n 2)) 2) => 4 ((lambda (n) (+ n 2)) 3) => 5
Here, Scheme evaluates the lambda to a function form without assigning it a name (at least not one that you can see), and then evaluates it with the supplied parameters. In the first case, the name “n” is assigned the value of 2 and then (+ 2 2) is evaluated, of course, to 4. In the second case, “n” is assigned the value of 3, and then (+ 3 2) is evaluated, of course, to 5.
In fact, this is also exactly how it works when you do call a lambda by name: it substitutes the name for the value (which in this case is a lambda expression) and then evaluates the lambda expression against the parameters supplied to it—just like we described in the case of named lambdas. So, because you named the lambda “plus-2”, you see (plus-2 2), while the interpreter sees what is behind the name, namely, ((lambda (n) (+ n 2)) 2). Substituting 2 for “n” simplifies the situation further and evaluates (+ 2 2).
So, the advantage of giving lambdas (and everything else) good names is clear: the code is more readable, and you have a lot less to type by referring to a thing by an ideally short but descriptive name you assigned to it. Scheme does not, strictly speaking, care what you name things. Therefore, be careful not to rename things by accident.
Understanding How Scheme Fits Into Excel
Now you have enough background knowledge to appreciate what is happening when you create, optionally name, and use lambdas in Excel using AcceλerateTM for Microsoft 365.
The =λ function is really about calling one-off, unnamed lambda functions like we saw above:
((lambda (n) (+ n 2)) 2)
In Excel’s formula expression language, this would translate to:
=λ(“(lambda (n) (+ n 2))”, 2)
Both return the same answer, 4, because they are the same function.
To give it a name, you would then use define:
(define plus-2
(lambda (n)
(+ n 2)))Which looks like this in Excel:
=define(“plus-2”,“(lambda (n) (+ n 2))”)
Note that the Lambda Editor that we provide conveniently pretty-prints your lambda definitions so you can read them with indenting and syntax highlighting (including highlighting matching parentheses) while you work on them. When you’re ready to save your code changes, it also “crunches” or flattens your code into a one-liner suitable for the formula bar. It could be quite painful to write long, intricate S-expressions as one-liners by hand, but it can be done. Fortunately, you don’t have to worry about that.
When you wish to evaluate a lambda function with parameters, there is the =eval function, which allows you to choose either writing one-off lambdas and calling them just like the =λ function, or to refer to the name of a lambda already defined by its Scheme name (or by an Excel name that you can also give it).
=eval(“(lambda (n) (+ n 2))”,2)
or
=eval(“plus-2”,2)
These are equivalent, assuming “plus-2” is already defined elsewhere. If the cell where you defined “plus-2” also was given the Excel name PlusTwo, you could call it like this:
=eval(PlusTwo,2)
The advantage of this approach, as we mentioned in the How to Create a Definition section of the Becoming Productive with Acceλerate for Microsoft 365, is that if PlusTwo’s code changes, this manner of calling it will ensure that this call is evaluated immediately after the code changes. This is not guaranteed using the Scheme name, which is more appropriate to use when you know a lambda definition won’t change frequently.
Finally, we have the =apply function, which Schemers will recognize because in Scheme a predefined function called “apply” behaves in a similar fashion. In normal Scheme you call apply when you want to take a lambda, either by name or as an inline expression, and “apply” it to each element in a list of zero or more elements. For example:
(apply + ‘(1 2 3 4)) => 10
The “+” function basically gets called like this:
(+ 1 2 3 4) => 10
You use =apply when you have a variadic function that you want to apply, usually to a column or row of values, but you can also use “literal arrays” from Excel’s formula language:
=apply(“+”, {1, 2, 3, 4}) => 10
Assuming those values were in cell range A1:A4, you can also call it like this:
=apply(“+”,A1:A4)
Obviously, Excel already has a SUM function that does what “+” does, but you aren’t limited to the “+” function - any function that takes zero or more parameters can be called with =apply in this way, including functions you write. So =apply is really a more general, reusable approach to dealing with lists of data.
In Summary
This brief primer barely scratches the surface of what is possible to build on top of Scheme using the functions we expose through AcceλerateTM for Microsoft 365. Refer to the section Helpful References and Resources in the Resources for Acceλerate Microsoft 365 for a list of Scheme and other resource books that we believe are great references to assist you on your journey with Scheme and Acceλerate for Microsoft 365.
The following terms are registered trademarks of the Microsoft group of companies and are used in accordance with Microsoft’s Trade and Brand Guidelines: Microsoft, Microsoft 365, Microsoft Office, Microsoft Excel, Microsoft Edge, Microsoft Edge WebView2, Microsoft Windows, Excel, Office 365
The following terms are registered trademarks of Apex Data Solutions: Visual Scheme, VSA.
Copyright © 2022. Apex Data Solutions, LLC. All Rights Reserved.