TABLE OF CONTENTS
Approach #1 - Using Pure Scheme Functions
Approach #2 - Using .NET Functions
This tutorial will walk you through a comprehensive, end-to-end example of solving a practical problem using Visual Scheme for ApplicationsTM (“VSA®”), and iteratively improving it from “one off” stage to something that can be reliably shared across all your Excel workbooks.
When you are done working through this example, you’ll have a great grasp of the basics of solving problems in Excel with VSA, and be well on your way to mastering some of the most fundamental skills required to become proficient in it.
For purposes of this tutorial, we will consider the following mildly contrived problem, inspired by the December 2020 Microsoft article Announcing Lambda: Turning Excel Formulas into Custom Functions. Along the way we’ll highlight some of the unique benefits of solving problems in VSA compared to alternative approaches, including but not limited to the upcoming LAMBDA function from Microsoft.
The Problem
The task is to come up with a reusable way to parse out the particular US state referenced in the Station ID by the state’s two letter abbreviation.
We will call our custom VSA function get-location, as in the example from the article. In the referenced Microsoft article, they initially use this complex formula based on their built-in Excel functions:
=LEFT(RIGHT(B3,LEN(B3)-FIND("-",B3)),FIND("-",RIGHT(B3,LEN(B3)-FIND("-",B3)))-1)
Yes, this complex formula works.
However, it is hard to read - you cannot be certain as to what the formula is supposed to do. Moreover: it is not reusable in a clean and efficient manner. Relying on the cut-and-paste method of reusing formulas from the clipboard is clunky, error prone, and inefficient.
With AcceλerateTM for Microsoft 365, there are two implementation approaches you can use to achieve the same goal, all while creating a custom function that is reusable in a clean, efficient manner. Approach #1 is using well known functions from the Scheme programming language, and Approach #2 using .NET functions.
We will show both of these implementation approaches so that you can understand all your options.
Solution Overview
The simplest abstract approach, regardless of implementation options, is illuminated by noting that the Station ID has a delimiter between its “parts”, namely the dash (“-”) character. Splitting the string on that delimiter, and grabbing the 2nd of 3 “parts” in the resulting list will do the trick. Doing so is a 2 step process:
Original string: “362273-LA-9763”
Step 1: Split string on the dash character “-” : (“362273” “LA” “9763”)
Step 2: Grab the 2nd value in the list: “LA”
Approach #1 - Using Pure Scheme Functions
In our example, the first step in solving the problem is to split the string at the dash “-” character. VSA is based on R6RS Scheme, so we can use a pure Scheme implementation approach to do this. The pure Scheme approach involves implementing a string splitting function in Scheme, and then getting the 2nd element of the resulting tokenized list. Due to Scheme’s long-standing popularity in the programming world, there are many resources to find a solution to do just this, which you can find by conducting a simple internet search. For the purpose of this demonstration, we have chosen to use an implementation originally created by a well known Lisper/Schemer named Doug Hoyte found in GitHub Gist:
We’ll use this GitHub Gist implementation of str-split to take a string and a character delimiter, and return a list of strings “split up” by the provided delimiter. To do this, click on the “Raw” view in GitHub Gist. Note: copying the code from the HTML view will include a lot of characters we don’t need or want to paste.
Highlight everything from “(define …” down…
and type CTRL-C to copy it.
Let’s test this in the REPL first. From a workflow point of view, the REPL serves as an initial playground where you can try code out, see if it works, experiment with how you want to work with it, and otherwise work “bottom up” on a solution.
Click on the Acceλerate tab, and press the REPL button so the REPL pops out.
In the editor region at the bottom right, click CTRL-V to paste the code copied from GitHub Gist
Now type CTRL-SHIFT-T to submit the code.
The code appearing in the response area like that with no errors means it is compiled and is ready for use. However, you will note that nothing is showing up in the related cell yet.
We just need to call it now. To do this, type (str-split "362273-LA-9763" #\-) in the editor on the lower right.
Now submit the code by typing CTRL-SHIFT-T.
As you can see by the blue colored listed items in the REPL, we now have our list. Let’s save this as a variable so we can play with it. Type
(define the-list (str-split "362273-LA-9763" #\-))
into the code area on the lower right.
And CTRL-SHIFT-T…
And now it is saved. We can check this by typing “the-list” in the editor area and submitting it (CTRL-SHIFT-T):
As you can see, “the-list” holds the value returned when you originally called the split. Because we gave this value a name (“the-list”), we can now refer to it subsequently. We have completed the first step in our solution.
For the second step, we need to grab the 2nd value of this list - the US state two letter abbreviation - of the station.
As a descendant of Lisp, Scheme provides numerous functions for this. One is the ‘car’ function, which returns the first item of a list. The “cdr” function gives you the “rest” of the list from the 2nd element on. What we want is the first item of the “cdr” of the the-list, or as you might say, the “car” of the “cdr”... i.e.,
(car (cdr the-list))
Or alternatively, the “cadr” of the list.
(cadr the-list)
Fortunately, Scheme does us a favor. Scheme has a type called vectors which allows access to items by their “index” location in the vector. Converting a list to a vector is easy; there’s a standard function for that called list->vector. So:
(define the-vector (list->vector the-list))
Vectors look like lists when printed in the REPL, except they are prepended with a hashtag. That’s how you know a vector from a list in Scheme code, especially in the REPL. Now that you have it in vector form you can call vector-ref, which takes a vector, and the 0-based index of the item you want.
So basically if you want the 2nd item, you subtract 1 from 2, and ask for the “one-th” (the “first” item is technically the “zero-th”):
(vector-ref the-vector 1) ;; grab N, the second item in the vector. So, N-1 = what you want.
So tying all this together, let’s use the Scheme approach to distill this process with vectors instead of the archaic car/cdr/cadr combinations. We’ll make a function called station-id->state which, like its name implies, will take a station ID and return the state from the ID.
(define station-id->state
(lambda (station-id)
(let* ((a-list (str-split station-id #\-))
(a-vector (list->vector a-list))
(state (vector-ref a-vector 1)))
state))) And that’s our “two-step” process—separate the ID into a list, and grab the 2nd item in the list. We decided to make it a vector first, but that was a simple translation using a standard function.
The only function we needed to implement is str-split.
And now let’s try it:
And try another one:
So, now that we’ve used the REPL to work out that our borrowed pure Scheme str-split function works, and created a function in the REPL to transform a Station ID into a state, we can test calling them. For this purpose, we will use the =eval function (see The Lambda Functions in Becoming Productive with Acceλerate for Microsoft 365), since our station-id->state function takes exactly one parameter. (We would instead use our =apply if a function we want to use takes a variable number of parameters.)
Let’s call our function using =eval in the C4 cell. Highlight that cell, and from the λ Lambda drop down on the Acceλerate tab, and click Evaluate a Named Lambda. You should see this:
The “Insert …” functions provided via the Ribbon are a starting point for writing our functions correctly, but will usually require editing. In this case, the default behavior is to use =eval with a lambda “inline” (a lambda that has not been named); but what we want to do is to call “station-id->state” so we need to change it from “inline lambda” mode to “call a form” mode. You’ll notice a button with a squiggly line with arrows at both ends, to the left of the Cell: field at the top of the Lambda Editor.
Click that button and now you’ll see this:
Change the Form: field from “existing-form” to “station-id->state” and click the Accept button.
You’ll see that now in cell C4 we have an error message, telling us that it was expecting 1 argument, but got 0. This is correct. We need to input the Station ID, in this case, the one located at cell B4. So, in the Lambda Arguments section of the Lambda Editor, type in B4.
And click the Accept button.
Now you have the correct result: OR.
So now that we know the functions work, we need to persist them. As things stand right now, having worked through the REPL to create them, they live in memory only. If you shut down Excel now and re-open your workbook, you’ll get an error message to the effect that you’re trying to call an unknown identifier.
Let’s start with the str-split function. Pick a blank cell off to the side. Click the dropdown on the λ Lambda button and select Define a Named Lambda.
What you see is the “boilerplate” =define statement in the formula bar, which is a lambda called “deep-thought” that takes no arguments and returns 42.
What you see in the cell where you defined the new lambda function is a visual representation of the fact that it contains a lambda, named “deep-thought” which takes 0 arguments. Everything on the right side of the “@” is a hash that you can ignore. It will change if you modify and save lambda code. In this way, a change to code will trigger Excel’s recalculation engine when you call this lambda as a defined Excel “name.” We’ll get into assigning an Excel name shortly. First, let’s focus on defining the lambda on the Scheme side of things.
We want to rename the boilerplate function to “str-split” and copy the text we found on Github Gist to replace the boilerplate lambda expression.
First of all, we need to change the first line of the lambda code. When writing Scheme code in a file using a text editor, that way of writing it is fine. But the Lambda Editor expects a lambda expression.
To achieve this, all you have to do is to change “define” to “lambda,” remove the name from the first set of parentheses, and remove the last parenthesis at the end. Why?
The original style in which the function was defined was syntax sugar for the following canonical Scheme form definition:
(define str-split
(lambda (str ch)
(let ((len (string-length str)))
(letrec
((split
(lambda (a b)
(cond
((>= b len)
(if (= a b) '() (cons (substring str a b) '())))
((char=? ch (string-ref str b))
(if (= a b)
(split (+ 1 a) (+ 1 b))
(cons (substring str a b) (split b b))))
(else (split a (+ 1 b)))))))
(split 0 0)))))Because the =define function accepts the function name in the text box and builds around the inner lambda, that’s all you need to write in the text box—the inner lambda expression. That is,
(lambda (str ch)
(let ((len (string-length str)))
(letrec
((split
(lambda (a b)
(cond
((>= b len)
(if (= a b) '() (cons (substring str a b) '())))
((char=? ch (string-ref str b))
(if (= a b)
(split (+ 1 a) (+ 1 b))
(cons (substring str a b) (split b b))))
(else (split a (+ 1 b)))))))
(split 0 0))))Now click CTRL-SHIFT-T to accept the above code and tab into the Lambda Arguments text box.
Note the red warning at the bottom of the Lambda Editor. We are running into an Excel limitation now. Generally formulas in Excel can be quite large - the maximum number of characters permitted in a formula expression is 8,192 characters. However, no string passed to a formula can be greater than 255 characters.
Although this feels very constraining, we can actually work around it quite easily. So let’s back out of this by clicking the Cancel button.
To work around this Excel limitation, we need to save the str-split function to a file that gets automatically loaded into Excel when it starts up. To find this file, go to the REPL, and type (current-directory), then CTRL-SHIFT-T.
What you see there is what you can think of as AcceλerateTM for Microsoft 365’s “Default Working Directory” which is located off of your Windows account’s “Home” directory, usually referred to as %USERPROFILE%.
The .accelerate folder off your home directory is where you can configure your local installation to ensure certain Scheme libraries that we include are pre-loaded, modify the default search path for libraries, and include custom code.
It is beyond the scope of this document to describe everything that could be done in the Default Working Directory. However, for our purposes, the one thing you must know about is an optional file called “init.ss” which, if present in the current working directory VSA will pre-load at startup. This is a great place to put our str-split function, so let’s do that.
On first use of Acceλerate for Microsoft 365, some files will be created in the default working directory at C:\Users\<username>\.accelerate. One of those will be a very basic init.ss file. This is the one you should modify during this tutorial.
Using Windows Terminal or your favorite editor, add the canonical form we added above to the init.ss file in the directory returned by (current-directory).
Generally, an init.ss should be structured as follows:
The first thing you must add are any import specs. Import specs are used to pre-load any libraries that you intend to be available in Excel regardless what spreadsheet you are working on. See our Advanced Topics documentation to learn more about this topic. For the purposes of this Tutorial , we are adding a built-in logic programming framework called miniKanren, which will now be available in Excel every time it loads.
The next thing you may need to do is adjust the search path. Again, see our Advanced Topics for more information related to this.
After doing these steps, you can include any functions you want accessible at runtime. There are no limits to how large or how complicated code you put here can be. Now, the Excel character limitation (255 characters) is no longer a problem as you will be able to write lambda expressions that are short, clear, clean, and usually just referring to code you can load from here.
Save that file, then save your workbook and restart Excel.
Now, let’s try saving that function again using the Lambda Editor and the =define function. Again, go to the Acceλerate tab, press the λ Lambda button, and choose Define a Lambda. In place of the boilerplate code that appears in the Lamda Editor, change it to the following:
Be sure to click into the Lambda Arguments text box (or use the CTRL-SHIFT-T keyboard shortcut); it triggers the Formula Preview to update. And finally, click Accept.
In this example, we named the lambda get-location.
Now delete the cells in C3 and C4 and put your cursor in C3.
Click the λ Lambda dropdown button and click Evaluate a Named Lambda.
Once again you’ll get the boilerplate.
Once again, toggle out of “inline lambda” mode either by clicking the “Swap” button or by typing the CTRL-SHIFT-X keyboard shortcut, and change the Form: field to get-location.
A familiar error appears. You may remember that we had to pass it the station-id argument. This time, let’s use Excel’s standard Function Arguments editor. Click the fx button in the Formula Bar.
Notice, you can in theory use this form for any of our functions. But you’ll definitely prefer our Lambda Editor for writing lambda expressions using either the =λ or the =define function. For =eval and =apply, however, the standard Excel Function Arguments dialog has the advantage of being able to point-and-click assign regions and cells to arguments. Try this out by selecting the A1 (as in, “argument 1”) box and then clicking the “point to” button. Highlight B3.
Then click the little blue “down” button at the far right edge of the Function Arguments window which was temporarily shrunk to allow you to highlight a cell or region.
Notice the result “WA” is already shown in the dialog. Click OK to close the dialog.
Now instead of using the Scheme name - which is better when the function is stored in a file - we will give the get-location a proper Excel name. So let’s highlight E3.
Note that the Lambda Editor “pretty prints” your code, and “crunches” it for entry in the formula bar. This is why you REALLY want to use it when writing a lambda expression, as opposed to the Function Arguments dialog. There is also still some room for code, about 100 characters, so you could still fit a larger expression than this in it. However, as it is, it’s short and quite readable code once you’re comfortable reading Lisp/Scheme code.
So what we want to do now is:
Give this lambda an Excel name
Change our =eval call to use the Excel name
Copy the =eval call to the cells below
To give it an Excel name, select the Formula tab, and click Define Name
Enter the name in the New Name pop up box. We will use “GetLocation”. Click Ok.
Now select C3 and highlight the Form text box, which currently reads “get-location”:
Replace it with GetLocation (no quotation marks or dash). You can tell a Scheme name in the Lambda Editor because it will be in quotation marks and probably contain dashes. An Excel name will generally by convention be all caps or camel-cased like I named GetLocation, and will not have quotation marks around it, or dashes in it.
Now click Accept.
Nothing changed, which is exactly what we expected. Now click C3 and copy the cell.
Now highlight C4:C7.
And hit CTRL-V or ENTER.
You have successfully extracted the state abbreviation from the Station ID.
Approach #2 - Using .NET Functions
Implementation approach #1 - using pure Scheme - is one way to solve the problem at hand. However, there is an easier way to achieve the same result.
VSA® is built on IronScheme and we have included all the IronScheme libraries, plus several others. One of those IronScheme libraries - ironscheme strings - already has a wrapper of the .NET String.Split function, that works like this:
(string-split "362273-LA-9763" "-") => #("362273" "LA" "9763")Since it is already pre-loaded, you can test this in the REPL.
So the final lambda that we COULD have used is literally a simple one-line piece of code:
(lambda (station-id) (vector-ref (string-split station-id "-") 1))
Let’s test it out by using the =λ function.
Click on D3, and then from the λ Lambda dropdown menu select Insert Lambda.
You should see a familiar result from the sample code in the Lambda Editor.
Enter the lambda one-liner referred to above into the text editor. Click or use the keyboard shortcut CTRL-Shift-T to set focus on the Lambda Arguments text box and enter the value “B3.” Then click accept.
Copy and paste the lambda in D3 to cells D4:D7.
And we’re done!
Normally, we do not recommend using cut and paste while using the =λ function, as it will recompile each and every instance for each and every call. However, in this case, we’re calling into optimized .NET code, and using built-in functions, so there’s very little extra overhead for a small amount of cutting and pasting. Typically, however, =λ is for one-off lambdas; once you plan to heavily reuse it, it’s always best to give it a name and call it with =eval (or =apply, if it’s a function that takes a flexible number of parameters).
In case you’re wondering what the code in ironscheme strings looks like that wraps the .NET code, here’s how it’s defined:
(define/contract (string-split str:string . del:string)
(clr-call String (Split String[] StringSplitOptions) str (list->vector del) 'none)) There are several calls starting with clr-* that are also already pre-loaded. If you are a developer with .NET experience, you will be able to easily wrap your favorite .NET SDK classes and use them right inside Excel!
What You Learned
You learned how to use the REPL to play with ideas, even with code from the Internet.
You learned how to use the Lambda Editor to:
Create a named lambda using the =define function
Evaluate a named lambda against actual parameters using the =eval function
Create an “unnamed” lambda using the =λ function
You learned how to overcome some limitations in Excel by adding to your init.ss file any import statements, custom search path configuration, and custom lambda functions that are either “too big” for Excel formulas, or that you want to re-use in other contexts (across workbooks, across Office apps, or any other application running on your desktop).
You learned that VSA brings two vast programming worlds — Scheme and .NET — right inside Excel, making things possible to do in a workbook that are limited only by your imagination and creativity.
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.