1

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.

Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
user9630195
  • 91
  • 5

1 Answers1

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
duncan
  • 446
  • 3
  • 7
  • Thank you @duncan. I still have to figure out how things work. We are able to update an image using a worksheet range and a R. function call in same sheet so I thought the method would be similar. – user9630195 Apr 13 '18 at 11:42
  • I don't know the actual reason, but my assumption is that changing the value of one cell from another cell is disallowed because it would break the calculation tree, and possibly introduce circular dependencies. – duncan Apr 13 '18 at 17:16