Bridging the Spreadsheet Divide: How Developer Sam Lovick Resurrected BERT to Bring Modern R to Microsoft Excel

LONDON — For data scientists working in corporate environments, a familiar professional friction exists: the technical team lives inside the flexible, powerful command-line environment of R, while the business stakeholders demand the ubiquitous, grid-based familiarity of Microsoft Excel.

For years, a bridge called BERT (the Basic Excel R Toolkit) offered a seamless conduit between these two worlds. BERT allowed analysts to write standard R functions, drop them into a designated directory, and invoke them directly inside spreadsheet cells as =R.MyFunction(A1:A20). Complete with a docked R console and an inline graphics device that could render plots directly into spreadsheet cells, BERT was celebrated as a remarkably functional piece of software.

However, software vitality depends on ongoing maintenance. Its original development stalled in June 2018, and as the R ecosystem marched forward through successive versions, BERT began to quietly fracture.

Enter developer Sam Lovick. After undertaking a comprehensive and arduous overhaul, Lovick has successfully resurrected the toolkit, releasing a modern fork that brings full native compatibility up to R 4.6. This development restores a critical productivity pipeline for analysts who rely on Excel interfaces but require the rigorous statistical muscle of modern R.

Main Facts: The Scope of the Modernization

Lovick’s newly released fork—available via his public GitHub repository—represents a comprehensive technical modernization of a legacy toolchain. The revamped add-in officially supports R versions 3.5 through 4.6, resolving years of cumulative incompatibilities that had rendered various features inert on modern machines.

Key parameters of the updated release include:

• Architecture: The toolkit is now strictly 64-bit only. The legacy 32-bit add-in has been abandoned, reflecting industry realities where 64-bit Excel has been the default standard for years.
• The Julia Component: Despite BERT’s original billing as a dual-language connector for both R and Julia, the Julia side of the codebase is effectively defunct. Its controllers targeted early alpha versions of Julia (0.6 and 0.7), and because that embedding API does not exist in modern Julia 1.x architectures, the Julia code remains untouched in the tree awaiting a ground-up rewrite.
• Feature Parity: Across the entire supported range of R (3.5 to 4.6), cell functions, the interactive console, Excel cell referencing, graphics devices, and native function help are fully functional.
• Self-Documentation: Functions can now accept custom metadata descriptions and categorization tags, allowing R-written functions to populate natively inside Excel’s Insert Function and Function Arguments dialog boxes.

Chronology: The Roadmap to R 4.6 Compatibility

Reviving a six-year-dormant software project that interfaces directly with two complex, closed- and open-source ecosystems (Excel and R) is rarely straightforward. Lovick’s retrospective details a meticulous sequence of bug fixes, infrastructural upgrades, and environmental realizations that ultimately brought the project to stability.

Phase 1: Addressing the Controller and R 4.3–4.6 Hurdles

BERT relies on an external executable (ControlR.exe) that loads R’s DLLs and communicates with the Excel add-in via a dedicated pipe. The core version-sensitivity lives here.

Initial hurdles appeared early: R 4.3 introduced a C99 _Complex member to the Rcomplex struct, which Microsoft Visual C++ (MSVC) refused to compile. Resolving this required defining R_LEGACY_RCOMPLEX to force adherence to the older memory layout.

Furthermore, because users run diverse versions of R, the controller was updated to dynamically check the R version handed to it at runtime, validating it against the compiled range. By the time R 4.6 was integrated, only a single source change was needed: removing the deprecated Rf_isFrame function and replacing it with the long-stable API call Rf_inherits(sexp, "data.frame").

Phase 2: Resolving the Dreaded Excel Freeze

The most critical engineering breakthrough involved a severe concurrency bug that had plagued the software invisibly for years. When users executed R functions that called back into Excel—most notably BERT’s graphics device (BERT.graphics.device(cell=T)), which queries xlfCaller to determine target cells—Excel would lock up completely, displaying the dreaded "Calculating (64 Threads)" message indefinitely.

Through careful tracing, Lovick discovered a race condition. Callbacks from R arrive on secondary threads. If a call originated from the console, Excel was idle and the callback routed safely through COM. However, if the call originated from an active spreadsheet calculation, Excel’s main thread was blocked waiting for an answer.

A legacy Sleep(1000) command sat immediately before the thread’s wait loop, and an event flag reset incorrectly inside that loop. Consequently, for the first full second of every spreadsheet calculation, the flag misidentified the origin as a "console call," routing it straight to an Excel instance that was mid-calculation and permanently unresponsive.

