Error with Rexcel when starting up Excel

Question

Today something happened (never had this problem before) when I start up Excel workbook where I use Rexcel.

And then there are some more errors... Everything worked properly before.. Does anyone have an idea about what the problem could be?

The other strange thing is that after that the Rapply and so on works.. But I get errors again when I close workbook.

So the problem appears when I startR from Rexcel meny in Excel.

EDIT: Thanks to flodel for his answer. But is it not possible to run the Sub below now or?

    Sub create_efficient_frontier()

 RInterface.StartRServer

 Sheets("Analys").Range("A52:K82").ClearContents

 RInterface.PutDataframe "datat", Range("ChosenData!X181:AD352")
 RInterface.PutArray "startdate", Range("Analys!K2")
 RInterface.PutArray "enddate", Range("Analys!K3")

RInterface.RunRFile "C:/Users/Documents/EffFront.R"

RInterface.GetDataframe "hmz$pweight", Range("Analys!A51:E76")

End Sub

I get runtime error 13 ("incompatible types") at line RInterface.GetDataframe "hmz$pweight", Range("Analys!A51:E76")

That worked yesterday, see Run macro with Rexcel

Solution: Ctrl+Alt+Shift+F9 seems to work:)

Best Regards

Answer 1

Yes. I have struggled with this myself. Most likely your computations must be computed in a specific order. Typically, some of your (RApply, REval, etc.) calls create some data (e.g. x <- 10) in the R session and later calls will expect that data to exist: compute x^2.

Everything works fine when you build your spreadhseet from scratch. The problem is that when you save it and reopen it, Excel does not know in what order it needs to compute your cells: it figures out its dependency tree by firing out cell computations in a somewhat random order. So what happens is the cell supposed to compute x^2 might be run before the cell that creates x in your R environment, which triggers an error.

This is annoying but as you noticed, the errors do eventually go away after Excel is able to build its dependency tree. If you absolutely need to find a solution to that problem, it will have to involve VBA code to help Excel build its dependency tree in a very specific order.