sdllc/basic-excel-r-toolkit

basic-excel-r-toolkit's Issues

Stability issues - problem with "bertribbon.connect"

Hi there,

we du have some stability issues with BERT, it sometimes causes a problem at startup of Excel (addin: bertribbon.connect). Is there any log-file we can analyze?

Also, as the name indicates, only the ribbon is affected. Could we avoid such ribbon issues by simply loading BERTMGRXX.xll? Am I right that the COM addin is not needed?

Bundling of Excel files and R scripts

Hi there,

First of all I want to applaud this project. The tests I did seem to work remarkably smooth and solid! This really is a game changer for those excel users that are sick of working with VBA.

Anyway, I speak here as a heavy duty user of both Excel and R in an enterprise financial environment. The only thing that really stops BERT from lifting off in an strictly controlled environment is the lack of bundling of Excel files and R scripts. Sharing files that belong together and versioning are a strict necessity, however currently BERT creates a strict barrier between the location of R and Excel.

It seems BERT only allows a single R file to be loaded at startup that must service all the different excel tools that I have to make. To make matters worse, for each tool multiple versions that need to be clearly segregated and auditable. I think you get my point that having just a single source file in some not so obvious user directory makes it very hard to distribute the amazing things I could make with BERT.

I think the this problem can easily be resolved by allowing each excel file to have an unique flag (perhaps a string variable in the vba code of that excel file) that I can set that directs BERT to the correct R file for that particular excel file. In this way, I can keep my excel and R file bundled and organised. Moreover I could quickly zip the bundleand share it with my coworkers.

Is it possible that in the future BERT could facilitate such a feature? In this way, BERT could become the greatest extension for R and excel in a similar way that Xlwings is for Python and excel.

I would love to hear your thoughts.

EDIT:
Excuse me for my ignorance, it seems BERT can facilitate multiple R files already. The issue only seems to be limited to BERT always looking in the same folder. If it would be possible to tell BERT for each individual excel file where to look for scripts, my (and I assume many other's) problem would be solved!

Limit in execution in the console

Hello,
There seems to be a bug when a code is executed from a file using the "execute selected code" tool. Basically, and this happens only in loops, when there are too many lines of code, the execution freezes. This does not seem to depend on the actual content of the code : I had a loop that was working perfectly fine and simply adding a line of comment was enough to freeze the execution. The code written in the R console does not get to the last bracket of the loop.

Regards

Handling of complex numbers

Personally I find this logic sufficient:

• Input: for simple values / matrices: If it is not numeric, it's a string. If the string (single/matrix) can be interpreted as complex, it is complex.
• Output: if it is complex, output its character representation.

This logic can be done in R (e.g., in Functions.R), but then it requires wrappers around all functions which must accept real AND complex values (e.g., sin). It would be nice to have two (input/output) R hook functions in do.call, initially set to identity, so that if necessary one can alter the default behaviour.

Increase number of functions slots

Tried it, works very well and I like it. But I'd increase MAX_FUNCTION_COUNT, 100 is definitevely too small (I made it 2,000, changing accordingly the def file and the thunks in bert.cpp)

How to stop Prcessing Error: Is Excel Busy?

I do not know why instruction given at R console behave differently- especially user defined functions. Sometime they work smoothly sometime they trapped in endless loop with message Processing Error: Is Excel Busy?. How to break this loop? Whether function behaves in ad hock manner due to buffering problem?

Pass matrix from excel to r

I wanted to see what the form of the matrix would be when passed from Excel to R, with this simple code.

createDF<- function(mat){
  data <- as.data.frame(mat, ncol=3)
  return(data)
}

It seems to return 0's when the selected matrix is a mixture of types. What am I doing wrong?

Weird startup error message

Hello,
I keep getting this weird startup error message on startup:
"Sourced file OK: ExcelFunctions.R
Sourced file OK: functions.R
Error in file(filename, "r", encoding = encoding) :
cannot open the connection
In addition: Warning message:
In file(filename, "r", encoding = encoding) :
cannot open file 'ExcelFunctions.R': No such file or directory
Error reading startup file @ Fri Apr 21 14:59:30 2017"

Of course, what's odd is the combination of "Sourced file OK: ExcelFunctions.R" & "cannot open file 'ExcelFunctions.R': No such file or directory"

I have tried to change the startup directory etc but with no result.
Any clues ?
thanks !

help on rpart function

Hi,
First of all thank you for developing this great application! I was wondering if you could help me on an issue with R functions that use formulas as their arguments. I wanted to integrate rpart function into excel, unfortunately I can't get it to work using 'BERT.Call'. I believe there is an issue with the formula, which is takes the following form 'predict_var ~.'. I would be very grateful for any hints!
Thank you!

Can I writing R-code in the spreadsheet and export it to the Bert-console?

Sorry, in case this is not the right forum for my question !

I am wondering whether it is possible to write simple R-code in the spreadsheet - say something along the line of:

and then export it to Bert-console?

This type of procedure is, for example, quite commonly used by traders in investment banks when one needs to quickly get indicative pricing of a new product without having to wait for a new release of the official pricing library.

BERT improvement on VBA subroutines

Hello,

I'd like to share just my thought about the interplay between Excel and R.

BERT does an outstanding job by adding R functions to Excel front-end, it's useful having these available on a spreadsheet for light functions returning single results.

For heavy tasks I think users would love resorting to subroutines as they've no dependency on formula recalculation and give better customization regarding output arrangement. I know BERT scripting capabilities would resolve, but I think it's cleaner from VBA

Tipically such VBA subroutinewould act like an "offline" function

1. reading contents on the WB ranges
2. passing these to an R function (which might return a list in case of multiple results)
3. organizing results, by writing each list component in various parts of the WB

1. 2. are already ok, regarding 3) I'm in trouble about resizing vectors/matrices/data.frames (please see code below).

