If you peruse the code in the xll library you might notice things being imported and exported. Libraries can be either static (.lib) or dynamic (.dll). Static libraries hook up functions at compile time. Dynamic libraries hook up functions at runtime. Import and Export controls what functions are available to your code.
The xll library is a static library. The .xll you create using it is a dynamic library, a .dll renamed with the .xll suffix so Excel can see it if you use File/Open… The /WHOLEARCHIVE flag is used to export all functions in xll.lib to create a self-contained add-in that does not require hooking up other dynamic libraries at runtime.

If you write an add-in that uses other shared libraries then it is on you to make sure they are available at runtime. If not, this is usually manifested by a bunch of gobbledy gook showing up in the spreadsheet after you open it. If your add-in uses a shared library then you have to make arrangements for that to be available at runtime. Use dependency walker to simulate what happens during runtime loading and identify the missing shared library. Charles Wright has reported there are newer versions of that.

Elon Musk just tweeted buy MPIR! I’m kidding, but I’m keeping my eye on the stock price tomorrow. 🙂

I scratched an itch I’ve had for a long time to allow big integer calculations in Excel. The main issue was building MPIR on Windows. Only add, mul, and sub so far. One issue was converting strings to and from the underlying C data types. The MPIR library uses char* for the string representation but Excel limits those to 255 characters. The workaround is to use a range of strings.

There is always the issue of modifying handles in memory or allocating new handles. My current thinking is using MPZ.MUL(z1, z2) for in memory updating of z1 and \MPZ.MUL(z1,z2) for returning a new handle.

Excel now has LAMBDA. If you define a name that refers to the formula =LAMBDA(args, ..., body) then you can use the name just like a built-in function by supplying its args,...

I’ve implemented a poor man’s lambda using only a few lines of code. It has the unusual, but hopefully apt, name \ which turns out to be a valid Excel function name. To create the ToCelsius function in the video enter =\("(5/9)*(_0 - 32)") in a cell to get a handle to the lambda. If you name the cell containing the handle ToCelsius then you can call it with \(ToCelsius, 451). The body uses the convention that the i-th argument is written _i. Creating and calling a lambda use the same function name \. If the first argument is a string and there are no other arguments it creates a lambda. If the first argument is a handle to a lambda then it uses that to call the function on the remaining arguments.

This is not production quality code. I don’t have Satya’s wallet to hire Haskell heavy hitters. If you figure out a way to break it let me know. If there is functionality (sic) that LAMBDA has that \ doesn’t, don’t be shy about pointing that out. It is not clear to me yet how many users will find either the rich man or poor man’s lambda useful.

I got bitten by a self-inflicted bug. The original code for embedding C++ objects in Excel using handles assumed functions returning handles occupied their own cell. Handles get created using new so there has to be a corresponding delete. The code for handle would look at the contents of the cell of the calling function and call delete if it was from a prior call. This was not perfect garbage collection, but it ensured at most one call to new for the object was performed. It also saved 1000 lines of code writing an “object manager” that would involve a lookup and a high probability of more bugs. In version 1 the funny looking doubles returned as handles are the C++ pointer. There is no lookup, only a cast.

The problem with version 1 was if a function had an argument that created a handle there was no way to call delete on it. Version 1 had to call back to Excel to get the previous value of a cell. Version 2 detected when this was not available and marked the object for deletion after the outer function returned. That can be expensive on memory, but at least it didn’t leak.

If you want to exempt a pointer from being deleted you can use safe_handle and access the handle using safe_pointer. If you go behind Excel’s back, it is your job to deal with memory. See xll_xml.cpp for examples of how I embedded libxml2 in Excel. Yes, libxml2 is a mess, but it has been battle tested and can handle XML you encounter in the real world.

The dumb thing I did was calling EVAL on a string that evaluated to a handle. The not so clever version 2 code would call delete on that since it looked like an argument to a function. The current solution is Don’t Do That!

Things have come a long way since Euclid and his axioms for geometry. It took 2000 years for people to discover his Fifth Postulate could not be derived from his other axioms by inventing non-Euclidean geometries. It was a bit shocking to realize mathematics does not involve absolute truth. Hilbert showed Euclid missed a few axioms in his theory but had the hope that any mathematical theory could prove any true statement.

Godel blew that out of the water with his Incompleteness Theorem. Any theory that allowed standard arithmetic had a true statement that could not be proved using the theory. Even if you added that statement as an additional axiom there would be other true statements that could not be proved.

Jean-Yves Girard introduced new logical connectives to bring resources under mathematical consideration. It allowed for the notion of a statement being used only once in a proof. We now have Haskell and Rust that can be run on a computer instead of being abstract mathematical constructs.

Maybe if I understood borrow checking better I could solve the EVAL problem.

