BERT R script, How does environment and scope work?

Question

I am having some issues understanding how BERT is handling R Environments. Ideally I would like to be able to run an R script via VBA, similar to a VBA Sub. I don't think BERT facilitates this.

When I run below R script in BERT Console:

controls_sheet <- EXCEL$Application$get_Sheets()$get_Item('Sheet1')
some_vec <- 1:5
controls_sheet$get_Range('A1:A5')$put_Value(some_vec); # print results to sheet

Behavior matches my expectations and I print a sequence from 1 to 5 in range A1:A5 on Sheet1.

When I set up a function to perform same task the results are not printed to the sheet. For example using below:

test_fun <- function(sheet = 'Sheet1'){
    controls_sheet <- EXCEL$Application$get_Sheets()$get_Item(sheet)
    some_vec2 <- 1:5
    controls_sheet$get_Range('A1:A5')$put_Value(some_vec2); # print results to sheet
} 

My expectations is that a sequence form 1 to 5 will be placed in range A1:A5 on a worksheet that I provide to the function. Calling =R.test_fun("Sheet1") anywhere in worksheet Sheet1 does not produce any output in range A1:A5.

Can someone please enlighten me about how I can run an R script similar to VBA sub or create a function that takes no input, runs a script and prints output to a worksheet.

Answer 1

This is actually a context problem. You can't set a cell value from another cell, whether you're using COM or the Excel API.

Not sure why you're not getting an error about this in the shell.

You need to do this from a different context, like from the BERT console or perhaps from a VBA function:

Sub x()
    Application.Run "BERT.Call.R", "test_fun", "Sheet1"
End Sub