In general for the project, it would be nice having a BERT utility helping "getting-out" smoothly results from functions (the top would be preserving also results names in case of lists).

If I missed some functionality alread in BERT please excuse me. In general I think this would enhance BERT a lot.
I'd like to hear your opinion and any insight on that (a simple example follows)
Many thanks, Paolo

R CODE
ff<-function(v){
list(m=mean(v),e="hello",d=v)
}

VBA CODE
Sub run_ff()
a1 = Range("B3:B6") ' Reads data (real numbers)
x = Application.Run("BERT.Call", "ff", a1) 'run ff, x is variant loosing list names (Top: preseving names)
' Writes results
Range("A1") = x(1, 1) ' ok it works, scalar number
Range("A2") = x(2, 1) ' ok it works, scalar string
'Range("A3:A6") = x(3, 1) 'this would work (but one needs to specify range size)
Call getRes(x(3, 1), Range("A3")) ' Top: having a BERT help-function reading arg and resizing destination
End Sub

Internationalization / Localization

The UI is now stable enough to support internationalization / localization, and the code now (mostly) will support it. We just need volunteers to translate it into other languages. If you have any interest in helping out, please let me know -- either respond here or send me a direct mail.

Use Fira Code font (with ligatures) in BERT editor

The Fira Code font is great for code editing because it uses ligatures for things like arrows and inequalities.