Lovick solved this by stripping out the timing dependencies entirely, explicitly recording Excel’s main-thread ID at startup, and resetting event flags before outbound calls occur.

Phase 3: Dismantling the Build Machine’s Blind Spots

Perhaps the most cautionary tale of the project involved a broken header file. R 4.2 altered the buffer argument of the ReadConsole callback from char * to unsigned char *. Yet, BERT’s codebase had been compiling successfully against it for years.

Upon investigation, Lovick discovered that the copy of R’s headers stored within the local source tree had been manually edited by hand years prior to read char *. Consequently, no developer could ever build the controller from a clean CRAN checkout; the software only compiled on the original author’s local machine, which housed the modified header.

This realization drove Lovick to implement continuous integration (CI) pipelines, ensuring the build was no longer tethered to the eccentricities of a single developer’s desktop environment.

Supporting Data: Performance Benchmarks and Modular Architecture

For a tool designed to process heavy analytical workloads inside a spreadsheet grid, performance is paramount. Prior to this update, empirical execution data for BERT was largely anecdotal. Lovick conducted formal performance benchmarking on the modernized build:

• Single Cell Execution: An end-to-end call from Excel to R and back costs approximately 0.3 milliseconds, translating to roughly 3,000 calls per second.
• Array Processing: Passing a 1,000-cell range adds just 0.2 ms of overhead; a massive 20,000-cell range executes in 3.7 ms.
• Console Evaluation: Interactive console evaluations execute between 0.2 and 0.5 ms.
• Autocomplete Latency: Workspace autocompletion takes 11 ms in an empty workspace and scales gracefully to 28 ms with 5,000 active objects defined.

Managing the Graphics Engine Version Trap

A major architectural challenge involved R’s internal graphics engine version, which changes across R series (e.g., R_GE_group in 4.2, R_GE_glyphs in 4.5, and R_GE_fontVar in 4.6). A compiled R package (BERTModule) built for one version of R will fail on another with a Graphics API version mismatch.

Instead of forcing users to download separate installers, Lovick engineered a modular distribution. The installer packages six distinct module versions (totaling a mere 3 MB combined) nested inside module/<major>.<minor>. At startup, startup.R automatically scans the host environment and loads the exact module corresponding to the active R installation. If an unsupported future version of R is encountered, BERT gracefully falls back to core functionalities while disabling plotting operations with a clear warning.

Official Responses and Developer Insights

Reflecting on the broader lessons of maintaining legacy open-source bridges, Lovick emphasized the dangers of desktop-dependent build environments.

"The build machine went from being the thing that hid two bugs to being the thing the build no longer depends on," Lovick noted in his engineering documentation.

By shifting compilation tasks to isolated CI runners—where automated jobs verify package integrity across multiple R versions simultaneously—he eliminated the invisible regressions that caused previous releases to fail silently on non-standard configurations.

Furthermore, user experience was significantly enhanced through integration with Excel-DNA IntelliSense. Because Excel natively suppresses inline tooltips for third-party add-ins, BERT now automatically generates and synchronizes XML metadata files with an auxiliary .xlam helper add-in. This enables custom R functions to display rich, parameter-by-parameter documentation directly within the Excel formula bar ecosystem.

Implications for the Data Science and Finance Communities

The revival of BERT carries profound implications for quantitative analysts, financial modelers, and data scientists operating within enterprise environments where Microsoft Excel remains an entrenched institutional standard.

1. Erosion of Technical Debt: Analysts who were previously forced to lock their local machine environments into archaic versions of R (such as R 3.4) simply to keep legacy financial models operational can now safely upgrade to modern R 4.6 runtimes without breaking downstream spreadsheets.
2. Reduced Friction in Interdisciplinary Teams: By empowering R developers to embed self-documenting functions directly into standard spreadsheet interfaces—complete with category groupings and native argument tooltips—the barrier to entry for non-technical business stakeholders is drastically lowered.
3. A Blueprint for Legacy Revival: Lovick’s transparent documentation of the resurrection process serves as an invaluable technical case study for maintaining abandoned open-source middleware, highlighting the absolute necessity of rigorous CI/CD automation, cross-version testing, and decoupled binary packaging.

For organizations seeking to deploy the updated toolkit, the open-source project is actively maintained on Sam Lovick’s public GitHub fork, with updated releases fully validated across multiple modern iterations of the Windows and R ecosystems.