Hello,
I am trying to embed R-code inside VB for Excel (probably a perverse
endeavour anyway) and I am running into difficulties, especially when
passing vectors back and forth between the two environments.
(1) I am using the RExcel package.
(2) An example of error that I often get and that I can't seem to be able
to work myself around of is the following VB message:
------ Run-time error '13' - Type mismatch
(3) Detailed example shown below (just added for completeness... I hope
it's not necessary to look at it in detail!).
(4) I have looked at the obvious aspects (eg consistent size of vectors)
but to no avail
I was wondering whether there is a strategy/good programming practice to
avoid these type mismatch errors, or if at least there is a good debugging
tool/method to go about correcting them -- the main problem with the R/VB
embedding is that it is quite difficult to understand whether the error
depends on VB, on my R code, or on the interface of the two environments!
Thanks in advance for any suggestions you might have
Peter
------------------- VB Code ---------------------------------
Dim Link_Ratio() As Double
[...]
No_of_Years =
Application.WorksheetFunction.Count(Worksheets("InputTriangle").Range("C15:IV15"))
ReDim Triangle(No_of_Years, No_of_Years)
ReDim Link_Ratio(No_of_Years)
[...]
' Calling some R functions
Call rinterface.RunRFile(Functions_String)
' Input
For ID_Col = 1 To No_of_Years
Link_Ratio(ID_Col) = Worksheets("Projection").Cells(Start_Row,
Start_Col + ID_Col - 1).Value
Use(ID_Col) = Worksheets("Projection").Cells(Start_Row + 1, Start_Col
+ ID_Col - 1).Value
Next ID_Col
Call rinterface.PutArrayFromVBA("link_ratio", Link_Ratio)
Call rinterface.RRun("fitted_lr <- link_fit_vec(link_ratio)")
Fitted_LR = rinterface.GetArrayToVBA("fitted_lr")
---------------------------- END OF VB code
-------------------------------------------
At this point the code execution stops, signalling the type mismatch
error.
The related R code is this:
------------------------------R code
------------------------------------------------------------
link_fit_vec <- function(lr,use_flags=rep(1,length(lr)),max_settle_time =
15, no_of_months=12, method="Exp"){
output =
link_fit_exp(lr,use_flags=rep(1,length(lr)),max_settle_time = 15,
no_of_months=12)[[4]]
output
}
link_fit_exp <- function(lr,use_flags=rep(1,length(lr)),max_settle_time =
15, no_of_months=12){
# lr = link ratios, INCLUDING the tail factor
# use_flags = 'used' flag, eg x = (1,0,1,1,0,1,1,1)
# default value is use_flags = (1,1,...1) with as
many 1's as no of years
if (length(lr) != length(use_flags))
message("Error: Used flag vector must have the
same length as link ratio vector")
# Remove rightmost element (tail factor)
lr = lr[-length(lr)]
use_flags = use_flags[-length(lr)]
use_flags[lr<=1] = 0
z = lr*use_flags
w = c(1:length(lr))
w = w*use_flags
z_prime = z[z!=0]
w_prime = w[w!=0]
y = log(z_prime-1)
# Least-squares regression calculations
lm_output = lm(y ~ w_prime)
# Curve parameters
intercept = summary(lm_output)$coefficients[1]
se_intercept = summary(lm_output)$coefficients[3]
slope = summary(lm_output)$coefficients[2]
se_slope = summary(lm_output)$coefficients[4]
a = exp(intercept)
se_a = a*se_intercept
b = slope
R2 = summary(lm_output)[9]
# For a complete output:
# type summary(lm_output)
param_vec = as.numeric(c(a,se_a,b,se_slope,R2))
# Fitted curve
xx = c(1:length(lr))
yy = intercept+slope*xx
fitted_model = exp(yy)+1
# Add a tail factor if max_settle_time > no_of_years
if (max_settle_time > no_of_years){
xx_ext = c(no_of_years:max_settle_time)
yy_ext = intercept + slope*xx_ext
}
# the reason for a double if is that the else condition
doesn't seem to work... I'm sure it's just me
if (max_settle_time <= no_of_years) yy_ext = 0
tail_factor=prod(exp(yy_ext)+1)
# Complete model -- link ratios + tail
fitted_vec = c(fitted_model,tail_factor)
# What happens if the no of months available is less than
12?
percent_shift = (12-no_of_months)/12
xx_shifted = xx - percent_shift
yy_shifted = intercept+slope*xx_shifted
shifted_model = exp(yy_shifted)+1
yy_tail = intercept+slope*no_of_years
yy_shift_tail =
intercept+slope*(no_of_years-percent_shift)
shifted_tail_factor = tail_factor *
(exp(yy_shift_tail)+1)/(exp(yy_tail)+1)
shifted_vec = c(shifted_model,shifted_tail_factor)
# This "shift factor" is to be applied to all link ratios,
regardless
# of whether the exponential model was used or not
shift_factor = shifted_vec/fitted_vec
# Output
output_list=list("Parameters and fit
(a,se(a),b,se(b),R2):",param_vec,"Fitted link ratios (including tail
factor):",fitted_vec,"Shift vector",shift_factor)
output_list
}
------------------------------ End of R code
------------------------------------------------------------
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS E-MAIL
For Aons standard conditions associated with this e-mail please visit
http://www.aon.com/uk/en/email-footer/aon-limited.jsp
Aon Limited
Registered Office: 8 Devonshire Square, London EC2M 4PL
Registered in London No. 210725 . VAT Registration No. 480 8401 48
Aon Limited is authorised and regulated by the Financial Services Authority in
respect of insurance mediation activities only.
[[alternative HTML version deleted]]
______________________________________________
R-devel@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-devel