To use Fira Code in the BERT editor, including ligatures, (and after you've installed the font on your machine), open the file bs-settings.json. This will be in the BERT folder.

Add these settings to the "editor" section:

    "fontFamily": "Fira Code Regular",
    "fontSize": "13px",
    "fontLigatures": true,

Obviously adjust size as desired. You can also use one of the heavier (or lighter) weights.

Missing BERTMGR32.xll

I had to reinstall BERT as some sort of conflict was happening between Excel Addons, but now something seems amiss. BERTMGR32.xll is no longer being installed into the Roaming\BERT directory, so Excel is having fits about it when I fire it up. I ran the msiexec with logging and while I see:

RegXLL: setting OPEN entry: "C:\Users\DrMCC\AppData\Roaming\BERT\BERTMGR32.xll"

I never see any install entry like this:

InstallFiles: File: BERT32.xll, Directory: C:\Users\DrMCC\AppData\Roaming\BERT, Size: 667648

So BERTMGR32.xll doesn't seem to be copied into the folder. Can anyone verify this is the case? I just tried it with a fresh download. Same issue. I'd built the project with my Visual Studio for kicks, but that can't be a reason for the failure, can it?

Matt

shell.exec() does not work from BERT console

Trying to find a simple way to open another Excel file from BERT console. shell.exec() does not work -no error messages are posted but the file doesn't open. (The same command works fine from R Studio.) Is there another way to accomplish this?

Excel scripting (COM) interface

Hi Duncan,

This is very great for new version 0.55 comes with COM interface. I tried below commands are success:

EXCEL$Application$get_ActiveSheet()$get_UsedRange()$get_Rows()$get_Count()
EXCEL$Application$get_ActiveWorkbook()$get_Path()

However, when I tried some more complicated command which returns error. I'm not sure what's going wrong. Is it too complicated for BERT? I'm just interested what level I can use BERT to automate Excel task. BERT is very useful. I'm very appreciated you develop such tool.

EXCEL$Application$get_ThisWorkbook()
=> Error in EXCEL$Application$get_ThisWorkbook() :
COM Exception in Invoke (ThisWorkbook) 0x80020009: Exception occurred.

EXCEL$Application$get_Range("A1")$get_offset(0,1)$get_Value()
=> Error: attempt to apply non-function

How to create range object for R function at run time?

I need help for creating a R function which may transfer R dataFrame of varying dimensions (based on inputs) to Excel.

kmeans.xlsb Runtime error 1004

I'm trying to run the kmeans.xlsb example and Excel 2016 is throwing up an Runtime 1004 error on the following line:

Set rng = sel.Offset(1, 0).Resize(sel.Rows.Count - 1, 1)

It doesn't look right, but I'm not sure how to fix it.

Excel does NOT see BERT

I'm a bit of newbee around this, so please excuse my ignorance.

Excel isn't see BERT. I'm running at MacBook pro, using windows 10 through Bootcamp. I've installed BERT twice and it simply doesn't show up. It's unclear to me what's going on.

If this is a useful bit of information, Rexcel doesn't link to R either. There are several versions of R on the machine, ranging from simple R to Rstudio.

The warning message for R is that there's no R registered, while BERT simply isn't there.

Thanks.
Larry

Forecast functions

Hi Duncan,
I have tried using Foo <- function( rng ){
process(rng);
}
to import forecast R functions into Excel (from the forecast package). The functions appear in Excel but when I try to use I get #Value!. Excel seems to read the code correctly, there are no errors when reading the start-up file, I am not sure what I am missing. I am particularly interested in the forecast.ets function.

Thank you,
Despina

BERT does not support function arguments (e.g. Y ~ X ).

I am trying to run glms models from data in Excel. How can I do this when BERT does not support functional arguments. The documentation seems to mention a work around but its quite general and I can't seem to make sense of it. Is it possible to show a working example where you are able to run a GLM model (or LM model for that matter) via BERT? Thanks in advance.

r console not working at Excel 2010 but working at Excel 2013

I have installed version 0.41 and tested it for 4 computers with Excel 2013 (both 32 and 64 bit) and Excel 2010 (both 32 and 64 bit). I found that the r console didn't work at Excel 2010 but worked perfectly at Excel 2013.

Besides, I found that the VBA worked at both Excel 2010 and Excel 2013 such as

• Application.Run "BERT.Exec", "Set.Range('F2:F9', seq(1,10))"

I feel BERT is very great. I prefer BERT much more than RExcel. This is very bad if I can't use r console at my office which use Excel 2010. Is there any help for this? Thanks.

Three improvement ideas

Hello,
just a couple of suggestions for your fantastic work that IMO would be very useful.

a) One button on the Excel menu, when clicked an input box appears , then the current Excel selection is set into R environment with the name choosen in the input box

