[This article was first published on R Archives - Sam Lovick Consulting, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.I use R for most of my modelling work, but a lot of my clients live in Excel. BERT — the Basic Excel R Toolkit — bridges the two: you write an ordinary R function, drop it in a directory, and call it from a spreadsheet cell as =R.MyFunction(A1:A20). It also gives you an R console docked beside Excel and an R graphics device that draws into a cell.It is a genuinely useful piece of software. The problem is that its author stopped work on it in June 2018, and R has not stood still since. BERT 2.4.3 was built against R 3.4, and the further R moved the more of BERT quietly stopped working. I have spent some time bringing it up to R 4.6, and this post is a record of what that took — partly because a couple of the bugs are interesting in their own right, and partly in case anyone else is running BERT and wondering why bits of it have gone quiet.The work lives in a fork at github.com/SamLovick/Basic-Excel-R-Toolkit, and the whole set of changes is offered back upstream as a pull request. Releases there run on R 3.5 through 4.6, and as of the latest one everything works on all of it — which was not true a fortnight ago, and the story of how it became true is most of this post.Two things to say at the outset, so nobody downloads it on a false premise. This fork is 64-bit only — the 32-bit add-in is no longer built, which in practice matters to nobody, since 64-bit Excel has been the default for years. And although BERT was always billed as a connector for R and Julia, the Julia side is untouched and effectively dead. Its controllers target Julia 0.6 and 0.7, and the embedding API they use simply does not exist in Julia 1.x, so bringing Julia up to date is a rewrite of that controller rather than an upgrade of it. I use R, so R is what I fixed. The Julia code is still in the tree if anyone wants to take it on.Getting the controller onto current RBERT works by running a separate process, the controller, which loads R’s DLLs and talks to the Excel add-in over a pipe. That controller is where most of the version sensitivity lives.Two things made this less mechanical than I expected. The first is that R 4.3 added a C99 _Complex member to the Rcomplex struct, and MSVC will not compile it — so the build has to define R_LEGACY_RCOMPLEX and take the older layout. The second is that there is no single R to build against and no way to guess which one a user has installed, so the controller now checks the version it has actually been handed at runtime and says so plainly if it is outside the range it was built for.R 4.6 needed less than I expected. It removes Rf_isFrame, which the add-in used when deciding how to convert a result back to a range; that one call now uses Rf_inherits(sexp, "data.frame"), which does the same job and has been stable API since long before 3.5. That was the only source change 4.6 required. The controller compiles against 4.6’s headers, links against the existing import libraries, and runs.One release taught me a lesson about linking that had nothing to do with R. I had moved the project to a current protobuf through vcpkg, which by default gives you dynamic libraries. Everything worked on my machine. On a machine with no development tools the add-in simply failed to load with #NAME? in every cell, because those DLLs wanted the Visual C++ redistributable. The x64 build now uses the static triplet and the binaries import only system DLLs. If you ship a Windows add-in, test it on a machine that has never had a compiler on it.Which R you needAny of them, within the supported range. R 3.5 through 4.6, and everything works on all of it — cell functions, the console, Excel references, graphics and function help.Your RFunctions in cellsConsoleExcel referencesGraphicsFunction help4.6.xyesyesyesyesyes4.5.xyesyesyesyesyes4.4.xyesyesyesyes*yes4.3.xyesyesyesyes*yes4.2.xyesyesyesyesyes3.5.xyesyesyesyesyesTested in Excel on 4.6.1, 4.5.2, 4.2.2 and 3.5.0. R 4.3 and 4.4 ship modules built and checked on CI but not exercised in Excel here, for want of those R versions on my machine; they work by the same mechanism as the four that are tested.That is a duller table than it was a fortnight ago, when half of it said no. How it got dull is the most interesting thing in this post, and I will come to it.How it hangs togetherOne ControlR.exe hosts every version of R from 3.5 up, which is why cell functions, the console and function help were never the problem. The part that does not travel between versions is BERTModule, a compiled R package providing the graphics devices, the xlReference class used for Excel references, and a few helpers.R checks a module’s graphics engine version whenever a graphics device is created, and that version changes between R series — 4.2 is R_GE_group, 4.5 is R_GE_glyphs, 4.6 is R_GE_fontVar. A module built for one series therefore cannot draw on another; it fails with Graphics API version mismatch.So the install ships one module per series, in module/., and startup.R loads the one matching the R it is hosted in. All six come to about 3 MB together, which means there is nothing to choose at install time and no reason to publish separate downloads per R version: upgrade your R and BERT picks up the matching module by itself.If you run an R with no module — a future series, say — BERT falls back to another one. It still loads, and still provides references and the helpers; only drawing is lost, and the console tells you so at startup rather than leaving it to be discovered when a plot returns an error.The bug that actually mattered: Excel frozeThis is the one worth reading. I was checking whether BERT’s R graphics device still worked under R 4.5.2, and it did not. Excel locked up solid, showing Calculating (64 Threads), and stayed there. The R controller, meanwhile, was completely idle — not spinning, not waiting on R, just sitting there.Graphics turned out to be a victim rather than the cause. The actual rule was broader: any R function called from a spreadsheet cell that called back into Excel would hang the whole thing. Graphics only tripped it because BERT.graphics.device(cell=T) uses xlfCaller to find out which cell it is drawing into.The cause is a race, and it had been sitting in the code untouched since long before I got there. When R calls back into Excel, the callback arrives on a different thread, and that thread has to decide how to handle it. If the call came from the console, Excel is idle and the callback can be routed through COM. If it came from a spreadsheet function, Excel’s main thread is already blocked waiting for the answer, and the callback has to be handed to that blocked thread instead. The code distinguished the two cases by testing whether an event was signalled.The trouble was that the event was only reset inside the wait loop — and immediately before that loop sat a leftover Sleep(1000). R’s callback arrives in about a millisecond. So for the first second of every spreadsheet call, the flag still said “console call”, and the callback was politely routed through Application.Run into an Excel that was mid-calculation and would never come to the phone. Both processes then waited for each other indefinitely.The fix is to stop inferring the answer from timing. The add-in now records Excel’s main thread id at startup, so it can ask directly whether the current call is a blocking one, and resets the event before the call goes out when it is. The Sleep(1000) is gone.Tracing it was its own small exercise, because the debug output is compiled out of release builds and the bug only appears in a real Excel calculation. I ended up building a release with tracing switched on by a separate flag and capturing the output with a small listener script. Two things I would pass on to anyone debugging Excel add-ins: put the test formulas in the workbook and set it to calculate on open, so nothing has to be attached to Excel while it runs; and do not drive Excel over COM for this, because a client that exits without releasing its references leaves Excel hung in a way that looks exactly like the bug you are chasing. That particular mistake cost me an hour.A header that had been edited by handThe second interesting bug was found not by me but by continuous integration, which I had added mostly out of tidiness.R 4.2 changed the buffer argument of the ReadConsole callback from char * to unsigned char *. The controller still declared R 3.5’s signature. Against a stock R 4.2 or later that does not compile — so how had it ever built?Because the copy of R’s headers in the source tree had been edited by hand, years ago, to say char *. Every binary that had ever shipped was compiled against a modified R header, and nobody could have built the controller from a clean checkout with an R from CRAN. It worked on the one machine where the edit lived, and that machine was doing the releasing.The callback now follows whichever headers it is compiled against, switching on R_VERSION, and the local header is back to CRAN’s.This is the strongest argument I know for a build that runs somewhere other than your own desk. The CI job did not find a bug in the code so much as a bug in my environment, and it found it on the first run. It is also not the last time in this post that the build machine turns out to have been hiding something.The graphics bug the build machine was hidingThe version table above has a history behind it, and it is the bug I am least proud of and most glad to have found.Until very recently the build shipped exactly one BERTModule, built against whatever R happened to be installed on my machine. Because of the graphics engine version check described above, that meant graphics only ever worked on the R series the module was built against. The release before last shipped a 4.5 module, so plotting from a cell failed on R 4.2 in precisely the way it failed on 4.6.I did not notice for the same reason the hand-edited header went unnoticed: my build machine runs 4.5, so on my desk everything drew perfectly. Both bugs are the same shape — a defect that is invisible from the one machine that does the releasing — and finding one is what made me go looking for the other.The first fix was to ship one module per series and load the matching one, which turned the problem from “graphics works on exactly one R” into “graphics works on the two series I happen to have installed”. Better, but the table still said no rather more than I wanted.The obstacle was never the codeHere is the part I think generalises past BERT.Nothing about shipping a module for every R series was hard to write. startup.R already picked the right one, the selection was automatic, and six modules come to 3 MB. The obstacle was purely that building a module for a series needs that R and its matching Rtools installed on the build machine — well over a gigabyte of downloads per series, six times over, on a machine that then has six R installations to keep straight. That is why r10 shipped two modules and not six: not a design decision, just the path of least resistance on one developer’s desktop.The answer was to stop building them on my desktop. A workflow now builds the modules on runners, one job per series, and each job checks that the module it produced really was built for the R it claims — belt and braces against exactly the mix-up described below. Packaging pulls the modules from the last successful run of that workflow and verifies the same thing again before they go in the box.Which closes a loop I did not plan. The CI I added out of tidiness found the hand-edited header that meant nobody could build from a clean checkout. It now does the building for six R versions I do not have and would not want to install — and the result is that a table which said “no” in half its cells says yes in all of them. The build machine went from being the thing that hid two bugs to being the thing the build no longer depends on.Verified in Excel afterwards: drawing and Excel references both work on R 3.5.0 and 4.2.2, which is precisely what the previous release could not do, with 4.5.2 and 4.6.1 unchanged.One trap if you ever build R packages against several R versions in one script. R CMD INSTALL will happily relink from object files left over from a previous build, so building a module for 4.6 immediately after one for 4.5 produces something that claims to be built for 4.6 and behaves as 4.5 — the worst possible outcome, because it loads without complaint and then fails at the point of drawing. The build cleans Module/src between R installations, and the per-series verification exists because I did not trust myself to have caught every way that can happen.The small breakagesNot everything was dramatic. Argument completion in the console had stopped working under recent R, and the cause was a single line in BERT’s startup script:while (length(n) > 1) { if (n == "" || ...) { ... }}Inside that loop n can have length two, and || on a length-two logical has been an error since R 4.3 rather than the warning it used to be. It is the sort of thing that sits harmlessly for years and then becomes fatal in a point release. There was a second instance of the same shape elsewhere in the add-in.The consoleBERT’s console is an Electron application, and it was on Electron 1.8 — which dates from 2017 and predates most of the security model Chromium now takes for granted. It is now on Electron 44, with current Monaco, xterm and TypeScript.The unexpectedly fiddly part was the clipboard. Chromium dropped execCommand, current Monaco registers no clipboard actions of its own, and its editing surface never receives the browser’s paste event — so cut, copy, paste and select-all all had to be implemented and bound explicitly. If you maintain an Electron app that has been sitting still for a while, budget time for the clipboard.One performance bug was worth the measuring. Completion on a token that matched several thousand symbols would hang the console for ever. The reply was larger than a single 64k read on the pipe, and the reading code assumed one read per message, so split frames were silently dropped and the shell waited for a reply that had already arrived in pieces.Making your functions describe themselvesOne thing I added rather than fixed. An exported R function can now document itself, and Excel will show that documentation the way it shows help for its own functions. You attach a description attribute — the first element describes the function, the rest describe the arguments in order — and optionally a category to group it in the Insert Function dialog:TestAdd