Pass a list of characters as parameter to RScript in VBA?

Question

I have a R script that works fine, with one of its variables being set as such :

Benchmark <- c("Bench1","Bench2","Bench3","Bench4","Bench5","Bench6")

Benchmark
[1] "Bench1"   "Bench2"   "Bench3"   "Bench4"   "Bench5"   "Bench6"

Later on in the code, I check in a table if one of its column has a value present in Benchmark :

CollStruc <- CollStruc[CollStruc$Designer %in% Benchmark,]

Until there, no problem. The issue here is I'd like to call the Rscript from VBA, thanks to this code :

Dim shell As Object
Set shell = VBA.CreateObject("WScript.Shell")
Dim waitTillComplete As Boolean: waitTillComplete = True
Dim style As Integer: style = 1
Dim errorCode As Integer

'Paramètres à passer à R
Dim CheminR As String, Benchmark As String

NbBench = WSExec.Range("C16").End(xlDown).Row - WSExec.Range("C16").Row

Benchmark = """" & WSExec.Range("C16").Value & """"
For i = 1 To NbBench
    Benchmark = Benchmark & "," & """" & WSExec.Range("C16").Offset(i, 0).Value & """"
Next i

Dim path As String
path = "RScript \\Tagwalk-nas\public\R-Scripts\Brand_Equity-Benchmark-parameters.R " & Benchmark

errorCode = shell.Run(path, style, waitTillComplete)

I've been passing arguments in RScript with lines such as these :

args <- commandArgs(trailingOnly=T)
Benchmark <- c(args[1])

And this is where things go wrong. My Benchmark variable doesn't look the way I expect it to, and so the rest of the code returns errors

Benchmark "1" "Bench1,Bench2,Bench3,Bench4,Bench5,Bench6"

Do you know how I can make this work ? Thanks

EDIT : Thanks Adam and Parfait for your help.

The variable Benchmark created in VBA now looks like this :

print.debug(Benchmark)
"Bench1,Bench2,Bench3,Bench4,Bench5,Bench6"

I've tried using unlist as suggested by Adam, and it seems to transform the variable as I'd like. Here is the variable when I use it in R :

str(Benchmark)
chr [1:6] "gucci" "louis-vuitton" "chanel" "christian-dior" "prada" "fendi"

and here is the output provided by sink() I get when putting str(Benchmark) in my Rscript executed from VBA :

 chr [1:6] "gucci" "louis-vuitton" "chanel" "christian-dior" "prada" ...

Parfait · Accepted Answer · 2019-12-06 22:19:53Z

2

You are passing a comma separated list. I can't replicate your project so this'll be a partial answer.

Once you have Benchmark <- "Bench1,Bench2,Bench3,Bench4,Bench5,Bench6" the problem becomes pretty easy. I can't tell from your question what the structure of Benchmark is after you try running the script. So you might need to clean it up or unlist() it before you are ready.

Then,

args <- commandArgs(trailingOnly=TRUE)

Benchmark <- unlist(strsplit(args[1],","))
#[1] "Bench1" "Bench2" "Bench3" "Bench4" "Bench5" "Bench6"

edited Dec 6, 2019 at 22:19

Parfait

108k19 gold badges103 silver badges138 bronze badges

answered Dec 6, 2019 at 14:45

Adam Sampson

2,0411 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Sampson Over a year ago

Can you do a str(Benchmark) or something else so we can see what is in your variable? Otherwise, I don't have your excel sheet. I'd have to try to create one that would work with your code before I could get your VBA to run. And it's probably just that you don't understand the different types of r variables.

Parfait Over a year ago

I am not the OP. I was suggesting you show the commandArgs to help OP connect his/her to yours.

Adam Sampson Over a year ago

Ah. Gotcha. Losing steam here on a Friday.

Collectives™ on Stack Overflow

Pass a list of characters as parameter to RScript in VBA?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related