b) One button on the Excel menu, when clicked an input box appears , then the corresponding choosen R variable is copy&pasted to the current Excel cell (which would be the upper-left corner in case of vector/matrix/data.frame)

c) An excel function that paste an R result into the spreadsheet, something like
=Rpaste(R.rnorm(100)) or from R console Rpaste(rnorm(100),cell="A10") with cell set by default to the current selection

All these would be useful because one would need just to select the starting cell to get some long result, overcoming Excel vector-formulas. I know that a) b) are available on RExcel and Matlab-Excel toolbox, about c) no idea.

Many thanks for your attention!
Paolo

r console not working

I downloaded the latest version. Excel 2010, Windows 7, 32 bit. Typing commands into the console does not work, but clicking on install packages does.

macro running button for bert macros

Hello everyone,
i am having an issue, in loading my vba macros.
when loading my vba macros in vba interface, using application.run to call bert functions, my program works correctly, but when i create a button for end users to call the macros, nothing happen.
does anyone have this issue too?
Thanks in advance for any suggestion :)

NAs in R still returns as large number

Old issue but still exists in BERT 1.08
ret11NANANA <- function() {return(append(rep(1,2),rep(NA, 3)))}
In Excel I get 1 1 2,6965E+308 2,6965E+308 2,6965E+308

Displaying plots from R in Excel

I have created a function in R to plot two probability density functions and I was wondering how I display the plot that is created with this function in Excel. Using the function (R.PlotDens(vars)) in Excel returns 0.

Thanks in advance

PlotDens <- function(alpha_A,beta_A, alpha_B,beta_B){
  require(ggplot2)
  x <- seq(0, 1, length = 100)
  y1 <- dbeta(x,alpha_A-1,beta_A-1)
  y2 <- dbeta(x,alpha_B-1,beta_B-1)
  plot <- ggplot() + geom_area(aes(x,y1), colour="dodgerblue3", fill="lightskyblue1", alpha=0.7, size=1.3) +
    geom_area(aes(x,y2),colour="brown2", fill="lightcoral", alpha=0.5, size=1.3) +
    scale_x_continuous(name = "Conversion rate") +
    scale_y_continuous(name = "Probability density") +
    ggtitle("Probability density functions of versions A and B") +
    theme_bw() 
  }

Enable the BERT Ribbon Menu Excel crashes

I'm on excel 2016 32-bit and for some reason when I try to Enable the BERT Ribbon Menu Excel crashes. (BERTRibbon2x86.dll)

Have tried fresh install for excel and re-installing Bert.

NaNs in R datatable become large number in excel

Thanks for your suggestion on the previous issue.
Here's another problem. I am returning the following data table to Excel.

Classes 'data.table' and 'data.frame':  548 obs. of  3 variables:
 $ Date            : Date, format: "2015-02-01" "2015-02-02" ...
 $ V1: num  107 106 NaN NaN 107 ...
 $ V2: num  103 103 NaN NaN 103 ...
 - attr(*, ".internal.selfref")=<externalptr> 

The NaNs become

Please advise. These NaN or NA should not show any numeric value.

Problem with function

I am using BERT 1.24. I want to incorporate `merge' (or join) type function in Excel with help of BERT. It is not very friendly to do same task with help of lookup functions. For this, my program takes references of merging ranges like "lev1!A1;D13" and "lev2!A1:G62" written at any place in sheet and selecting the written place. It works nicely if I perform following task at BERT console directly

`lev1<-range.to.data.frame(Get.Range(Text.Ref(z[1])),header=TRUE)
lev2<-range.to.data.frame(Get.Range(Text.Ref(z[2])),header=TRUE)
lev1_2<-merge.data.frame(lev1,lev2, all=TRUE)

