Category Archives: Uncategorized

I Work Hard to be Lazy

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.

LET’s LAMBDA!

So Microsoft is rolling out LET and LAMBDA. Using The Wisdom of the Ancients (also known as the Excel SDK) something similar can be done using good, old fashioned, xlfEvaluate. The function XLL.EVAL takes any Excel formula as its first argument and evaluates it using the remaining arguments. It uses _1, _2, ... in the formula to refer to the arguments. If you want named arguments use key-value pairs like {foo, 1.23}. These can be mixed and matched at your pleasure.

Their Thanksgiving example would be XLL.EVAL(Thanksgiving, {year, 2020}) where Thanksgiving is the string "TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), """mm/dd/yyyy""")", although "DATE(year,11,MOD(12-WEEKDAY(DATE(year,11,1)),7)+22)" seems simpler.

One thing to keep in mind is that references and defined names occurring in the first argument must be preceded with an exclamation point. Don’t ask me why because I don’t know. Excel is Excel.


It might be big, but it sure is slow

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.

xllmath

I’ve been eating my own dogfood and it sure is yummy! The xllmath add-in hooks up functions in the C++ standard <cmath> runtime library to Excel. I work very hard to be lazy so I wrote some python code to generate the add-in from the GitHub documentation provided by Microsoft. Thank you Satya!
It is just a first cut but it compiles and runs. Try out my kibbles ‘n bits and let me know what you think.

New xll add-in library

I’ve rewritten my library for creating Excel add-ins for the n-th time: xlladdins.com. Let me know what you think.

The biggest change is that it now uses UTF-8 so you don’t have to deal with multi-byte (ASCII) character sets vs Unicode (UTF-16, UCS-2) pain. Everything gets converted to whatever makes Excel happy. Hopefully that makes you happy too.

If you have issues please post them to GitHub Issues. It’s not that I prefer the evil, money grubbing, GitHub capitalists to the evil, money grubbing, WordPress capitalists, I just like having things in one spot and I’m on GitHub more regularly.

Disclaimer: This is at the Minimum Viable Product stage so please don’t use it in production unless you need to get fired.

Msiery

I have been working on a new release of my add-in for doing Monte Carlo simulation (and mucho more!) in Excel. You can help me by trying out xllMonte . I am still shaking down the current release so expect to run into problems. The good news is I have the release procedure mostly automated so I can get you a quick turnaround on anything that comes up.

The install process is still too complicated. You have to download and unzip both the setup.exe and xllmonte.msi files and run setup as administrator. I am using the (free)  Visual Studio Installer Project extension to create the msi file.

I’ve used this in the past and it worked fine for what I needed. Then there was a period when Microsoft wanted you to use InstallShield Lobotomiz^WLimited Edition until you find out it was pretty useless and had to pay for the Actually Useful version.

The next thing I tried was WiX. No, not that WiX, this WiX. It was a bit daunting at first, but the more I looked into it, the more impressed I was by their product. These are top-notch software engineers providing high quality tools to solve complicated problems.

The Windows Installer is a big, complicated machine and I don’t claim any expertise in all of its ins-and-outs, but a msi file is just a database. You can use Orca to see and edit (don’t try this at home kids) the tables. The tables declare what state(s) you want to achieve –  e.g., where files get installed, what registry entries you need – and msiexec reads the database and executes the state machine to make that happen.

As I’ve painfully found out, the Visual Studio Installer Project does not give you full access to the Orca database. It gets worse – in Windows 10 the msiexec program runs with very low privilege. So low that you can’t even modify HKCU registry entries!

I use a Certificate Authority to sign the add-in and installer but still get the nasty warnings about installing “dangerous” software. It turns out there is a new CA in town: Extended Validation Certificates.  Money can’t buy you love, but it can buy you trust. At least from Microsoft.

For now you can run setup.exe as administrator, which then runs xllmonte.msi with appropriate privileges, and then you can get up and running.

I am not worried one iota about AI putting programmers out of work. There are an infinite number of fiddly little details that have to be solved to produce a product human beings want to use. Unless, perhaps, AI can train people to be satisfied with crappy products. We seem to be starting down that road…

Of course I’m probably missing a simpler solution. Maybe Windows Store is the right way to go.

 

Simpler Unit Test Framework

The C++ Range library always seemed overly complicated to me. I’ve been working on https://github.com/keithalewis/sequence lately. I work hard to be lazy and came up with an easy way to add unit tests: just write a function returning an `int` and call it to initialize a global `int` variable. The driver is just a translation unit having an `int main() { return 0; }`

See https://github.com/keithalewis/sequence/blob/master/binop.t.cpp for an example.

Unit test frameworks

I’ve never understood why people use GTest, CppUnit, Catch, oh wait, Catch2 now.
Here is my C++ unit test framework:

// test object constructor calls function
struct test {
    test(const std::function& f)
    {
        try {
            f();
        }
        catch (const std::exception& ex) {
            fputs(ex.what(), stderr);
        }
    }
};

Just write a int main() { return 0; } file and add .cpp files that call your tests.
They look something like this:

test my_function([]()
{
    assert (my_function_works());
});

Just make sure my_function_works throws an exception with an informative message. I use ensure for that.
More examples can be found here.
Why make things complicated?

Types

What is a type? You see that word used all the time in computer languages but it never seems to be precisely defined. Here is my definition:

type is a set of bits together with the operations that can be performed on those bits along with the rules the operations satisfy.

This is really just math 101 but the computer science world is finally catching up. Math doesn’t mention the set of bits because everything is a set in math.

An int is a set of bits (usually 32 or 64 these days) that can be added, subtracted, multiplied, divided, complemented, etc. These operations satisfy certain rules. For example, x + y = y + x. They actually satisfy a long list of rules that most people can’t be bothered to learn until something blows up. What about 2’s complement or unsigned ints or overflow? Ain’t nobody got time for that.

As my friend Candy Chiu first pointed out to me, C++ concepts (at the time called Concepts Lite) provide a language mechanism to do precisely this. More precisely, they allow compile time checks on boolean constraints on template parameters. And template parameters are types.

Li’l help?

I’ve been quiet lately, but I’ve been putting in a lot of new work. As some of you know, I teach Computational and Quantitative Finance at Columbia and use this library in that course. I revamped the xll12 library to generate documentation that can be built with Sandcastle Help File Builder and integrated with the Help on this Function link in the Function Wizard.

My students use the library to get a front row seat with traders: if you give a trader an Excel spreadsheet they will look at it. It is nearly impossible to get them to schedule a meeting where you can get their attention for any length of time, but spreadsheets are their world and they will quickly tell you what they like, what you got wrong, and, oh, yeah, BTW can you get it do this too?

One thing I learned from Mark Joshi (of xlw fame) is that you have to make things simple. People give up easily when they run into a problem. It takes a lot of work to make things simple.

I am still working on getting a CA so I can sign add-ins and set up a WebDAV server so people don’t even need to download the add-in/spreadsheet/help file. Just waiting on D&B to update my company in their database to move forward.

What I need help on is expert users to start pounding on the code and maybe even give pull requests. I’ve set up a repository at https://github.com/xlladdins/main. You can build it with VS 2017.

It is not as simple as it should be yet, but I will help you get going if you find this interesting. Follow your passion.