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]]