wb <- EXCEL$Application$get_ActiveWorkbook()
new.sheet <- wb$get_Sheets()$Add()
new.sheet$put_Name("R_MergeData")
Activate.Sheet("R_MergeData")
out<-paste0("R1C1",":","R", dim(lev1_2)[1],"C",dim(lev1_2)[2])
Set.Range(Text.Ref(out),lev1_2)`

Unfortunately it does not works if I combine these codes as function and run them at BERT console. My function is

excel_merge3<-function() { z<-Get.Selection() lev1<-range.to.data.frame(Get.Range(Text.Ref(z[1])),header=TRUE) lev2<-range.to.data.frame(Get.Range(Text.Ref(z[2])),header=TRUE) lev1_2<-merge.data.frame(lev1,lev2, all=TRUE) wb <- EXCEL$Application$get_ActiveWorkbook() new.sheet <- wb$get_Sheets()$Add() new.sheet$put_Name("R_MergeData") Activate.Sheet("R_MergeData") out<-paste0("R1C1",":","R", dim(lev1_2)[1],"C",dim(lev1_2)[2]) Set.Range(Text.Ref(out),lev1_2) }

It gives message Error in BERT$.Excel(32768 + 109, list(ref)) : Excel call failed (32). Probably problem occurs at Set.Range.... Program post data in sheet from which takes information for merging ranges.

Other problem is that output is placed as array. For relatively larger data sets (5000 row and 15 columns) excel tries to put all data (5000*15) into each cell. It creates problem in handling the merge data.

Problem in BERT 1.33 Installation without administrator privilege

Without administrator privilege on Windows 2007, message “unable to write in C:\config.msi. Log on as administrator” is flashed. With administrator privilege, BERT is installed properly.

Prblem with R1C1 style

For BERT 1.24, problem occurs with R1C1 style while it is working nicely for A1 style.

`new.sheet<-wb$get_Sheets()$Add()
range<-new.sheet$get_Range("A1:J62")
range<-new.sheet$get_Range("R1C1:R62C10")