This is still a work in progress, but I’ve been eating my own dog food and liking it: Win Inet for Excel. Let me know if you find something that breaks and open an issue on GitHub so I can fix it.

This add-in lets you grab a URL and put the characters returned in memory. Parse them as XML or HTML using the battle-tested libxml2 library used by etree in Python. Extract anything you want by manually traversing nodes or use XPath to zero in on exactly what you are after. It also has functions for parsing CSV and JSON into an Excel range.

JSON maps quite neatly to Excel. A JSON object is a two row range with keys in the first row and values in the second. A JSON array is a one row range of values. JSON primitive types number, string, boolean, and null correspond to Excel types xltypeNum, xltypeStr, xltypeBool, and xltypeErr with val.err = xlerrNull. Excel needs a little help to play nice with ranges that are recursive. You can use HLOOKUP to return a primitive type using the key and returning the second row but if the value is an object or array you will get a #VALUE!. Use JSON.VALUE(val, key) instead. The key can be an array of keys to recurse through or a jq-like dotted index. JSON arrays are indexed by 0-based numbers. Unlike jq, they should not be enclosed in square brackets.

There are plenty of products that do Monte Carlo simulation in Excel. Here is one more: https://xllmonte.com. Do an A/B test if you are already using one and let me know if theirs is simpler, faster, or easier to use. My product does not have many bells and whistles, or even examples of how to use it other than a simple demo showing how to trade space for time in a spreadsheet. It is a stripped down version of what I’ve been using with my hedge fund clients over the years.

They love using it and told me I should spin off a commercial version. They have no idea how difficult it is to write software that works on versions of Excel in the wild. If you help me shake down the issues involved with this I would love to keep rolling out new functionality. What problem can it help you solve?

If you are an Excel power user please have a look at https://xlladdins.com. It is still in early stages so any feedback will be appreciated. Currently there are add-ins for the C/C++ math library and Windows registry. The add-ins are signed but until I scrape up enough nickels for an EV (“Expensive Validation”) CA you will still see warnings when opening them in Excel. Who says money can’t buy you friends?

If that makes you nervous then you can build everything from source for yourself using the free version of Visual Studio. It should be as simple as cloning the repository in VS and hitting F5. Let me know if that does not work for you.

The xll_math library uses a python script to scrape the documentation from web pages and generate C++ code. I work hard to be lazy. The xll_registry library scratches an itch that I am surprised nobody else has scratched. Even VBA does not come equipped with this functionality.

I endeavor to be lazy so I work hard to figure out something difficult if that means I can be lazy ever after. One lesson I learned from Mark Joshi (of xlw fame) is that people give up on using your software unless it is dead simple. It took a lot of work to figure out a way to make xll easy use.

Standard .msi Microsoft install files have become problematic, aside from the problem of creating them in the first place. If they are not signed by a Certificate Authority users get ominous warnings. After buying up all the CA companies, Microsoft figured out a way to extract even more money from software developers by inventing Extended Validation certificates that you can write a check for to make those warnings go away when using their products.

Visual Studio can use VSIX or NuGet to install packages without requiring certificates. They are almost as much fun as creating a .msi file. We now have MSIX and winget if you enjoy package management insanity.

There is an easier way.

Building software involves wrangling files that change over time. One might think that this should be a solved problem by now, but we live in a world where lesser minds have become fascinated by looking for ponies in Linus’ pile of git. He built git to solve his problem: how to merge (lots and lots of) changes into the Linux kernel code. Submodules were an afterthought and his original advice on how to implement those seems to be this.

The current version of git used by GitHub implements submodules adhering to his advice. Instead of a fancy pants package management system, you can just clone xllproject1 and it will drag in all the xll files you need to get started. Those files never change behind your back as you write new code. Visual Studio does not grok submodules, yet, so you need to start a Command Prompt (Tools > Command Line > Developer Command Prompt) and cd xll; git pull to get the latest version. Not dead simple, but the version of xll to use is under your control.

I’ve been getting up to speed with the latest in JavaScript add-ins for Excel. I ran the generator to create a sample add-in and took a look at what was generated:

WTF? 25,487 files weighing in at 147MB! WTF-ingF? That is insane.
Fine, it builds and runs, if you have enough patience. The functions even return results that appear to be correct.
Lets add the getStarCount example and rebuild. Works as advertised. Now let’s copy and paste into, say, 100 cells. After several seconds of #BUSY! maybe 10 of the calls succeed and the rest come back with #VALUE!. F9 does nothing. Shift-F9 does nothing. Ctrl-Alt-F9 does nothing. Alt-replace-equal-by-equal-all, nada. F2 an individual cell maybe? Nope.

Don’t get me started with how add-ins are now distributed. You might as well wear a straitjacket and leg irons.