Bundling of Excel files and R scripts

[iiLaurens]

Hi there,

First of all I want to applaud this project. The tests I did seem to work remarkably smooth and solid! This really is a game changer for those excel users that are sick of working with VBA.

Anyway, I speak here as a heavy duty user of both Excel and R in an enterprise financial environment. The only thing that really stops BERT from lifting off in an strictly controlled environment is the lack of bundling of Excel files and R scripts. Sharing files that belong together and versioning are a strict necessity, however currently BERT creates a strict barrier between the location of R and Excel.

It seems BERT only allows a single R file to be loaded at startup that must service all the different excel tools that I have to make. To make matters worse, for each tool multiple versions that need to be clearly segregated and auditable. I think you get my point that having just a single source file in some not so obvious user directory makes it very hard to distribute the amazing things I could make with BERT.

I think the this problem can easily be resolved by allowing each excel file to have an unique flag (perhaps a string variable in the vba code of that excel file) that I can set that directs BERT to the correct R file for that particular excel file. In this way, I can keep my excel and R file bundled and organised. Moreover I could quickly zip the bundleand share it with my coworkers.

Is it possible that in the future BERT could facilitate such a feature? In this way, BERT could become the greatest extension for R and excel in a similar way that Xlwings is for Python and excel.

I would love to hear your thoughts.

EDIT: Excuse me for my ignorance, it seems BERT can facilitate multiple R files already. The issue only seems to be limited to BERT always looking in the same folder. If it would be possible to tell BERT for each individual excel file where to look for scripts, my (and I assume many other's) problem would be solved!

[duncanwerner]

Hi,

Thanks for your note. Let me clarify on the files: for a long time BERT just loaded a single file. This seemed sufficient, given that you could source() additional files. In the last version, though, we switched to a directory -- BERT will load any R file in the functions directory, and watch for changes. And we moved from the appdata directory to Documents for the reason you suggest.

This just changed in the last version, so documentation is still lagging a bit (and tell me if you find something confusing).

Even so, however, it's still monolithic, in the sense that the R code is attached to Excel rather than to particular spreadsheets. There are two reasons for this. One is historical: that's just what BERT was originally intended to do. The second is for security. While it's true that there's nothing R could do that malicious VBA couldn't do, VBA is at least a little more visible. And a known exploit vector.

We could attach R code to particular spreadsheets -- in fact we do that ourselves from time to time, here's a module that will do it. But it's not something that's built in.

So we certainly could do it. I'm not 100% sure about it, though. I could be persuaded.

[iiLaurens]

Hello Duncan,

Sorry for the late reply.

As you are the creator, I respect your decision although I would have gladly seen support for some sort of R & Excel bundling. I can imagine that an implementation seriously impacts the design of BERT as it is now, and might negatively impact or confuse some existing users.

Perhaps I could suggest another solution that could solve my, and I believe many other's, problem. I was thinking about a way to interface with the BERT from VBA. If I'm able to send commands to BERT from VBA, This would enable me to save R files wherever I like and then tell BERT where to look for them. To illustrate, imagine something like this:

Private Sub Workbook_Open()
    path = ThisWorkbook.Path
    Application.BERT.set_dir(path)
    Application.BERT.refresh()
End Sub

This would be ideal for me, as I can keep code and files (R/Excel) that belong together in the same directory. This avoids confusion when my colleagues have to access and run these files. It also allows me to share the files to external parties very effortlessly without much explanation. Moreover, it prevents the issue you raised about security. Since VBA code is involved, the VBA needs an explicit confirmation from the user before the VBA code can be run.

I would love to hear if you are open to this suggestion.

[duncanwerner]

Well that you can actually do now. See here for an overview of talking to BERT from VBA.

I'm not sure setting the directory is the best way to do that (although it's certainly possible), but you could source (or watch) individual files, or even store R code in the spreadsheet itself. As a general matter more specificity is probably better.

While it's true that you have to explicitly allow VBA (unless you disable that setting), it's nevertheless a pretty significant security risk. Plenty of people disable security checks, or just blindly click yes to prompts. I'm not saying don't do it, just be aware of the risks.

[tranzmatt]

We could attach R code to particular spreadsheets -- in fact we do that ourselves from time to time, here's a module that will do it. But it's not something that's built in.

@duncanwerner Something along the lines of "Scratch space" would be awesome for me. I'm working on a port an Excel<->Matlab project into Excel<->R. I saw BERT and thought it would do nicely, but the original VB code does a lot of this:

Worksheets("Biff").Activate
Set BiffRange = ActiveSheet.Range("A5").Resize(NumBiffs, 15)
MLPutMatrix "Biff_Data", BiffRange

Worksheets("Bob").Activate
Set BobRange = ActiveSheet.Range("B5").Resize(NumBobs, 10)
MLPutMatrix "Bob_Data", BobRange
      
MLPutVar "BB", Worksheets("GlobalConstants").Cells(16, 2).Value
mlevalstring "BiffBob"