Error in new.sheet$get_Range("R1C1:R62C10") :
COM Exception in Invoke (Range) 0x80020009: Exception occurred.
`

Microsoft Excel has stopped working

When I have the BERT add-in enabled and I close out of Excel I get the following message. The issue goes away when I deactivate the add-in. Any idea on what is causing this?

Visual Studio 2015

I have tried to build the solution with VS2015, without success.
Are there any plan to upgrade the solution?

Set.Range( "B2:E9", seq(1,10))

when I try: Set.Range( "B2:E9", seq(1,10))

I get a 8X4 Matrix as output containing only 1's;

it looks like:

what am I doing wrong?

thanks

How not to lose the excel pipeline capability when using bert?

I'd like to know how to use bert without losing the excel pipeline capability.

rJava package .jinit() call crashes BERT/Excel

The last line in the following instantly kills Excel and BERT without any notice.

install.packages("rJava")
library("rJava")
.jinit()

This works the same on both my PCs (Windows 10 and 7 - both x64, MS Office 2010 x32, JRE x32)
Standalone x32 and x64 Rs with correct respective JRE do not crash.

R environment interference

Hi there,

we are just checking out if BERT is suited to replace our old interface between R and Excel. We really love the idea of this plugin!

However we came along a small issue where BERT interferes with the local environment. Obviously we have a local R installation for our tasks and we do call the local R scripts form VBA by using shell() or CreateObject("WScript.Shell").

If we do call the local R installation from VBA via shell, BERT somehow interferres with the environment, so our local R installation looses the library path to the userdocs-folder (in Windows, R creates a local plugin library in the user's documents folder). If BERT is deactivated or we call the R script from a command line not opened by VBA, everything works fine however. So there is some interference with the environment.

Just wanted to let you know about this, as I think that dual installation and local execution of R via VBA will be an established task for users interested in BERT - If you have any hints how to avoid this issue, please let me know.

Best
freiwerk

More than one Excel category

It is ok to have all function prefixed with "R.".

However, having all R functions grouped in one category can become rapidly impractical (too many).

It would be nice to have the capability to define more than one category to which assign R functions. Maybe just based on the file in which functions are defined (e.g., "Special Functions in R.R")

Error in file(file, "rt") : cannot open the connection in BERT CONSOLE

Is there any problem in Bert Console about reading the data?
My function involves a data so I have to read this data.

The home directory of the BERT console is C:\Users\SONY PC\AppData\Roaming\BERT
Although there is a text file which name is "bloodpressure.txt" in the same place. BERT can not read the tex file . It gives the warning message: Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") : cannot open file 'bloodpressure.txt': No such file or directory
What is the problem of not reading tex file although this tex file exists?

Excel Macros

This is not an issue, more something which might be added to the documentation. The docs show clearly how to run from the console macros that interact with Excel, but what if you want to run a macro directly from Excel? As Excel (Alt+F8) understands vba only, a vba trampoline is needed.
This is my attempt:

Sub EXEC(fun As String, ParamArray args())
    Select Case UBound(args) + 1
        Case 0: Application.Run "BERT.Call", fun
        Case 1: Application.Run "BERT.Call", fun, args(0)
        Case 2: Application.Run "BERT.Call", fun, args(0), args(1)
        Case 3: Application.Run "BERT.Call", fun, args(0), args(1), args(2)
        Case 4: Application.Run "BERT.Call", fun, args(0), args(1), args(2), args(3)
        Case 5: Application.Run "BERT.Call", fun, args(0), args(1), args(2), args(3), args(4)
    End Select
End Sub

Sub trycall()
    ' Here Set.Cell is a minimal example of an full R macro, which can interact with Excel, including the spreadsheet.  EXEC <macro name>, arguments
    EXEC "Set.Cell", "a1", 10
End Sub

problem with bert module

Hi, i am using Bert in french, and i am having some issue when loadind a vba code like application.run("R.bertfunction"), i have this message when opening Bert console :
"Warning: l'espace de nom 'BERTModule' n'est pas disponible et a �t� remplac�
par .GlobalEnv lors du traitement de l'objet 'EXCEL'"
does anyone knows why ?
Thanks in advance

How to expose to Excel functions from a package?

I'm absolutely a newbie to R, so forgive if I'm asking a stupid question: is there a way to expose directly all functions from a package? For example: I'm trying to expose to Excel special functions from package GSL; I could manually write interface function in Functions.R for them, but they are literally hundreths.
I don't know R sufficiently to make this automatic/programmatic.

P.S.: in this particular case, I've found that in the package source there are individual files (e.g., "bessel.R"): functions are defined there, and they simply call the gsl dll. So I could just source these files in Functions.R, and probably this would expose bessel functions to Excel. But this seems a kind of hack, and probably cannot be always applid.

Passing data.frame to COM API

Passing a data frame to COM (e.g. range$put_Value) only inserts the first column.

functions sd and diff spit errors

Tried 3 very simple functions: mean, sd, and diff. All of which I coded identical to SUM in your code, mutatis mutandis. I used a vertical column in excel of prices. Mean worked with no issue but with both sd and diff, I got a #VALUE error.

When attempt the converse and read the data into the console first using get.range, again SUM and MEAN worked without issue, but sd and mean result in "Error: stack usage xxxxxx is too close to the limit".

I am unfamiliar with this error and do not see why it would register. When I try typeof(matrix) is confirms that the vars is in fact "double", and I even tried to convert the matrix into a dataframe and only considering the first column of the matrix to no avail. Any assistance would be appreciated.

Using Win7, Excel 2010 (32-bit), BERT 0.40

Limitation on row and columns

Whether there are limitations on reading number of rows and columns from Excel in R through BERT? I tried to read 396 columns and 51314 rows through Get.Range function. BERT failed to read it. It flashed message
Error in BERT$.Excel(16386, list(ref)) : Excel call failed (32)
and failed to read data of any size from Excel. After re-opening BERT and Excel, I tried to read data in increasing number of rows. After reading data successfully at one level, I removed it from R and then tried to read larger number of rows. I could reach to 20000 rows after which it showed message
Error in BERT$.Excel(16386, list(ref)) : Excel call failed (32).
Strangely after showing this message, BERT denied to read smaller number of rows which it read successfully earlier. Whether it is problem in memory allocation by BERT or garbage (created through BERT) collection at R?

Computer Detail
Processor: Intel Core 2 Duo
Operating System: Windows 7 (32 bit)
MS Excel 2007
BERT v 1.36
RAM 2 GB

Problem with CustomXMLParts

Hi,

I am presenting a problem by inserting a CustomXMLPart, is successful only the first call to Add, but in the second the answer is "an error occurred processing the command. Excel is busy?" then excel(version 2013 64 bits) crashes.

 xmlString <-
        paste("<?xml version=\"1.0\" encoding=\"utf-8\" ?>" ,
         "<employees xmlns=\"http://schemas.microsoft.com/vsto/samples2\">" ,
             "<employee>" ,
                 "<name>Karina Leal</name>" ,
                 "<hireDate>1999-04-01</hireDate>" ,
                 "<title>Manager</title>" ,
             "</employee>" ,
         "</employees>", sep="")
EXCEL$Application$get_ActiveWorkbook()$get_CustomXMLParts()$Add(xmlString, missing)
<environment: 0x000000000adfd070>
attr(,"class")
[1] "_CustomXMLPart" "IDispatch"     
 xmlString =
         paste("<?xml version=\"1.0\" encoding=\"utf-8\" ?>" ,
         "<outputvars xmlns=\"http://schemas.microsoft.com/vsto/samples\">" ,
             "<outputvar>" ,
                 "<name>Karina Leal</name>" ,
                 "<hireDate>1999-04-01</hireDate>" ,
                 "<title>Manager</title>" ,
             "</outputvar>" ,
         "</outputvars>", sep="")
 EXCEL$Application$get_ActiveWorkbook()$get_CustomXMLParts()$Add(xmlString, missing)
(an error occurred processing the command.  is excel busy?)

Any idea what could be happening?

How to make BERT use the 64- instead of 32-bit R

Hello everybody!

I was struggling to make some model work in excel and after comparing with my co-workers who don't have a problem with reading rJava package in BERT, I found out that I seem to be using a different version of R (R.Version() returns platform "i386-w64-mingw32"... instead of platform "x86-64-w64-mingw32". I have both versions of Java installed). I have re-installed R and also tried to erase files from //R 3.3.3/bin/i386 in BERT folder (which resulted in being unable to run BERT at all) with no success.

I would greatly apreciate any ideas about how to set BERT to use the 64-bit version instead of 32 and why on my coworkers' machines it works without any problems (we all use 64-bit OS, and I have ).

Thanks, Matija

Copying and pasteing data from excel to R and back using vba

Hello;
I am following the vba two examples and so far what I have tried its not working. I am trying to copy a range of cell with values to R, perform some statistics in R and get the results back to the same spreadsheet. Is it possible to do it? if so, could you provide a minimal example or direct me to a place in the wiki where I can read about it? Thanks

Get.Range does not work some time

I was trying to read data with header (data frame type) from excel. Get.Range() some time fails to read data and flashes message
Error in BERT$.Excel(16386, list(ref)) :
SET_VECTOR_ELT() can only be applied to a 'list', not a 'NULL'
Some time it read data in single array and show error (in valid dimension) at time of using range.to.data.frame() for line rng[-1, ]