TABLE OF CONTENTS
Getting to Know the clr-* Functions
Calling into .NET Libraries
As discussed earlier, at your fingertips are two whole new “worlds” of code into which you can tap to take your Excel spreadsheets to the next level. In this section we will focus on the .NET world. In the next section, we will give you some idea of the libraries and features of the Scheme world. These two worlds are at your disposal because the Scheme we integrated into Excel happens to be implemented in, and itself be fully integrated with, the Common Language Runtime (“ CLR”), aka “.NET.”
Now we will take you on a thorough walkthrough of accessing .NET via Scheme in AcceλerateTM for Microsoft 365.
This is as much a part of the value proposition of VSA® as a companion to VBA and the formula expression language of Excel as the uncanny power of the Scheme language itself—and for some purposes, it may even be the most immediately useful part of it.
.NET’s ecosystem of commercial and open source frameworks and libraries is vast, and the CLR (Common Language Runtime) is Microsoft’s official development platform not just on Windows, but on other operating systems as well. If you are contemplating taking the business value “trapped” in your Excel spreadsheets on Windows to the cloud, the .NET runtime is your friend. VSA runs on all variants of .NET, including those that run on Mac and Linux.
Note: Future Acceλerate products will make porting your digital assets currently bound to desktop-based Office solutions to the cloud much easier, including support for Docker and other technologies associated with modernizing legacy applications.
While VBA can call into C/C++ DLLs through its foreign function interface, it cannot so easily tap into the .NET framework without some heavyweight COM-based bridge. VSA makes this much easier with a little Scheme coding.
Getting to Know the clr-* Functions
IronScheme comes with a library for accessing the CLR directly called (ironscheme clr). We ensure that when you open Excel with AcceλerateTM installed, or adb.exe from the Acceλerate Ribbon or command line in your working directory, this library is already loaded.
In order to be able to call into the .NET Framework and the libraries we provide with Acceλerate for Microsoft 365, we recommend that you familiarize yourself with this library and learn to write your own library code that simplifies calling into the CLR in your lambda functions.
We call the process of writing idiomatic Scheme code to access a .NET library “wrapping” the library. The goal is to hide all of the verbose boilerplate associated with calling into .NET’s object-oriented programming model in a more functional style that allows you and users of your wrapper library to do a lot with very little code.
Rather than list the functions let’s consider what we need to do, and simply use them — explaining them along the way.
The first thing to understand is that to use a .NET library, its assembly must be “referenced,” meaning loaded into the current application domain. A great deal of the most useful libraries in .NET are already loaded, but it’s okay to request a reference to an assembly already loaded.
The next thing you need to do, to refer to symbols in a library, is make sure we are “using” it. This idea is familiar to C# and VB.NET programmers, who are used to “using” namespaces in .NET assemblies in their class files. Same idea here: we want to make sure the runtime is ready to look in namespaces we declare we are “using” for definitions of classes and methods that we want to use.
Once you have referenced an assembly and declared the namespaces you will be using, the next step is to understand the classes and other types you plan to use directly. Toward this end the .NET documentation is quite useful. If you are wrapping an assembly from an open source project, you will want its documentation available as well.
The main steps of wrapping is this:
Call into static classes and their public fields, properties and methods directly.
Make it easy to create new instances of objects from their class definitions.
Manipulate object instances by calling into their public instance fields, methods and properties.
Deal with other types such as enumerations, structures/records, etc.
Static classes are simply classes that offer methods you can call directly, without creating instances of objects. A good example of this is the Math class in the System namespace found in Microsoft’s .NET documentation. There are obviously not a lot of functions it has that Excel does not already have, but we will use this simply to show the idea of calling static fields, methods, and properties of static classes in .NET.
Creating a Scheme Wrapper
Suppose you want to refer to the value PI, which in the Math class referred to above is called:
Math.PI => 3.1415926535897931
As the documentation makes clear, PI is a field, not a method or a property. So we need to get the PI field of this CLR static class, Math.
The good news is that the System namespace is already loaded and referenced, so we can dispense with the ceremony of calling clr-reference and clr-using. In fact, this is probably the simplest imaginable call into .NET you can experience:
(clr-static-field-get Math PI)
CTRL-Shift-T:
Here is proof that .NET is the framework that VSA® includes.
But we can make it easier to reference by defining “pi”:
(define pi (clr-static-field-get Math PI))
CTRL-Shift-T:
You may note that I gave the Greek letter π the same value as “pi.” This was to show you that identifiers in Scheme are quite expansive in terms of Unicode support. I am able to do this because I installed the Greek keyboard in my Windows 11 and can alternate between English and Greek by the keyboard shortcut <Windows button>-<spacebar>. Thankfully, the English letters P and L resolve to π and λ, respectively.
So that’s clr-static-field-get. Note it takes two parameters: the class name, and the field name, and case is important. There is also clr-static-property-get for properties. For setting values that can be changed, there are clr-static-field-set! and clr-static-prop-set!. Note, you cannot change the value of PI:
For the more curious: The difference between fields and properties in the CLR is not important beyond knowing whether a value you seek to use is one or the other, but usually fields are private and inaccessible, whereas properties are almost always public. Properties in C# and VB.NET can also have programmer-defined logic associated with getting/setting them, whereas fields are direct values. In this case the field PI is final/constant and cannot be changed, so there is no harm in making it public. This is a typical C# coding standard in action.
Now let’s do something a little bit more involved.
Let’s Build a Blockchain!
No, we are not necessarily talking about cryptocurrency here. We are, however, going to show at its most fundamental level what a blockchain really is, a task that requires calling into .NET cryptography libraries. Fortunately, it’s none of the complicated things you hear about in the news and marketecture surrounding “cryptocurrencies.”
All a blockchain is, when you boil it down and strip it of any domain-specific application, is a list of “blocks” containing values that are hashed according to some predetermined hash algorithm, typically SHA256. Each block hashes the value of the data in the current block, combined with the hash computed for the previous block. The hash is a one-way function: the same string input always produces the same hash, assuming you use the same hash function algorithm. However, you cannot deduce from a hash what the original string was. This establishes a secure “chain” of values that are tamper-resistant. Changing the slightest jot or tittle of the data of any block causes all subsequent blocks’ hashes to be incorrect, thus identifying clearly where the data was tampered with.
We will emulate this by calling into the System.Cryptography namespace and using the SHA256 class. We will also need to call a couple static classes in the System.Text namespace to convert a string to a byte array usable by the SHA256 hash algorithm class, and to tidy up the result.
So, in the REPL, let’s experiment with a function to convert a string to a byte array, and then a function to apply SHA256 hash to a string.
In C#, this is an easy call:
Encoding.UTF8.GetBytes(<some-string>);
We need to remember to refer to the relevant namespaces:
(clr-using System.Text) (clr-using System.Text.Unicode) (clr-using System.Security.Cryptography)
Now, after CTRL-Shift-T, we can call into the relevant APIs.
However, it sometimes takes a bit of experimenting in the REPL to find the correct incantation:
This is what the REPL is for - it is okay to make mistakes and “discover” whatever you need to learn along the way, because it is in its own space, not affecting your spreadsheet until you tell it to.
The whole idea here is to experiment and come to a correct understanding of how things need to work from the “bottom up.”
Now the implementation is clear. We need to understand that the Encoding.UTF8 property is a reference to an instance of the UTF8Encoding class. We use clr-static-prop-get to obtain that instance, but we must call clr-call in the correct way with the instance we obtained.
The only thing “tricky” about calling UTF8Encoding.GetBytes(...) is that it is overloaded. That is, it is defined multiple times with different data types as input. We are interested in the version of GetBytes that takes a String. We need to tell IronScheme this, so it can resolve it to the correct method that it needs to call. We do this by providing the signature for GetBytes we want, namely, the one that takes a single String instance, in parentheses.
(define dotnet-string->byte-array
(lambda (s)
(let ((utf8 (clr-static-prop-get Encoding UTF8))
(bytes (clr-call UTF8Encoding (GetBytes String) utf8 s)))
bytes)))CTRL-Shift-T:
Now let’s test this with some strings. The output isn’t anything we care to look at, but it’s what the SHA256 class needs.
Now that we can take any string and turn it into the byte array, let’s move on to the SHA256 class wrapper.
The SHA256 class has a static method called Create() which returns either a SHA256Managed instance if FIPS is turned off, or SHA256Cng instance if it is on. We’re not trying to secure anything special here so we will just skip this and create a new instance of SHA256Managed. Assuming we pass in System.Byte[] as the input to our function, there are a couple steps we will want to go through to get it out in exactly the form we want.
All of this can be contained in a single function:
(define sha256-hash
(lambda (bytes)
(let* ((hash-fn (clr-new SHA256Managed))
(raw-result (clr-call SHA256Managed (ComputeHash System.Byte[]) hash-fn bytes))
(bits-str (clr-static-call BitConverter ToString raw-result))
(clean-bits (clr-call String Replace bits-str "-" ""))
(lower-bits (clr-call String ToLower clean-bits)))
lower-bits)))Walking through this step by step:
Clr-new creates a new instance of SHA256Managed, which we name “hash-fn.” Basically, that’s what this instance of SHA256Managed class is. Recall that this class is found in the System.Security.Cryptography namespace, which we previously loaded via the (clr-using System.Security.Cryptography) call.
We use the let* form because each step along the way we are using a symbol bound in the step just before it.
To compute the hash we have to call the ComputeHash() method on the instance of SHA256Managed that we created. That method is overloaded - or there are multiple versions of the same method with different inputs and input types. The one we care about accepts a byte array, or type System.Byte[]. Our Scheme’s integration with .NET makes it possible to call into the runtime in this way, because (ComputeHash System.Byte[]) tells our Scheme how to find the method we want. We pass in the “hash-fn” instance we just created, and the “bytes”, and name the return value “raw-result.”
What we get back from that looks something like this: #vu8(69 103 200 24 104 38 14 100 120 255 187 56 124 228 17 73 138 166 254 166 174 135 57 47 193 38 100 16 156 144 147 180), which is not ideal for human viewing. It’s the raw Scheme bytevector type. So we use BitConverter’s static ToString method to transform it into something a little bit easier to read.
Even that isn’t much better, but at least it’s looking like something useful: "45-67-C8-18-68-26-0E-64-78-FF-BB-38-7C-E4-11-49-8A-A6-FE-A6-AE-87-39-2F-C1-26-64-10-9C-90-93-B4"
Now to get rid of the dashes we call String’s instance method Replace, to replace the dashes with nothing. And finally we set the result to lower case (for those bits that use alphabet characters) and return them. Now this looks like something we’d expect to see: "4567c81868260e6478ffbb387ce411498aa6fea6ae87392fc12664109c9093b4"
So let’s do this in the REPL:
(define genesis-block "This is my genesis block.") (define genesis-block-bytes (dotnet-string->byte-array genesis-block)) (define genesis-block-hash (sha256-hash genesis-block-bytes))
Looking good:
Now let’s combine the steps transforming a string to bytes and computing the hash so that the user can just pass a string to the hash function. In this way we can say we have fully “wrapped” the SHA256 hash generating capability of .NET (we can worry about FIPS compliance later, as it is only a two-line tweak).
Along the way we have learned about several important functions that are the key to Scheme interoperability with .NET. Moreover, we learned a few things about how to deal with method overloading, discerning between fields and properties, the difference between static and instance methods on .NET classes, REPL-driven development, and a lot more if you think about it.
So here is the final function:
(define string->sha256
(lambda (str)
(let* ((utf8 (clr-static-prop-get Encoding UTF8))
(bytes (clr-call UTF8Encoding (GetBytes String) utf8 str))
(hash-fn (clr-new SHA256Managed))
(raw-result (clr-call SHA256Managed (ComputeHash System.Byte[]) hash-fn bytes))
(bits-str (clr-static-call BitConverter ToString raw-result))
(clean-bits (clr-call String Replace bits-str "-" ""))
(lower-bits (clr-call String ToLower clean-bits)))
lower-bits)))And let’s test it:
(string->sha256 genesis-block)
CTRL-Shift-T:
This is exactly what we want. Notice I changed my genesis block as the original version I typed in was incorrect, missing the verb “is” in my sentence. This leads to a different hash output, which is exactly the whole point of hashing. Now we can turn this into a reusable function, and get busy building our modest little blockchain.
So the way this will work is quite simple: We will have a column of data, and a column for the hash of that data. What we need to do now is create some lambda functions! We want names for these, so we will start with “promoting” string->sha256 to a ‘defined lambda’:
A warning has appeared. It looks like we are running up against the built-in Excel size limitation on string inputs to Excel functions. We have a couple options, one of which is splitting the functions back up to get under the limit. We explain this approach in the the Tutorial: Creating Reusable Solutions to Practical Problems.
Here, we will focus on a more advanced method and make this a proper library instead. We will save our library into our working directory, which is %USERPROFILE%\.accelerate. On my machine, that’s C:\Users\Bob\.accelerate. Yours will be specific to your user account name.
In that directory is a \lib subdirectory. To keep it simple, we will just create the library, which we will call blockchain.sls, right in that directory, so that we can import it simply with
(import (blockchain))
We will add this to the init.ss file in our working directory when we’re ready to test it.
Here is the code for the blockchain.sls library. We will explain the pieces to this after you have a chance to save a copy in your lib directory (see the Add Scripts to Your \scripts Directory and Run Them in the Advanced Topics 02 - Setting Up a Dev Environment in Acceλerate for Microsoft 365):
(library (blockchain)
(export string->sha256
data-mine
genesis-block
genesis-block-hash)
(import (ironscheme)
(ironscheme clr))
(clr-using System.Text)
(clr-using System.Security.Cryptography)
(define genesis-block
(make-parameter
"This is my genesis block. ))
(define genesis-block-hash
(lambda ()
(string->sha256 (genesis-block))))
(define string->sha256
(lambda (str)
(let* ((utf8 (clr-static-prop-get Encoding UTF8))
(bytes (clr-call UTF8Encoding (GetBytes String) utf8 str))
(hash-fn (clr-new SHA256Managed))
(raw-result (clr-call SHA256Managed
(ComputeHash System.Byte[]) hash-fn bytes))
(bits-str (clr-static-call BitConverter ToString raw-result))
(clean-bits (clr-call String Replace bits-str "-" ""))
(lower-bits (clr-call String ToLower clean-bits)))
lower-bits)))
(define data-mine
(lambda (current-block-data previous-block-hash)
(let* ((combined-data
(string-append current-block-data "+" previous-block-hash))
(result (string->sha256 combined-data)))
result)))
)You will note that in addition to the string->sha256 function, we have added a few extra items to make our blockchain emulation more real-world:
genesis-block - Every blockchain has an initial block that is referred to by this name. It contains only the hash needed for subsequent blocks to compute properly. The thing to note about this definition, something perhaps you have not seen yet, is the fact it’s defined as a parameter. Parameters in Scheme are like dynamic variables, very useful when you want to manipulate their values in lexically scoped scenarios, such that you don’t affect their value outside that block. In order to get the value of a parameter, you call it like a function that takes no arguments, so in this case, (genesis-block).
genesis-block-hash - This is a function that will return the hash of the current value of the genesis-block parameter.
data-mine - A function that, given a block of data presumed to be the current one, and the hash of the previous block, will concatenate them into a single string and return the hash for the current block. In short, this is what “data mining” means in the context of blockchain: computing the hash of the current block. In a cryptocurrency context this step is made arbitrarily difficult by adding a constraint like “the hash must begin with four zeros” in which case various algorithms are used to modify the data block with a “nonce” that will produce a hash matching this constraint. We have no such extra level of difficulty we wish to add to this demo, so we will not include this here.
All we need to make our code work is to import the (ironscheme) and (ironscheme clr) libraries. In the context of a library you have to be explicit about what you import into the library’s “name space.”
Finally, we include the (clr-using …) clauses we need to make sure our CLR classes are also visible in the library’s “name space.”
The rest is just the same function we defined via the Excel REPL. To test this, we advise you to use the command line REPL from the Windows Terminal, since this will let you easily exit and restart the command line REPL as you need to. Again, to access Windows Terminal from the Acceλerate Ribbon, click on the REPL button and choose Open Windows Terminal.
The terminal will pop up, already located in your working directory:
Now you can enter the REPL by typing ‘adb repl’ and hitting <ENTER>.
Now we can try to test our functions. Let’s start with the genesis-block-hash:
We forgot to update our init.ss file to add (blockchain) to the import specification. Let’s do that now:
Now we have to exit and re-enter the REPL:
Now we can try to test our methods:
That worked. Now let’s test both our data-mine and our string->sha256 methods by calling data-mine. We will pretend we’re making a new block that needs a hash composed of the data of the current block and the hash of the previous block. This would be the very next block after the genesis-block:
We are now ready to get back to our spreadsheet. You should be able to restart Excel and find that this works in the REPL automatically:
The next step is to emulate a real block chain. First off, let’s take a quick step back to understand what the purpose of the blockchain algorithm is. The purpose of a blockchain is to enable two parties to have copies of the same data, and to be absolutely certain that what both parties have is in fact the same data. This may not sound as exciting as the bitcoin application, but bitcoin itself would be impossible without this feature of the blockchain algorithm.
So we will emulate that by making some data in one column, call it Party A’s Copy, and a few columns over we will copy it over to Party B’s Copy. Then we will use our blockchain functions with some =eval statements to emulate the hash functionality, and we will show what happens when somebody tinkers with one of the copies.
For data, we will use the lines of a well-known children’s poem:
Our goal is to treat each data “block” in column A as a block in the blockchain, and in Column B we will compute the hashes for each block. First we need the “genesis block hash” in B2:
For this we clicked the λ Lambda button and selected Insert Lambda, and replaced the boilerplate (ultimate-answer) with our (genesis-block-hash) function call. Now our blockchain has a genesis block.
Next we will use =eval to call data-mine in B3, passing it A3 for the current block data and B2 as the previous hash. This is such a simple call, we will do it by hand:
=eval(“data-mine”,A3,B2)
Now we can copy B3 and paste it from B4 down to B18:
And just like that, we have a blockchain!
So now we will copy Party A’s Copy of the data from column A to Party B’s Copy in column C:
The next step is to perform the same steps to compute the hashes on Party B’s copy:
Now the 2nd block:
And finally the rest:
The neat thing about a blockchain is that to know you have exactly the same data as the other party, you do not need to pour over every single block of data and compare. All you need to do is compare the hashes of the last block. If they are the same, you know the copy of the data is an exact replica of the original data.
It may be a little hard to read, but let’s zoom in. Here’s Party A’s copy:
And here’s Party B’s:
And they are indeed the same. Now, what happens if someone tampers with the data in one of the copies?
Let’s change row 10 in B’s copy to:
To see a lambda at school
Now look at B’s hashes:
Starting at the “block” we tampered with, all the hashes down the rest of the chain no longer match what A’s copy has for hash values at the same data points.
So not only do we know that data changed somewhere, we can identify the precise block where the tampering occurred, and treat the rest of the “chain” as compromised.
This is what makes blockchain so potentially useful in so many domains. It helps with the data integrity problem that plagues every attempt to share data across organizational boundaries.
In Summary
As you can see, integrating .NET into Excel via Scheme allows us to bring some really powerful custom functions to our spreadsheets. In this example, we illustrated how blockchain works by using .NET Cryptography libraries behind nice, clean Scheme “wrappers” that you added as a library to your working directory, and then called them from Acceλerate’s built-in lambda functions.
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.