Worksheets("PostBiffBob").Activate
MLGetMatrix "PBB1", "PostBiffBob!A5"
MLGetMatrix "PBB2", "PostBiffBob!B5"
MatlabRequest

The original Matlab code is clearly using a lot of globals, which R doesn't support easily and, more important, makes use of the persistent Matlab session. I'm finding converting the actual number crunching routines from Matlab to R is simple compared to how to handle all the data in and out of Excel as the above is a trivial example. I'm looking at some extremely parameterized functions, both as input and output, so anything that facilitates this is greatly appreciated.

Thanks,

Matt

[iiLaurens]

Hi Duncan,

I did some digging into the source code and I actually found a method to implement my requested feature.

Suppose that I have two files in an arbitrary but common directory; one excel file and one R script file (aptly called script.r) containing all the functions I require. I found that it is possible to automatically load all the user defined functions from my script.r. To do so, I entered the following code in VBA under the "ThisWorkbook" module:

Private Sub Workbook_Open()
    Dim path As String, file As String
    
    path = ThisWorkbook.path & "\"
    path = Replace(path, "\", "\\")
    file = path & "script.r"
    
    Application.Run "Bert.exec", "source('" & file & "'); BERT$RemapFunctions(); BERT$WatchFile('" & file & "')"
End Sub

The benefit of this method are:

1. I can simply zip the two files together and share it with colleagues.
2. I can copy and paste both files to a new directory and continue on them if I have to work under strict version control.
3. The R functions I defined only exist within this excel file, and cannot possible interfere with other people's creations.

I hope the code I prepared is able to help some other's with similar problems as mine. Perhaps this code snippet can be added to the list of examples on the BERT documentation?

In any way, I'd like to complement the creator for making BERT and exposing BERT internals through the console with the BERT$... functions! It really provides flexibility and the power of R to excel.

[duncanwerner]

@tranzmatt I'm sorry, I'm not sure what you're asking for in terms of scratch space -- some place to hold data? The thing I mentioned was a facility for storing arbitrary R data (which can include functions) in a spreadsheet via a simple key-value store. If this is useful, here's the library.

If that's not what you're asking, please explain and I'll see if I can help.

[malcook]

@iiLaurens - thanks for the Private Sub Workbook_Open() example.

In my hands this works to load the file during workbook open, however watching seemed not to work.

At first.

However, apparently this came about because I changed the name of the file from "script.r" to "BERT.R"; when I changed it to all lowercase ("bert.r"), watching worked as desired.

Including the fact that if I edited the file in another editor, not only was it resourced into the console environment, but the display of the file in the BERT editor was refreshed.

NICE!

@duncanwerner - I second @iiLaurens that "Perhaps this code snippet can be added to the list of examples on the BERT documentation?" - maybe a good place would be the github wiki?

[iiLaurens]

@malcook - Nice to hear that it was useful for you. I think I can improve the VBA code so that other people won't run into the same issue as you did. Can you tell me what exactly went wrong? Can the bert console only watch files written in lowercase? Or can the bert console watch uppercase files, but the watch command should refer to the file as lowercase?

[malcook]

@iiLaurens - it appears to me that the filename needs to be lowercase for watching it to source it when changed, even though the editor will notice if it has been updated ( by another editor process).

[duncanwerner]

I'll have a look at file watching. This changed recently from watching specific files to watching directories, so that might have something to do with it. Just to be clear, are you matching on the exact name of the file, or are you trying to match with different casing?

[malcook]

I see both kinds of watching are available, but I have only so far tried watching specific files using exact name, where I find the name of the files must be all lowercase, though I think any of the following would work to watch it...

file = path & "script.r"
file = path & "SCRIPT.r"
file = path & "scripT.R"

[duncanwerner]

This should be fixed in a4f9ce39fe25b12ecc73725fae16ef8e0987a555, although there may still be a problem with some UTF8 file names. There is some path normalization to prevent you from watching the same file twice, which was causing the original problem.

[malcook]

Spendid! Thanks. I'll wait till next "release" to upgrade. For now, all lowercase is totally acceptable workaround.

Oh, and, your security considerations notwithstanding, I still think workbook specific bert environments is going to be a common theme in your future.... and the approach offered by @iiLaurens ought to be highlighted somewhere in your documentation/wiki.

[e-kennedy]

I'm trying to implement the solution proposed by iiLaurens using a custom Open_Workbook function. This does not seem to work (at least completely) in 2.4.4. It does not appear that the BERT$ functions are provided in the new version. I am able to source my file but don't know if the BERT$RemapFunctions and BERT$WatchFile functions are still needed. Has anyone tried to port this approach to custom R function files per workbook to the new version? Thanks.

Update: I found the equivalent to BERT$RemapFunctions. It is BERT$remap.functions. I have not found the equivalent of BERT$WatchFile. Would anyone know where that is?

[malcook]

hello @iiLaurens and @e-kennedy - I've restated your/our interest as a new issue at "BERT2 approach to bundling of Excel files and R scripts" #162 and welcome your closing it if you figured out how to do the same in BERT2.