CODEWITHSUNDEEP
rvbaexcelrexcel
Carlotta
Carlotta

Reputation: 1

Run user-defined R function from VBA

I have a problem while I am trying to call a R function I have created, in VBA. The problem does not come from the connection between VBA and R, but when my R code has to call my own function, the program stops. To make it clearer, here is the R code:

args = commandArgs(trailingOnly=T)
sink('Y:/Documents/BC10.txt', append=F, type = "output",split=TRUE)
source("Y:/Documents/myfunction.R")
cat('TEST !!')
simul = args[1]
level = args[2]
spd1 = args[3]
spd2 = args[4]
spd3 = args[5]
date_valo = toString(args[6])
swap_rate = args[7]
l1 = 0
u1 = 0.03
rho2 = 0.5
cat('\nparameters are: ', simul,  level, spd1 , spd2, spd3, date_valo, swap_rate)

PV_eq=PV_eq=myfunction(l1,u1,spd1,rho2,simul,level, date_valo, swap_rate)
cat(PV_eq)
sink()

When I run my VBA code, the program does well concerning the display of the parameters in the text file "BC10.txt", but it does not display PV_eq. So, I supposed the program does not manage to call my user-defined function "myfunction".

Does anybody has an idea of the problem I encounter ? Maybe, I have to add something in my VBA code (or R code) so that VBA takes into account user-defined function ?

Upvotes: 0

Views: 236

Answers (1)

Carlotta
Carlotta

Reputation: 1

Thanks for your answers.

I don't think the problem comes from my R code, because when I run the function "myfuntion" in R, it works.

I am using Rexcel to execute my R code. Here is the VBA Code:

Sub Compute_BC()

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 Dim path As String

Dim simul, level As Variant
Dim spd1, spd2, spd3, swap_rate As Variant
Dim date_valo As String

simul = BC.Range("B6").Value

level = BC.Range("B4").Value

spd1 = BC.Range("B13").Value

spd2 = BC.Range("C13").Value

spd3 = BC.Range("D13").Value

date_valo = BC.Range("B1").Value

swap_rate = BC.Range("B5").Value

path = """" & Cells.Range("RhomeDir") & """ """ & Cells.Range("MyRscript") & """" & " " & simul & " " & level & " " & spd1 & " " & spd2 & " " & spd3 & " " & date_valo & " " & swap_rate

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

End Sub

Rmq: I tested this VBA code with a simple R file, and it worked. The problem comes when in my R file, there is a call to a function I define.

Upvotes: 0

Related Questions