pietro.parodi at aon.co.uk
2008-Jun-05 17:01 UTC
[Rd] R-code embedded in VBE -- Type mismatch errors
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 Aon’s 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]]
Pietro, pietro.parodi at aon.co.uk wrote:> 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.There is a mailing list for RExcel where this should be discussed. Please re-post your message to rcom-l. Please be sure to browse the list archives first. Have a look at the RExcel documentation for sources of information. Thomas